Post reply

Running a DTS package from VB.

  • November 12, 2003 at 4:32 pm

    #151974

    Is it possible to run a DTS package from VB? If so how do I do this?

    The nearest I've seen to this is running a DTS package from ASP. Is it possible to use a similar technique in VB?

    Running DTS from ASP

    Any advice would be much appreciated,

    thanks,

    Matt

  • November 13, 2003 at 1:53 am

    #481801

    First of all you need to add a reference to the Microsoft DTSPackage Object Library.

    Then in your app do something along the lines of:

    Dim oDTSPackage as DTS.Package2

    Set oDTSPackage = New DTS.Package2

    oDTSPackage.LoadFromSQLServer ServerName,,,DTSSQLStgFlag_UseTrustedConnection,,,,PackageName

    oDTSPackage.Execute

    Set oDTSPackage = Nothing

    (replace with the correct parameters)

    You may want to do a few extra bits depending on what your package does but this is the basic Idea

    Simon.

  • November 13, 2003 at 10:02 am

    #481913

    Matt,

    First off, here is the code and article 252987: http://support.microsoft.com/default.aspx?scid=kb;en-us;252987

    (Scroll down, the page's html is messed up)

    Simon is right. I struggled with this some time ago, which ultimately resulted in the $245 call to MS. It does work, but be mindful of the security context you are in as the package executes from the ASP page. This was where I ran into problems. The ASP user (using the IUSR account) needs to have the proper permissions to access all the resources the DTS package calls. Hopefully you are on Win2k Server. We did this back in the NT4/SQL7 days and the package accessed resources on three different servers. If this is your case you will have problems, especially if IIS is on another server than SQL Server. NT4 did not like "server-hopping" like this.

    Check out the article. The cool thing is that you specify SQL Security, or Windows.

    Hope this helps,

    JR

  • November 13, 2003 at 10:46 am

    #481922

    you can do it by schedule DTS as a job, then call the job from your asp page.

  • November 26, 2003 at 5:23 pm

    #483845

    Thanks for all your suggestions.

    I am trying sismith's suggestion however when I try to run it I get an error message saying it can't find the server or that access is denied. Will I need certain admin rights to run this? Or am i doing something wrong? The code I ahve written is near identical to your suggested code:

    Dim oDTSPackage As DTS.Package2

    Set oDTSPackage = New DTS.Package2

    oDTSPackage.LoadFromSQLServer MyServer, , , DTSSQLStgFlag_UseTrustedConnection, , , ,MyDatabase

    oDTSPackage.Execute

    Set oDTSPackage = Nothing

  • November 27, 2003 at 1:46 am

    #483872

    Check that your server is set up for Mix authentication mode and that the login under which you application will run has access writes to run the package, alternativley you can use the sa login and password instead then it wont matter who is running the program. to use the sa login use:

    oDTSPackage.LoadFromSQLServer "MyServer","sa" ,"saPassword" , , , , ,"MyPackage"

    also not the last parameter is the name of your package not the database (dunno if thats a typing error by yourself but just in case)...

    Simon.

  • November 27, 2003 at 4:15 am

    #483886

    Thanks for your help. It was a typo on my part in the message. The problem I had was that the Server and Package name were not enclosed in quotes. It now works a treat. Many thanks,

    Matt

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply