Use SSIS to run custom .Net code based on data in a table

by Heathesh 3. February 2010 00:06

The requirement was to use an SSIS package to read data from a table, and once the data had been read based on a field in the table some processing (.Net code) had to occur for each record.

This is how I went about solving the problem:

1. Create a data flow task and open it (double click it).

2. In the "Data Flow" tab add an "ADO Net Source". Setup the connection to the database and point it to the required table.

3. Next add a "Script Component" and select "Transformation" as the way the script will be used. Now connect the green arrow from "ADO Net Source" to the "Script Component". This indicates the flow of the process, and that you want the package to run the "ADO Net Source" first to retrieve the data and pass it to the "Script Component".

4. Double click the "Script Component" and select "Input Columns" on the menu on the left of the pop up window. In the screen that opens, select the relevant columns you wish to use for processing.

5. Next choose "Script" and click the “Edit Script” button at the bottom right of the window.

6. When the script opens, there should be a method called "Input0_ProcessInputRow" or something similar.

7. Within that method, you will notice that there is a Input0Buffer object being passed into the method called "Row". Using "Row" you will have access to all the columns you selected above, for example if you selected a column called "Name", you will now be able to access the column data using "Row.Name" in your script code.

8. Now add the relevant references to the script project, and code whatever you wanted to do with the records.

9. Finally, if you need to do something after this, or need to send data out from a "Script Component", select “Input and Outputs” and then expand “Output 0”. Then click on the “Add column” button and add columns and types you want to return.

10. As before in the script code the columns specified will be available in the above method using the “Row” object to access them. Using your script you can then set the values of your output columns, and they will be available to you in your next step.

Happy coding!

Converting a SQL text value to varchar in SSIS

by Heathesh 13. October 2009 20:10

When I first tried this I used a script conversion task, with a script that would Convert.ToString() the text value to the string value I wanted. However, the ToString was returning the type name, and not the converted string.

It seems the best way to do this is to use two Data Conversion tasks. First convert the value to a DT_TEXT field. Next create a Data Conversion and convert the DT_TEXT value to a DT_STR value.

This achieved the desired result.

Tags: , , , ,

SQL Server Integration Services (SSIS)

Powered by BlogEngine.NET (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)


Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions


Tag cloud


<<  May 2017  >>

View posts in large calendar