Developing Matt

My Technical Journal

Dynamic SSIS?

leave a comment »

It’s not that difficult, but without much documentation out there SSIS (Sql Server Integration Services) can be a real pain. One hurdle I had a hard time crossing is the fact that it doesn’t seem dynamic at all. Because it’s replacing DTS, one of the main uses of SSIS is to transfer data into sql server. What I want to do is, on a weekly basis, transfer data into a table. Now, I could parse the data myself and write a big ‘ol program to do it, but SSIS touts it’s ease of use, so I was determined to make this work. All complaints aside, I made it work. I call my SSIS package from another application (after downloading the appropriate file to the appropriate place). When all set, I call and tell my package where to pull the data from and where to put the data. CnConnect is the name of the database where it is going (in my Package). “text file” is the name of the text file in my ssis package. As long as the format of the file or table doesn’t change it works (and has been working for a year now):

Dim transferapp As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pack As Microsoft.SqlServer.Dts.Runtime.Package = transferapp.LoadPackage(“mytransferpackage.dtsx”, Nothing)
pack.Connections.Item(“cnConnect”).ConnectionString = “Data Source=Servername;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;”
pack.Connections.Item(“text file”).ConnectionString = WHERETHEFILEISLOCATED
Dim result As New Microsoft.SqlServer.Dts.Runtime.DTSExecResult
result = pack.Execute
If result = 0 Then Return True Else Return False

Advertisements

Written by matt

August 2, 2007 at 2:34 pm

Posted in Sql Server, SSIS, VB.net

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

%d bloggers like this: