Get variable value into destination in SSIS

Many times, a data analyst has to log no. of records processed, inserted, updated, deleted or failed. This is the significant thing that one must do in a separate file. But have you thought how you will do this in SQL Server Integration Services (SSIS). Getting this numbers into variable in SSIS is effortless but in excel,OLE DB or flat file destination ?? Confused🤔! Don’t worry, here, we will log this numbers into destination file.

1. Here, I have used SQL table which has 10 rows as a source and we will log no. of records processed.

Source tabled.

2. Use data flow task to copy all the data from source to your destination. Here, I have used one variable named “ProcessedRows” to store no. of records processed and got this number using row count task.

Data flow task to get data from SQL to excel

3. Now, go to Control flow and add one more data flow task which I have named “Logging”.

Control flow task

4. In Logging, use script component task as source. Now time is for configuration. In Script transformation editor, go to the Inputs and Outputs menu and create Output columns.( Do not forget to check datatype).

5. Go to Script menu, select ReadOnlyVariables and Click on edit script, you will be redirected to the the Microsoft visual studio code.

In Microsoft visual studio code, Simply add below code:

public override void CreateNewOutputRows()

{

Output0Buffer.AddRow();

Output0Buffer.DateTime = DateTime.Now;

Output0Buffer.ProcessedRows = Variables.ProcessedRows;

}

Save this code.

6. In data flow, add destination file in which you are going to store logging details and configure it.

7. Run the package.

8. I have stored the logging details into excel file. After dubbing you will find the output into excel as below.

Output-Logging details

Output-Data from SQL to excel

Here you go fellow!

Easy, it was! You just needed a trick amigo.

Please let me know if you have queries.