SSIS

SSIS – using connection object inside Script Component

There might be a situation where you need to use a connection object inside an Script Component. The scenario can be you need to Query a table based on some parameters during the transformation and use that result to do some process and so forth.

To accomplish this, you can type cast the connection object(referenced in script component) to the corresponding .NET connection (oledb or Ado.net) or you can use the connection string property and use it to establish a connection to the database.

I find using the connection string method to be more safe and reliable, rather than type convertion. In other words programming Convenience huh  🙂

Create a Simple SSIS package that reads data from AdventureWorks database.

 

 

 

 

 

 

 

 

 

 
 

In the script component reference oledb connection.

 
Use this connection string inside the Script component as shown below.

Hope this helps.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s