Solved

change DTS connection properties

Posted on 2000-05-02
6
528 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
ID: 2771482
rreinmuller,
Your code posted will be very useful to determine what the problem is.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2772425
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
ID: 2773432
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 30 total points
ID: 2773474
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
ID: 2781285
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]
ID: 2785258
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 79
Error when loading the database 16 54
Need a starter for ETL protocol? 4 31
Sql query 107 22
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

943 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

15 Experts available now in Live!

Get 1:1 Help Now