DTS - Specifying Connections Dynamically

  • Hi.

    Apologies if this has been discussed before - I'm a total newbie to SQL Server (tho' I've a fair bit of Oracle experience).

    I've got to put together a DTS package for transferring data to SQL Server 2000 - from Oracle. And it's going to be used for transferring data from/to several different pairs of servers, so I have to be able to get the package to take the source & destination as dynamic parameters at run-time.

    I've been digging, but haven't found anything definite on how to do this - so if anyone can enlighten me I'd be MUCH obliged!

    Thanx, Dave.

  • set dtsPackage = DTSGlobalVariables.Parent

    Set sourceConnection = dtsPackage.Connections("Text File (Source)")

    sourceConnection.DataSource = DTSGlobalVariables("PollingBaseDirectory").Value

    The preceding 3 lines are in a DTS I use to upload a text file as specified in the package global properties.

    They demonstrate the principle but I cannot give you an explicit ORACLE example.

  • Review http://www.sqldts.com/default.aspx?210 and its links

    also view http://www.sqldts.com/default.aspx?298

    sqldts.com rocks!

    BTW I've used both. The first one gives me problems and may have to do with the way our server is configured. The second one is a bit more complex but has not given me problems on the same server.

  • Hi Folks

    Many thanx for the replies. I've managed to get things working..... not through ActiveX, but through putting a Dynamic Properties Task in the package to set the server connection properties from two global variables & then specifying those global variables in the DTSRun command line.

    Basic, I know - but it seems to be working. And in tests, when the first pair of source & destination servers I tested with (whose details I originally hardcoded into the package) were shut down & I specified different source & destination servers for those global variables in the command line, things seemed to run perfectly; the expected data ended up on the specified destination server with no problems.

    So, my final question is - does anybody know of any potential problems with this approach? And if so, how can I get round them?

    Thanx

    Dave.

Viewing 4 posts - 1 through 3 (of 3 total)

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