131 How to add a logging to SSIS package?

Подписаться 32 тыс.

Просмотров 6 тыс.

50% 1

How to add a logging to SSIS package?
drive.google.com/drive/folder...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
How to add custom logging to ssis package
How to create a custom logging in SSIS?
How to add a logging to SSIS package?
Happy Learning.
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”

Наука

Опубликовано:

11 апр 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 40

@charancharan4429 Год назад

Exactly we need this type of scenarios...Thanks bro...we will do it...

@learnssis Год назад

Okay great.

@maheshjampala560 Год назад

sir your really great in your teaching keep posting these kind of vedios

@learnssis Год назад

Thanks Mahesh for your comment. Sure that's the plan.

@varrenagavenkatasuresh1236 Год назад

Thank you very much for the video @Learn SSIS

@learnssis Год назад

You are most welcome.

@1217895 Год назад

HI, Data source is SQL. I use a data flow task to export a column called ID ( with numbers such as 1,2,3) to an excel sheet. When the user opens the excel, he is forced to convert the ID column from text to number format. The ID Column (when creating the excel is an integer).

@1217895 Год назад

Example of the excel CREATE TABLE syntax CREATE TABLE `Excel Destination` ( `ID` INT, `DateMonthLoad` INT, `DateMinLoad` DATE, `DateMaxLoad` DATE, `NextMthMinLoaddate` DATE, `MonthEndDate` DATE )

@1217895 Год назад

Please refer to column A in your video '14 Export data from Sql Server to Excel using SSIS'. At 3:03 , you can see column A showing as text and not a number

@venkatchinta3105 Год назад

Hi bro, recently I got an issue. while doing incremental load in cloud using store procedure. we are getting row count difference between on premise aggregate tables and cloud aggregate tables. in on premise the stored procedure generated by automation it has cursors. but In cloud we modified the code to avoid cursors. and create temp tables for every 7days incremental load. we are getting more row compared to on premise data. could you tell me any suggestion for this.

@learnssis Год назад

Sorry I don't have any idea about this case.

@1217895 Год назад

Hi Aqil, completely unrelated. Are you able to help with a C# script - That opens an excel work called 'Sales_YYYY_MM.xlsx' ( the YYYY_MM is like 2023_05 but changes with eachmonth) - And then changes the text output (of a column with numbers) to actual numbers - Eg: column 10 has several numbers saved as text data type in the worksheet. I want to be able to convert the column to numbers

@learnssis Год назад

You can use data conversion transformation to change the data type of a field from varchar to numeric.

@1217895 Год назад

A numeric column shows up as text in excel.. even agter changing its data type to numeric. Can you help with a c# script?

@learnssis Год назад

Can you pls explain in detail what you want I did not get it ?

@1217895 Год назад

So, essentially, what i really need is a C# script that edits this file and converts the required column to number format

@TheSK01010 9 месяцев назад

everything working fine but after configuring the data conversion method I am not able to capture the reason for rejected records.

@learnssis 9 месяцев назад

If you see this video, I have shown how to redirect the bad records to another table. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RK_sGiHgYWQ.html The reason for the failure that either truncation is happening or data in the field is not correct thus it is failing to convert to desired type. For example if you are trying to convert a varchar column to a datetime column and if invalid dates found in the source column then at the data conversion it will fail to convert and will move those records to errored table along with the value and you can look at the value and see what is wrong with the value.

@_prince.gupta_ 10 месяцев назад

Hi Aqil, I needed the same scenario, but the issue is I need to remove special characters from a particular column of a xml file and put the history of the changes audit table. I saw your remove special char video from csv file through SSIS package , but getting confused like , how can I relate that to this.

@learnssis 10 месяцев назад

