Database Variable

• jlayne

  Old Hand

  Points: 379

  More actions

  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?

• philcart

  SSC-Forever

  Points: 47796

  More actions

  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

• jlayne

  Old Hand

  Points: 379

  More actions

  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?

• AnzioBake

  SSCarpal Tunnel

  Points: 4134

  More actions

  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.

• jlayne

  Old Hand

  Points: 379

  More actions

  September 12, 2003 at 5:37 am

  #473888

  Thanks for the help. Will give it a try.