question

askthisoldSql avatar image
askthisoldSql asked

Need to run SSIS package in VS VB.net

I have a SQL 2008 package stored on SQL Server. I need to run this package through code in VS VB.net. pkg = App.LoadFromDtsServer("\MSDB\SSIS_Package1", sServer, Nothing) pkg.Execute The trick however is that I have only 1 data flow task and new change the query in code (date criteria change) With the Old DTS I could do this by manipulating the right Task and Property. How can I do this with VB.net and an SSIS package?
ssisprogramming-languagevb2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

west007 avatar image
west007 answered
Hi I dont now how to do with VB. But you can do it with DTEXEC.exe from SQl or Dos or even VB Regards
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askthisoldSql avatar image askthisoldSql commented ·
recall in DTS that you could change items in it on the fly in code. The most noteable for me was changing a query in the DTS on the fly. I need to do that same thing with SSIS.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Hi. You can change and create SSIS-packages programatically. Perhaps I would solve your particular problem with a query being changed by passing the query as a parameter and have a script task inside the SSIS-package change the elements accordingly. But if you want to do it from a .NET application, you can program against a new or existing SSIS-package. Either using the Microsoft.SqlServer.Dts.* namespaces, or perhaps somewhat easier, use EzAPI. Microsoft.SqlServer.Dts.*-namespaces: http://msdn.microsoft.com/en-us/library/ms135997.aspx EzApi: http://blogs.msdn.com/b/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askthisoldSql avatar image askthisoldSql commented ·
Thanks Magnus. While the EZAPI looks exactly like what I need - I'll not be allowed to download it onto my machine. The MS blog I saw and I'm not sure it's that helpful. I have an SSIS package with 1 dagta task. the query in that task needs to change. I still see nothing in regards to code for this in VB.net that is related.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
I haven't really worked with programatically changing SSIS-packages. On the URL http://msdn.microsoft.com/en-us/library/ms136082.aspx is an example on how to access, add and change package variables. But what you are really looking at is programatically locating a component inside a data flow task, and change its SqlCommand attribute. Exactly how that's done, I don't know. A good starting point is probably the Integration Services Developer Guide: http://msdn.microsoft.com/en-us/library/ms136025.aspx Sorry for not being able to be more specific. All of the code samples are by default C#, but there's a tab to change language to VB.NET.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.