Hi Prince, in your case there are 3 things 1. Import XML file to sql server table 2. Remove the special character and insert the affected record to sql server table 3. Export the data back from sql server table to XML file. I have created few videos on how we can import the xml file to sql server table Different ways to import xml file to sql server table ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gZs3yPhfowU.html Import xml file to sql server table using xml source ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9vWYLGsaXJc.html Import xml file to sql server using sql query ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-w05oX0obgp4.html How to import data from xml to sql server using C# ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bZAvgXE5awk.html Now you can write a select query to fetch the record sql table where we have special character in it and log it to sql server table and then remove the special character from the table. Later you can export the corrected data from xml file to sql server table Export data from SQL Server to xml file using SSIS ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NuT3CpiUSrM.html

@_prince.gupta_ 10 месяцев назад

@@learnssis THANK YOU Aqil, will do and let you know.

@_prince.gupta_ 10 месяцев назад

Hi@@learnssis Aqil, I tried and successfully import the xml data into SQL server table ,also audit the changes into audit log table. But enable to remove special characters from a particular column of imported data. I want to remove the special char before uploading it to table.

@josemanuelnaranjoespinosa6234 10 месяцев назад

Hi Aqil, could you please add the query to create the SQL log table into the Google drive folder? Thanks for the great video...

@learnssis 10 месяцев назад

Thank you for letting me know on this, I have added it to Google drive and you can take it from here as well. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_log]') AND type in (N'U')) CREATE TABLE [dbo].[audit_log]( [Id] [int] IDENTITY(1,1) primary key NOT NULL, [PackageName] [varchar](50) NULL, [Status] [varchar](100) NULL, [FileName] [varchar](1000) NULL, [RecordsInserted] [int] NULL, [RecordsRejected] [int] NULL, [Dated] [datetime] NULL, DestinationTable varchar(100), ErrorMessage varchar(max) ) ON [PRIMARY]

@TKJ Год назад

Hi, I want to load FTP server .CSV file to SharePoint. Can you suggest me best method

@learnssis Год назад

actually I never uploaded the file to sharepoint, probably you would need to try C# code for this.

@TKJ Год назад

@@learnssis if possible then can you please create a video of that c# code

@1217895 Год назад

Additionally, my SSIS package initially saves the Excel file with a date time stamp eg:'Sales_YYYY_MM.xlsx' . I then email the file to the end user. It's just that the user is forced to convert text to numbers in excel (and he doesn't want to manually do this)

@abidalisuthar7738 11 месяцев назад

Very good Aqil. I ran your sample and it's working as expected with one exception that when we have "Row Redirec" on error, we are not getting the actual error in audit_Log table. Please let me know if something is missing in sample.

@learnssis 11 месяцев назад

I don't know how you are populating the audit_log table.

@abidalisuthar7738 11 месяцев назад

@@learnssis I haven't made any change in your sample. Just runing it but on error (currently which is redirected to get rejected count) it's not logging actual error in audit_log table.

@1217895 Год назад

The requirement is to open an excel workbook with a dynamic name 'Sales_YYYY_MM.xlsx' and change the data type of a column such as Sales_Amt from text to number

@techbroaniletl8226 Год назад

Hi sir i need information my source is sql and my destination is sql we need to load data in destination without duplicate but if we got the duplicate we need to capture that , that duplicate need to loade or capture in one of the table, please can you provide this information please.

@learnssis Год назад

Sorry I did not get your question.

@techbroaniletl8226 Год назад

@@learnssis Hi sir i need information my source is sql and my destination is sql we need to load data in destination without duplicate but if we got the duplicate we need to capture that , that duplicate need to loade or capture in one of the table, please can you provide this information please.

@learnssis Год назад

@@techbroaniletl8226 You can insert data from sql to sql as it is in a staging table. And then you can get the duplicate records easily from the staging table and insert those records to your desired table. You can find plenty of videos on how to get the duplicate records from a table. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8ohs43g9X5Y.html

@techbroaniletl8226 Год назад

@@learnssis yes sir but need to load data through the help of ssis and also what we got any duplicates we need to capture that data sir please help on this

@1217895 Год назад

After