This article will show you how to use the SSIS Script Component as a Destination with a practical example. You can also look into the following links:
Before we start creating the SSIS Script Component as Destination package, Let us see the SQL table and the data that we are going to use.
Configuring SSIS Script Component as Destination
STEP 1: Drag and drop the Data Flow Task from the toolbox to control flow region. Next, rename it as the SSIS Script Component as Destination.
Double click on the SSIS data flow task will open the data flow tab.
STEP 2: Drag and drop OLE DB Source from the toolbox to the data flow region. Double click on OLE DB source in the data flow region will open the OLE DB Connection Manager settings
From the below screenshot you can observe that We selected [MyEmployees] table present in the [SSIS Tutorials] Database as source table
STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.
STEP 4: Drag and drop Script Component from SSIS toolbox to data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type will open, as shown below. Here we want to demonstrate about the SSIS script component as Destination so, we are selecting the Destination option
Next, drag and drop the OLE DB Source Output Arrow to this script component
STEP 3: Double click on the Script component will open the following editor to configure the properties. Please see Script Component as a Transformation to understand these properties
STEP 6: Within the Input Columns tab, you can cross-check the input columns.
STEP 7: Within the Connection Manager tab, you can add the OLE DB, ADO, or Flat File connection manager. So that you can call the connection from the script file. For this example, we will directly provide the file path inside the C# script
STEP 8: Within the Script tab, please click on the Edit Script.. button to write the actual C# Script
Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the Input0_ProcessInputRow(Input0Buffer Row) function
STEP 9: Add your custom C# code here. For this example, we are reading each row and column present in the source data to the text file.
Code that we used in the above SSIS Script Component as Destination screenshot is:
C# CODE
// Creating two private Variables
private StreamWriter textWriter;
private string columnDelimiter = ",";
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
public override void PreExecute()
{
base.PreExecute();
/* Establish the Connection before the data flow begins.
If you place this code inside the Input0_ProcessInputRow() then it will
an error
*/
textWriter = new StreamWriter(@"D:\FILE EXAMPLES\Write.txt", false);
}
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
public override void PostExecute()
{
base.PostExecute();
// Closing the Connection.
textWriter.Close(); // It is always advisable to close the Connection
}
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
/// string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
/// Row.ZipCode = zipCode
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
textWriter.Write(Row.FirstName);
textWriter.Write(columnDelimiter);
textWriter.Write(Row.LastName);
textWriter.Write(columnDelimiter);
textWriter.Write(Row.Education);
textWriter.Write(columnDelimiter);
textWriter.Write(Row.Occupation);
textWriter.Write(columnDelimiter);
textWriter.Write(Row.YearlyIncome);
textWriter.Write(columnDelimiter);
textWriter.Write(Row.Sales);
textWriter.WriteLine();
}
I suggest you read the comments as well. Once you finished editing the Script, Please close the main.cs file, and click OK to finish creating our Package
STEP 10: Right-click on the SSIS Script Component as Destination Package in the Solution Explorer, and select Execute Package.
From the above screenshot, you can observe that our SSIS Script Component as Destination Package has executed successfully. Let’s open the text file and see whether the data are written or not.
