change DTS connection properties

Hi!
I am calling a DTS package that moves data from one database to another. The DTS is called from a stored procedure.
I would also like to move the same data to other databases on the same server or another server.
I would like to change the connection properties from the stored procedure but I cannot find any help online on how to get to my connection properties (I am using sp_OACreate, sp_OAMethod, sp_OASetProperty).

Any help would be greatly appreciated!
Thanks!
rreinmullerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I'm glad you could work with my suggestion.
The property for the database name is catalog:

EXEC @hr = sp_OASETProperty @object, 'connections.item(2).catalog' , 'another database'
0
 
Gustavo Perez BuenrostroCommented:
rreinmuller,
Your code posted will be very useful to determine what the problem is.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In fact, your only problem is your don't know how to identify your connections and the properties.
OK, i did it only in VB/VBScript, so we need to translate it into TSQL with the sp_OA... procedures:
------ VB Code -------
For Each objCon In objPackage.Connections
    With objCon
      Select Case True
        Case .ProviderID = "SQLOLEDB.1"
          .DataSource = "Server"
          .UserID = "UserID"
          .Password = "PWD"
         
      End Select
    End With
  Next
---------------
objPackage is the variable of the package, corresponds to the value sp_OACreate returned.


Hope this helps

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rreinmullerAuthor Commented:
Hi! This is how my calls look like:

SET @dts_file = 'my dts file'

EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile', NULL, @dts_file, ''

EXEC @hr = sp_OAGetProperty @object, 'connections.item(2).datasource' , @property OUT
SELECT @property

EXEC @hr = sp_OASETProperty @object, 'connections.item(2).datasource' , 'another server'

I can change the server to a different server but I don't know how to change the database that I am working with.

Thanks!
0
 
rreinmullerAuthor Commented:
Thanks angelIII!
Unfortunately only changing the connection database did not help. :(
I have to also change the database inside each task (drop table, create table).
It worked fine for the drop table commands using:
EXEC @hr = sp_OAGetProperty @object, tasks.item(1).customtask.sqlstatement, @property OUT
SET @property = replace(@property,'old db', 'new db')            
EXEC @hr = sp_OASetProperty @object, tasks.item(1).customtask.sqlstatement , @property
but it did not work for the create table statements. It probably has to do with the way the create table statement is defined in more than one line. Any ideas?
Thanks a lot for your help!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You might not give the database name during SQL Statements if not really necessary.
"USE new db" statements in front of my Scripts / TSQL are my favorite place to precise which database i use.
so instead of CREATE "new db".dbo."table" i generally use
CREATE "table", because i know that i'm logged on as SA when executing scripts, and the database has been fixed by the USE statement (in DTS this is the connection settings)
Can you work with this?

Glad my other suggestions helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.