I’m not writing about technical issues for a long time ... but I’ll start to diversify more the content of this blog. Today I’m writing about script component in SSIS that people ask frequently in Microsoft forums and in http://www.experts-exchange.com/.
To access the SQL Server or other sources, there are some standard components provided with the SSIS that I always suggest for almost the cases, but for some complex issues that require some customization, this post could be useful. If you understand the script component logic, and know some .net programming, you can get data from any source.
1. Open Business Intelligence Development Studio (BIDS), and create a new package.
2. Add a dataflow to your controlflow (There is a script task in the controlflow, but I’ll write about Script Component inside dataflow)
3. Create the table Contry for this example
4.Create and configure ADO.NET connection with the name “cmSQL_ADONET”
5. Configure the script component setting the script connection to the connection created in the previous step, and add the columns you need to the SSIS pipeline
6. Open the design script to add the customized code and try to understand the logic of the component. You have five main procedures: AcquireConnections, CreateNewOutputRows and ReleaseConnections, preExecute and postExecute. In this example, I only use the first three procedures but you are free to use the procedures you want.
7. You can see the results from the pipeline grid view.
This example is not dificult, but its the first step to give you more complex packages that I'll try to show you in a future post. If you want the code of this package, let me know and I send you by email instantly.