Solved

change DTS connection properties

Posted on 2000-05-02
6
527 Views
Last Modified: 2013-11-30
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!
0
Comment
Question by:rreinmuller
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
Comment Utility
rreinmuller,
Your code posted will be very useful to determine what the problem is.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:rreinmuller
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 30 total points
Comment Utility
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
 

Author Comment

by:rreinmuller
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now