Wednesday, December 17, 2008

SSIS - Script Component as Source From SQL

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.
Regars,
Pedro

8 comments:

Amin said...

Hi Pedro,

Nice post, I just want to add my experience in using this component.
We had some Dos base applications which were written by Fox Pro, and I wanted to make an automated solution for extracting the data from fox into SQL server. The main problem was Persian character set. The character set in Fox is different from the windows and UTF, so I used this component to change the character set and automate importing mechanism.

Cheers my friend!!
Amin

Miky Schreiber said...

I also had a very difficult problem that I solved using the script component. I had a excel file source, but the columns in this excel were switched from time to time. Of course that in the classic methods we couldn't deal with such problem. Writing a script that makes order in this source solved the problem.

Pedro said...

Hi Amin and Miky,
Thanks for your visit to my blog!
This was a easy step to go throuth more detail into Script component. I'm trying to do it more dinamic or using only the script component or create a custom component to realize that!
Thanks!!!
Pedro
Cheers!

Anonymous said...

Hi Pedro,

Very interesting post, thanks! Any chance you'll attach the sample package to the post? If not, I'd be very happy to get a copy.

Thanks,
Ella

Anonymous said...

Hi Pedro,

Just wanted to thank you again for helping me figure out that it was the Data Reader that was causing my SSIS package to run so slowly.

I owe you a beer!

Ted Landres
ted.landres@verizonwireless.com

P.S.
Was checking out some of your photos - Nice looking girl friend you got there Pedro!

Pedro said...

Hi Landres,
It's a pleasure helping people in Experts-Exchange! It's fantastic not only to improve our knowledge but also to know very kindly people like you!
Yes... My girlfriend has not a nice good, she's fantastic and amazing person!!! Nothing could be better than working for pleasure and having a fantastic person on your side!!

Thanks Landres you are always welcome here!

Pedro

Unknown said...

Hi Pedro,

I am trying to use your script on SQL 2008. The problem I am having is with my SP which returns multiple result sets but this script only inserts the first one.

Is there a way to make it insert all the results sets?

TIA,

Jim

Pedro said...

HI Jim!
Welcome to my blog!
Tomorow I'll convert the package to SQL 2008 and I'll give you feedback. If not remember me... check my email in my blog profile!
Cheers!
Pedro

LinkWithin

Related Posts Plugin for WordPress, Blogger...