Assigning Sequence Number from SSIS
Assigning Sequence Number from SSIS
(SQL Server Integration Service)
By : Kasim Wirama, MCDBA
This article will show you how to assign sequence number from SSIS when the destination table has integer column intended for sequencing, but not set it to identity column’s attribute for some reasons. I assume you have good basic understanding, and could create ad hoc SSIS package in Business Intelligence Development Studio (BIDS) and you know BIDS IDE such as Control Flow designer and Data Flow designer, creating variables, and so on.
Let’s say I create source data in form of query like this below :
SELECT ‘mydata’ AS col0, ‘xx’ AS col1, 0 AS Id
UNION ALL
SELECT ‘mydata1’, ‘xx1’, 0
UNION ALL
SELECT ‘mydata2’, ‘xx2’, 0
UNION ALL
SELECT ‘mydata3’, ‘xx3’, 0
UNION ALL
SELECT ‘mydata4’, ‘xx4’, 0
UNION ALL
SELECT ‘mydata5’, ‘xx5’, 0
UNION ALL
SELECT ‘mydata6’, ‘xx6’, 0
From the query above, I need to change value of Id column from 0 to sequence number with 1,2,3,…so on same results if you assign identity attribute on a column.
For demonstration purpose, I just create Recordset Destination type for the final destination table. That’s the situation above I describe, now I tell you how to achieve it by SSIS.
Create new package in BIDS, and create new variable (let’s say v variable at package level with object type). Create data flow task, and inside the data flow task, put above query into OLEDB Source and connect it to Recordset destination type. You assign variable v into VariableName property of the recordset destination component. Don’t forget to check all column (at least one column) at Input Columns tab.
Now back to Control Flow designer, drag Script Task component, and connect it with previous component (Data Flow task). The Script Task component has ReadWriteVariables with v variable, then add the script to update Id column incrementally, one caveat here, you cannot convert ComObject type to DataSet type, instead you instantiate OleDbDataAdapter object and call its Fill method to fill records to new DataTable object.
Until this step, the job is 80% job done, now you need to add one more DataFlow Task into Control Flow designer. I would like to display updated value in the second one. Inside second data flow task, drag Script Component, and set it to source component.
Double click Script component, add output columns to col0, col1 and id respectively, and adjust the DataType property for each of them. Click Script section, and set ReadOnlyVariables to v variable. And, inside the script, you iterate for each item inside the v variable, and call AddRow method of Output0Buffer, and assign columns of Output0Buffer to each columns of current iteration item of the v variable.
Next you add RecordSetDestination and connect arrow from the script component to RecordsetDestination component, and add data viewer in the arrow so you can observe the Id changes before they flow into RecordsetDestination.