January 30, 2006 at 5:20 am
Hi.
Apologies if this has been discussed before - I'm a total newbie to SQL Server (tho' I've a fair bit of Oracle experience).
I've got to put together a DTS package for transferring data to SQL Server 2000 - from Oracle. And it's going to be used for transferring data from/to several different pairs of servers, so I have to be able to get the package to take the source & destination as dynamic parameters at run-time.
I've been digging, but haven't found anything definite on how to do this - so if anyone can enlighten me I'd be MUCH obliged!
Thanx, Dave.
January 30, 2006 at 6:00 am
set dtsPackage = DTSGlobalVariables.Parent
Set sourceConnection = dtsPackage.Connections("Text File (Source)")
sourceConnection.DataSource = DTSGlobalVariables("PollingBaseDirectory").Value
The preceding 3 lines are in a DTS I use to upload a text file as specified in the package global properties.
They demonstrate the principle but I cannot give you an explicit ORACLE example.
January 31, 2006 at 10:12 am
Review http://www.sqldts.com/default.aspx?210 and its links
also view http://www.sqldts.com/default.aspx?298
sqldts.com rocks!
BTW I've used both. The first one gives me problems and may have to do with the way our server is configured. The second one is a bit more complex but has not given me problems on the same server.
February 2, 2006 at 3:29 am
Hi Folks
Many thanx for the replies. I've managed to get things working..... not through ActiveX, but through putting a Dynamic Properties Task in the package to set the server connection properties from two global variables & then specifying those global variables in the DTSRun command line.
Basic, I know - but it seems to be working. And in tests, when the first pair of source & destination servers I tested with (whose details I originally hardcoded into the package) were shut down & I specified different source & destination servers for those global variables in the command line, things seemed to run perfectly; the expected data ended up on the specified destination server with no problems.
So, my final question is - does anybody know of any potential problems with this approach? And if so, how can I get round them?
Thanx
Dave.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply