Post reply

Common Functionality among 10 DTS Packages

  • October 18, 2005 at 1:17 pm

    #89691

    Hi,

    I need to create a separate DTS Package to upload data from a different server. Data from 10 different databases residing on the same server need to be uploaded to my local server.  I am planning to write a separate DTS package to upload data from each database. 

    Each database has same tables and structure.

    The query to upload data from each database is same in all the DTS packages.

    Since, the query is same, i want to put this query in a user-defined function and use that in all the DTS packages.

    Inorder to use the common function in all DTS packages, i need to pass server.databasename.user.tablename as a parameter to the user-defined function.

    I am unable to pass tablename as parameter to a user-defined function.

    Can anyone please help me through this issue.

    Thanks,

    Siva.T

  • October 18, 2005 at 3:12 pm

    #598073

    Creating dynamic SQL is the wrong way to go about dynamic configuration in DTS. If everything is the same each time except for the connection string, you should use an ActiveX script task to modify the properties of the connection object.

    http://groups.google.com/groups?as_q=dynamic%20connection&as_ugroup=microsoft.public.sqlserver.dts&hl=en

  • October 19, 2005 at 8:59 am

    #598305

    Hi,

    Thank you for the response. At first i thought of using ActiveX script for dynamic connections.  Since each connection uses the same DataTaskPump, if any of the connections fail in uploading the data, then all the remaining connections will also fail uploading the data and the whole DTS package fails.

    The idea behind designing a separate DTS package is to keep track of failure when uploading the data.

    Please let me know your suggestions.

    Thank You,

    Siva.

     

     

     

     

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

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