Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-01-24 : 09:14:42
|
| Dan writes "I have executed a DTS package from a stored procedure using OLE Automation Objects and using xp_cmdshell. Executing DTSRun from xp_cmdshell I can set the value of DTS Global Variables.The package dumps a table to a .txt file. I can set the source table and the destination file using DTS Global Variables. I have no way to change the transformation.In DTS Designer I can set the source and destination connections and in the Transform Data Task I can click a button and make the destination fields match the source fields and the transformations are a simple copy. How can I do that from a stored procedure using either OLE Automation Objects or xp_cmdshell?I am using SQL Server 2000 running on an NT Server. The OS on the development machine is Windows 2000 Professional.Thanks,Dan H." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-24 : 09:31:29
|
| by using BCP ...(that's probably not the answer you wanted) ...I've looked into using ActiveX to manage transformation, but I haven't found a good way to do it. As best I can tell, the package will retain the transforms you last used, so if all your exports are the same format, you can run it once manually to "prime the pump" ... Maybe someone can show us the light.Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-24 : 09:44:42
|
| If the transformations are the same, meaning that the only thing that changes are the file names, why not set up the DTS package with a set of files, then in the stored procedure use xp_cmdshell calls to rename (or copy over) the source and destination files to match the names used in the package? In other words:DTS:Set up file source.txt-->dest.txtSPROC:EXEC master..xp_cmdshell 'rename c:\file123.txt c:\source.txt'EXEC master..xp_cmdshell 'rename c:\file987.txt c:\dest.txt'After the DTS package is run:EXEC master..xp_cmdshell 'rename c:\source.txt c:\file123.txt'EXEC master..xp_cmdshell 'rename c:\dest.txt c:\file987.txt' |
|
|
|
|
|
|
Change DTS Transformations from Stored Procedure