November 9, 2001 at 8:50 am
Can someone please help me understand how to call a DTS package from a stored procedure and at the same time pass in a file name to the package. The file name will be the file that data will be exported to. Thanks ahead of time for your help!!
Jason
November 11, 2001 at 3:24 pm
There are several solutions I can think of:
(1) You can use the sp_OACreate etc stored procedures. There is an article in Peterson's SQL Server 2000 DTS for this. Basically you need to create a reference to the dts com object and then set the property of a global variable to hold the new filename.
(2) If you're using SQL 2000 you can use the dynamic properties task to read in the filename from an ini file (resurfaced from good old days of Win 3.1!). DTSRUN can be used to run the package (just schedule it and then use sp_start_job to run the resulting job). This is definitely the easiest option, but hte filename isn't sent into the sp like above, and may not be the versatile soln you want.
(3) There must be a way to send in the argument from an sp and then use the /A flag of DTSRUN. Doing this seems tricky, and the only way I can think of is using xp_cmdshell which may give undesired priveleges to your user.
Maybe someone else knows how to do this more easily?
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 11, 2001 at 4:25 pm
I think maybe it's worth rethinking the idea of passing the file name to the package - a better approach would be to read the file name from somewhere - ini file (cmon Paul, it's the ought's, at least use XML:-)), maybe a table?
Other than that, you could set up a job to do it, then modify the job in your sp. I'd avoid xp_cmdshell even though you can get it to work that way.
Andy
November 12, 2001 at 7:25 am
Paul and Andy,
Thanks for the replies. I should have said that I was using SQL 7 because the DTSRUN utility doesn't support the /A until SQL 2000. I thought about using xp_cmdshell to execute the DTS package, but I guess what you're saying is that it is not reliable?? Also, one other little tid-bit, the file name has to be created on the fly, and part of the naming convention tells what kind of file it is (fixed-length, comma separated, etc.) Thanks for your input!
Jason
November 12, 2001 at 10:08 am
xp_cmdshell should work well. I'd schedule the pacakge and take the encrypted values to use for a xp_cmdshell.
Steve Jones
November 12, 2001 at 11:08 am
If you can't use /A due to sql 7.0 constraints, I'd go back to running the dts package as a job (2). Also, I'd use Andy's idea and write the input value to a configuration table on the database first - it seems the easiest soln, and could be done in the stored proc your application calls. The package can read the value from the table to initialise the output filename. Also, if I understand you rightly, you want to output to different file types, depending on the value of the input argument. If there are only a couple of options : eg flat file or xls, I'd predefine the packages for these options and just call the relevant package for a filetype. The decision of which package to call could again be done by the initial stored procedure. By the way in the OLE DB properties there is "File Format", so it should be editable through the connection object, but I'd only consider setting all the appropriate properties for each filetype if you really need the versatility (and are prepared for the hassle!).
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply