Retrieve Records in SSIS Script Task - Netwoven

Retrieve Records in SSIS Script Task

By Subhendu Das  •  December 16, 2013  •  14586 Views

Netwoven Blog

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.

Retrieve Records in SSIS Script Task

Steps

1. Go to Start >>All Programs>> Microsoft SQL Server 2012>>

Click on SQL Server Data Tools

Retrieve Records in SSIS Script Task

2. Visual Studio >> File >>New >>

Click on Project

Retrieve Records in SSIS Script Task

3. Select Business Intelligence Under Installed Templates >>Integration Service Project
Click Ok Button

Retrieve Records in SSIS Script Task

4. Now you can see inside Visual studio >> solution Explorer >> Package.dtsx

Retrieve Records in SSIS Script Task

5. Double click on Package.dtsx file and you can see the design editor of that package

Retrieve Records in SSIS Script Task

6. 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

Retrieve Records in SSIS Script Task

7. Add another control script Task from SSIS toolbox to Control flow tab

Retrieve Records in SSIS Script Task

8. Create a connection between Data Flow Task and Script Task using the down side arrow of Data Flow Task

XAML 4
Retrieve Records in SSIS Script 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

Retrieve Records in SSIS Script Task

10. It will open a Window, Bottom side of your visual studio

Retrieve Records in SSIS Script Task

11. Click on Add Variable

Retrieve Records in SSIS Script Task

12. Give a Name to the variable and Select Data type as Object

Retrieve Records in SSIS Script Task

13. Double click on Data Flow Task this will bring you data flow tab

Retrieve Records in SSIS Script Task

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

Retrieve Records in SSIS Script Task

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.

Retrieve Records in SSIS Script Task

16. Click on the New button and we will get the Configuration ole DB Connection Manager Window

Retrieve Records in SSIS Script Task

17. Again, Click New button, put the server Name and Select the database Name in the Connection manager window and click ok.

Retrieve Records in SSIS Script Task

18. Now select the Table Name

Retrieve Records in SSIS Script Task

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.

Retrieve Records in SSIS Script Task

20. Add another control into the Data Flow Tab called Record set destination

Retrieve Records in SSIS Script Task

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 whether 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.

Retrieve Records in SSIS Script Task

25. Click on the Edit Script Button , this will open another project called Vista Project, Inside 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.

26. We’ll add a namespace for ole DB data adapter using System.Data.OleDb;

And add some lines of code inside the main method or any other sub method

OleDbDataAdapter da = new OleDbDataAdapter();

DataTable dt = new DataTable();

da.Fill(dt, Dts.Variables[0].Value);

27. Finally we can get all the records passed from our SSIS package inside the data table object.

Output

Subhendu Das

Subhendu Das

Subhendu Das is a technically competent IT Professional offering a distinguished career donning leadership roles for over 18 years primarily in IT Infrastructure Services along with a 12 years’ experience in IT Education Industry as a lead Educationalist. Subhendu has been working as a Senior Manager – IT Infrastructure with Netwoven and he is driving a team of IT Administrators and building sound IT Infrastructure for developers and remote servers in US. He is also actively involved with various client infrastructure migration, SharePoint, Exchange and Office 365 projects. Subhendu holds a Bachelor of Science from Calcutta University and also is a graduate from National Institute of Information Technology. He is a Microsoft Certified professional with certifications in MCSE, MCITP, MOS, MCTS, MCSA.

1 comment

  1. Thanks for the wonderful article. I am new to SSIS. Your article helped a lot. Thank you again

Leave a comment

Your email address will not be published. Required fields are marked *

Dublin Chamber of Commerce
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Fast Track
Microsoft Partner
MISA
MISA
Unravel The Complex
Stay Connected

Subscribe and receive the latest insights

Netwoven Inc. - Microsoft Solutions Partner

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex