| Author |
Topic |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-03-12 : 17:32:29
|
| I have two connections defined in the DTS package. I want to be able to call the DTS package and dynamically change the source and destination of the transformation between the two connections. I have tried to do this but I am fairly inexperienced with DTS on this level. Any assistance would be greatly appreciated specifically any resources where by I can become more proficient |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-12 : 17:38:23
|
| www.sqldts.com is a good site for some advanced DTS stuff. Books Online has a list of the DTS object model (look under "Building SQL Server Applications/DTS Programming" in the Contents window) and I think there's some code examples also, that show how to change package settings and objects. |
 |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-12 : 18:29:56
|
| So I got the first part to dynamically change the source and destination with the following:Function Main() Dim oPkg, oDataPump Dim sSourceTable, sDestinationTable ' Derive the new table names sSourceTable = DTSGlobalVariables("strTableName").Value sDestinationTable = sSourceTable ' Get reference to the DataPump Task Set oPkg = DTSGlobalVariables.Parent Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask ' Set the new values oDataPump.SourceObjectName = sSourceTable oDataPump.DestinationObjectName = sDestinationTable ' Clean Up Set oDataPump = Nothing Set oPkg = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionProblem is that I get an error message that says: The Data Pump Task requires Transformations to be specified. So how do I dynamically define transformations? As to the advice of looking at the samples provided by help I don't have them. I know I can install the samples from the CD but I don't have the CD nor can I get it easily so that is not a resource for me. Any assistance is appreciated!! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-12 : 18:52:59
|
| James,The easiest way to work dynamic connections is to actually set up the the DTS package with real connections.Simple design the DTS using real connections and transformations...Create your global variables..Create a "Dynamic Properties Task" object and assign the appropriate Global variables to this task.Run you code that assigns the Global variables...And just antother thing.. Change all the tasks name to something meaningful.. It will take a bit of extra work but is well worth it!HTHDavidMTomorrow is the same day as Today was the day before.Edited by - byrmol on 03/12/2002 18:54:20 |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-12 : 19:50:46
|
| James,byrmol's solution will work only if you have SQL 2000. Otherwise the Dynamic Properties Task is not available. I haven't tried this, but when you go through the DTS Import Wizard and select a table you want to copy, click on the transformations button. Once in this screen there is a column mapping and transformations tab. Looking under transformations, there is a radio button that says Transform information as it is copied to destination. I pulled the following out of this and I would think you could somehow modify this in an ActiveX script in a DTS package. DTSDestination("TimeID") = DTSSource("TimeID") DTSDestination("MemberID") = DTSSource("MemberID") DTSDestination("TMonth") = DTSSource("TMonth") DTSDestination("TYear") = DTSSource("TYear") DTSDestination("Est_TDY") = DTSSource("Est_TDY") DTSDestination("Act_TDY") = DTSSource("Act_TDY") DTSDestination("Est_Hours") = DTSSource("Est_Hours") DTSDestination("Act_Hours") = DTSSource("Act_Hours") DTSDestination("Submitted_By") = DTSSource("Submitted_By") DTSDestination("Date_Submitted") = DTSSource("Date_Submitted") Main = DTSTransformStat_OKI would sure like to know if this works.Jeremy |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-12 : 20:16:50
|
| Jeremy,You only need the Client Tools of SQL2K for it to work...I have succesful used this solution against SQL7 in a web environment.DavidMTomorrow is the same day as Today was the day before. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-12 : 20:45:01
|
| Let me explain what I am trying to do and see if I am going about it the right way. I want to pass a table name to a DTS package and have it copy data from one database on one server to another database on another server. Ideally I would use a linked server for this (both are SQL2000 boxes) but our DBA is super secure minded and doesn't like the security for linked servers. I figured that I would just pass a table name to a DTS package and have it do the copy for me. Am I making more trouble for myself than necessary or does this seem like a viable solution? The tables will vary so I can't just write a static DTS package to transform the data for me.Again, thanks in advance for lending your collective mental powers in my time of need. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-12 : 20:53:12
|
| I think your DBA should read up on linked servers a little more. You cannot configure a linked server without specifying login credentials for it; in fact it can be made more secure than a DTS connection. I can tell you that if you do use linked servers, it'll be extremely easy to write some dynamic SQL that accepts a table name, any table name, and move the data between the two. It wouldn't need more than 5-6 lines of code, vs. dozens for a DTS program. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-12 : 21:27:12
|
| byrmol,I swear I learn something new in this forum everyday that I wish I would have know 6 months ago. Thanks for the correction.I know that Dynamic Tasking is more useful, but does anyone know if the ActiveX task I described earlier will work?JeremyEdited by - joldham on 03/12/2002 21:27:59Edited by - joldham on 03/12/2002 21:29:25 |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-13 : 08:58:11
|
quote:
...but does anyone know if the ActiveX task I described earlier will work?...
I have never tried this, but I don't think it will. Now if your source and destination tables have exactly the same DDL, then DTS might magically throw a copy column transformation in there for you (kinda like how it guesses the transforms when you physically set up a data pump), but I kinda doubt it. I don't see how you are going to write . . .quote:
DTSDestination("TimeID") = DTSSource("TimeID")DTSDestination("MemberID") = DTSSource("MemberID")DTSDestination("TMonth") = DTSSource("TMonth")DTSDestination("TYear") = DTSSource("TYear")DTSDestination("Est_TDY") = DTSSource("Est_TDY")DTSDestination("Act_TDY") = DTSSource("Act_TDY")DTSDestination("Est_Hours") = DTSSource("Est_Hours")DTSDestination("Act_Hours") = DTSSource("Act_Hours")DTSDestination("Submitted_By") = DTSSource("Submitted_By")DTSDestination("Date_Submitted") = DTSSource("Date_Submitted")Main = DTSTransformStat_OK
...without a huge pain in the a$$. You would have to take you DTSGlobalVariables("strTableName").Value and query syscolumns and then loop through that recordset dynamically creating your transforms. Set up the linked server, use OPENROWSET or bcp. Just about anything will be easier especially for a one time thing. If it had to run every night, for example, I would look into grabbing all the table you might possible need and moving them to a staging area on ServerB, then write a proc on ServerB to get data for only those that you want.Jay |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-13 : 09:18:58
|
| Thanks Jay.I wouldn't want to do it this way either. I like doing things the easier way as well.Jeremy |
|
|
|
john.brandon.s
Starting Member
1 Post |
Posted - 2007-07-31 : 22:26:23
|
| can any one tell me the solution for this question, even I have the same issue, if resolved plz let me knowthanks,john |
|
|
|
|
Dynamic Source and Destination in DTS