executing a DTS from a stored proc....
-
I have a stituation where I need to be able to run a DTS package to clean up some data. The only thing that changes in the DTS is one number. Unable to find away to pass this variable to the package directly I made a table and a stored proc that updates this tables value. Then I run the DTS pack. This all works fine but is manual. If I could fire the DTS from the stored proc then this application would rebuild it's corrupt data on the fly. But I can't for the life of me figure out how to fire the DTS without clicking on the lil green arrow, running from a cmd prompt or using windows run utility. Any ideas? ----------------------------------------- Real programmers don't document. If it was hard to write, it should be hard to understand.
-
I have a stituation where I need to be able to run a DTS package to clean up some data. The only thing that changes in the DTS is one number. Unable to find away to pass this variable to the package directly I made a table and a stored proc that updates this tables value. Then I run the DTS pack. This all works fine but is manual. If I could fire the DTS from the stored proc then this application would rebuild it's corrupt data on the fly. But I can't for the life of me figure out how to fire the DTS without clicking on the lil green arrow, running from a cmd prompt or using windows run utility. Any ideas? ----------------------------------------- Real programmers don't document. If it was hard to write, it should be hard to understand.
You should be able to run the DTS package from code, so lets say that your
recordsetreturns 0 which means you would need to fire your DTS package to rebuild the data you could do something like this, (at least in VB6 :omg: )'=========================================================
'Include Microsoft DTSPackage Object Library in ReferencesDim dts As DTS.ExecutePackageTask
dts.PackageName = "[YourPackageNameHere]"
dts.RepositoryDatabaseName = "[YourDatabaseNameHere]"
dts.ServerUserName = "[YourUserNameHere]"
dts.ServerPassword = "[YourPasswordHere]"
dts.Execute(Package, PackageEvents, PackageLog, DTSTaskExecResult_Success)HTH Nick Parker
-
You should be able to run the DTS package from code, so lets say that your
recordsetreturns 0 which means you would need to fire your DTS package to rebuild the data you could do something like this, (at least in VB6 :omg: )'=========================================================
'Include Microsoft DTSPackage Object Library in ReferencesDim dts As DTS.ExecutePackageTask
dts.PackageName = "[YourPackageNameHere]"
dts.RepositoryDatabaseName = "[YourDatabaseNameHere]"
dts.ServerUserName = "[YourUserNameHere]"
dts.ServerPassword = "[YourPasswordHere]"
dts.Execute(Package, PackageEvents, PackageLog, DTSTaskExecResult_Success)HTH Nick Parker
Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end; where I need to look back and see how I got in this situation. The problem is probably my implimentation. The DTS takes approx. 4-5 hours to run, so anything through IIS is out. Thanks again though. :) Real programmers don't document. If it was hard to write, it should be hard to understand.
-
Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end; where I need to look back and see how I got in this situation. The problem is probably my implimentation. The DTS takes approx. 4-5 hours to run, so anything through IIS is out. Thanks again though. :) Real programmers don't document. If it was hard to write, it should be hard to understand.
pmenefee wrote: Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end; Actually, if you have your DTS designated to a job then I think what you should check into is
sp_start_job. This should work from within a stored procedure. :) HTH Nick Parker
-
pmenefee wrote: Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end; Actually, if you have your DTS designated to a job then I think what you should check into is
sp_start_job. This should work from within a stored procedure. :) HTH Nick Parker
You can use: xp_cmdshell and dts run utility to execute DTS package from store procedure. You can create statement like exec master..xp_cmdshell 'dtsrun /Uuser_name /Ppassword /Sserver_name /Npackage_name /Mpackage_password' Please read more about xp_cmdshell & dtsrun from SQL Server Books Online. Regards