Solved

change DTS connection properties

Posted on 2000-05-02
6
545 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 143

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 143

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

724 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