In Data Flow task of SSIS, we have to define a Data Source as well as a destination to extract & load the data respectively. And as per the functionality of data flow task (DFT), metadata of the source fields has to be defined at the design time. That’s the input columns from the source must be mapped to the destination columns and for that we must know the schema of the input as well as the destination/output columns.
But in many business scenarios we come across the requirement where at runtime only we come to know about the structure of the input columns and thus at runtime only we have to decide for the destination columns, this is called dynamic mapping.
This type of dynamic mapping for source to destination can be achieved using script component. This script component can be used in three ways in data flow task:
- As a Transformation,
- As a Data Source
- As a Destination
When we drag & drop the script component from tool box, a dialog box will appear to choose the required type.
1. Transformation: Script component can be used to do some transformations on the input data, processing each & every row one by one. We can write custom code/script in it to make the required operations on the data. In this case total number of output columns would be same as that of input columns.
2. Source: If we select script component to be used as a Data Source, we can configure the component to generate the required number of rows as the output. In this case we can add as many columns we want in the output of the script component and then at runtime we can write code/script to add the rows in the output. We can also have multiple asynchronous outputs with different-2 names which can be loaded to different-2 destinations.
3. Destination: Sometimes we don’t want to dump/load the input data directly to the destination like OLEDB destination or Flat file destination; rather we want to decide for the number of columns/rows somewhere before actually loading the data. Here we can choose script component as a destination.
Script component should be used as a source, in case we are sure about the schema/columns of the input records. But in the component’s script editor we can process the data & generate the output rows based upon certain conditions. But here also we have to map the output of this component to the destination columns at design time only.
Now in case; we want to have dynamic mapping at runtime, we can configure Script component as a Destination. In this mode of component we can look at the input data & decide about the structure of the fields to be loaded into the final destination, based upon certain conditions which can be stored in database or variables.
Configuring Script Component as a Destination
Adding Connection Managers: Since in the script component itself, we are going to load data into the final destination, we need the connection manager(s) to connect to the final destination e.g. Flat File connection manager, OLE DB connection manager etc. On Connection Managers page of the Script Component Editor, we can add the already created connection manager or also can add new connection managers. All the added connection managers would be available in the script via Me.Connections. connectionname (vb.net) & this.Connections.connectionname (C#).
Input/ Output columns and Variables: We can configure the input columns, output columns as well as variables as is done in the normal script task or component.
Scripting the Destination Component: Its main configuration is the custom script (vb.net or C#) which is used to process the input data, decide over the number/ type of destination columns and then load them into the final destination.
There are many functions/subroutines which we can override in the script to acquire the added connection managers, process the input data, form the query at runtime & load the data into the destination etc.
Example: Let’s take an example in which we add one ADO.Net connection manager ‘myConnection’ to execute a query at the SQL server destination.
Note: Here we will connect to SQL Server via a managed connection manager (ADO.Net) because AcquireConnections function works for these only. Otherwise we’ll have to create connection in the script only; using the connection string of the defined connection manager.
Public Class ScriptMain
Dim conMgr As IDTSConnectionManager100
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
conMgr = Me.Connections.MyConnection
sqlConn = CType(conMgr.AcquireConnection(Nothing), SqlConnection)
Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand(“INSERT INTO MyTable(Col1, Col2) ” & _
“VALUES(@Value1, @Value2)“, sqlConn)
sqlParam = New SqlParameter(“@Value1“, SqlDbType.Int)
sqlParam = New SqlParameter(“@Value2“, SqlDbType.Int)
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
.Parameters(“@Value1“).Value = Row.ID
.Parameters(“@Value2“).Value = Row.Count
Public Overrides Sub ReleaseConnections()
Thus we can process the input columns, make connections and based upon required conditions; we can build our own queries to dynamically map them to the destination. In the similar way we can write to Flat file destination as well.