• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

change DTS connection properties

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!
  • 3
  • 2
1 Solution
Gustavo Perez BuenrostroCommented:
Your code posted will be very useful to determine what the problem is.
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
objPackage is the variable of the package, corresponds to the value sp_OACreate returned.

Hope this helps

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.

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Guy Hengel [angelIII / a3]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'
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!
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.
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now