Thursday, 23 April 2015

Column Count in SSIS

         Here I will explain how to calculate the column count in SSIS by using Script Component.


         In SSIS Row count transformation is available but Column count transformation is not available but in some situations we want the how many columns are participated in particular area in package and what are the data types of columns and names also. Now I will explain to calculate column count by using script component.

• OLE DB Source is used to get the [Person].[Address] Table data from database by using SQL Command from Adventure Works database.

• Derived column component is used to create new column [full address] from the existing  columns.

• Data Conversion component is used to convert [full address] from Unicode String to String [DT_STR].

• In the Script Component
     Script tab: ReadWriteVariables à User::Column_Count
     Input Columns: Select all input columns depends on requirement and the column count value             also depends on it.
     Input and Outputs: Create Output Columns àCount [DT_I4].

• Code of Script Component

1. Initialize the local variables of ( i ) (Column count) , Rowno.
2. Local value of ( i ) is assigned to the package variable CoulumnCount.
3. Display the Column index.
4. Display the Columntype and Column Name.
5. Increment the column count of variable ( i ).
6. Display the Column count value.
7. Rowno value is increment by one.

• Finally the value of Column Count is stored in package variable.

Note : The Column count value is depends on how many columns selected as input columns in the script component.

Let me know what you think about this article.

1 comment: