You are developing an SSIS package. You want to work with a record in a Script Task. This blog discusses how you can get access to the Records in the Script task using the Object Type variable.
1. Go to Start
>> Microsoft SQL Server 2012
Click on SQL Server Data Tools
2. Visual Studio >> File >>New >>
Click on Project
Select Business Intelligence
Under Installed Templates
>>Integration Service Project
4. Now you can see inside Visual studio >> solution Explorer >> Package.dtsx
5. Double click on Package.dtsx
file and you can see the design editor of that package
Now go to the SSIS tool box
and add (either drag and drop or double click) a Data flow task
controls inside the control Flow Tab
7. Add another control script Task
from SSIS toolbox
to Control flow tab
Create a connection between Data Flow Task
and Script Task
using the down side arrow of Data Flow Task
9. Now we need to add an object type variable, so go to the right most side of the Package Design Editor and click on Variables
10. It will open a Window, Bottom side of your visual studio
11. Click on Add Variable
Give a Name to the variable and Select Data type as Object
13. Double click on Data Flow Task
this will bring you data flow
14. We can use any kind of data source like Flat File Source
, Excel Source
, OleDb Source
etc. I have Used OleDB Source, as per project requirement.
So just drag and drop the OleDB source
control from SSIS tool box
to Data Flow Tab
15. Double Click on OleDB Source control and we will get the oleDB Source editor.
Inside the Connection Manager
we have to set the OleDB connection Manager.
Click on the New
button and we will get the Configuration ole DB Connection Manager Window
17. Again, Click New
button, put the server Name
and Select the database Name
in the Connection manager window
and click ok.
18. Now select the Table Name
19. We can choose selected columns from the selected table, just go to the columns tab and unselect the checkbox beside the column and click ok
Add another control into the Data Flow Tab called Record set destination
21. Connect the Ole DB source with Record set Destination
using the bottom side of Ole Db source control.
22. Double click on the Record set Destination , we’ll get the Advance editor for record set destination, inside the component properties tab there is a custom property called Variable Name. Select the object type variable (that we have previously created).
23. Go to the next tab called Input columns
and choose the columns as per your requirement, also we can set the usage type
the columns are read only
or read write
type. After all required settings click OK.
24. Go to the control flow tab
back and double click on the Script Task
control. And set the Object type variable that we had created as Read Write variables.
25. Click on the Edit Script Button , this will open another project called Vista Project,
that project ScriptMain.cs is the class file,
where we have to add some codes to access the Object type variable that we have passed from our SSIS package.
We’ll add a namespace for ole DB data adapter
And add some lines of code inside the main method
or any other sub method
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
Finally we can get all the records passed from our SSIS package
inside the data table