Post reply

Database Variable

  • September 11, 2003 at 10:07 am

    #106494

    I have several large DTS packages with many Data Pumps, ActiveX, Bulk Inserts, ans connections. I need to fire the packages at different databases (with same table and column names) on the same server. How can I set the database names to a variable, or what would be the best approach?

  • September 11, 2003 at 8:14 pm

    #473840

    This little script will set the necessary properties for each connection in your package. Put the script into an ActiveX task as the first step in your packge. Then change the the items as indicated. If you use a trusted connection then you can put anything you like for userid and password.

    You could store the values in global variables, or in a table which list the different values for each connection.

    

    Function Main()

    

    	Dim oPkg ' DTS Package object

    	Dim colConn ' DTS connections collection

    	Dim sPkgConn ' DTS Package connection item

    

    	' open DTS package object so we can get the package details

    	Set oPkg = DTSGlobalVariables.Parent

    	' open object referring to Global variables collection

    	Set colConn = oPkg.Connections

    	' for each item in the collection

    	For Each sPkgConn in colConn

    		' assign results from query to connection properties

    		sPkgConn.Datasource = <Server Name> '<--- Change to your server

    		sPkgConn.Catalog = <Database Name> '<--- Change to your database

    		sPkgConn.UserID = <User ID> '<--- Change to your user id

    		sPkgConn.Password = <Passord>'<--- Change to your password

    		sPkgConn.UseTrustedConnection = <0 for SQL, 1 for Trusted>

    	Next

    

    	Set colConn = Nothing

    	Set oPkg = Nothing

    

    	Main = DTSTaskExecResult_Success

    

    End Function

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    Edited by - phillcart on 09/11/2003 8:17:31 PM

    Edited by - phillcart on 09/11/2003 8:19:28 PM

    --------------------
    Colt 45 - the original point and click interface

  • September 12, 2003 at 4:56 am

    #473882

    Thanks for the help Phil. This appears to maybe correct the connections, but what about my tasks. Will this correct all the tasks by changing only the connections?

  • September 12, 2003 at 5:14 am

    #473884

    Tasks use a connection( referenced by name [Actually the DTS Connection Description]).

    By changing the Connection Properties the Tasks will be function correctedly. It is like changing the properties of DSN without changing the name.

  • September 12, 2003 at 5:37 am

    #473888

    Thanks for the help. Will give it a try.

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

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