Pass paramter (file path) to DTSRun to import Excel file
-
Given a UNC path/filename to an Excel file, I need to be able to launch a DTS job from a proc and somehow pass the UNC path/filename to the DTS job.
Am I having delusions of grandeur, or can this be done?
Doug Brashear -
You can use the DTSRUN utility from a command prompt (to execute a command prompt in a proc, use xp_cmdshell in the master database). To pass the UNC to the DTS simply pass it as a global variable. An explanantion of how to do this can be found in the books online for SQL or you can do a search for passing global variables with the DTSRUN utility. Here is an example of what your procedure may look like. **Note the Data Type Number for a string value is 8
create procedure test_proc
as
exec master..xp_cmdshell 'DTSRUN /S "<ServerName>" /N "<DTSPackageName>" /E /M "<PackagePassword>" /A "<VariableName>:<DataTypeNumber>=<Value>"
go
-
Also notice that Global Variable names ARE case sensitive.
-
to create the first version of the DTS command string, use <SQL Install Root>\80\Tools\Binn\dtsrunui.exe; it's much easier to set it up that way. Then follow the advice above, substituting the new file name in the executed string (suggestion, concatenate the string before executing instead of at the same time)
In the DTS package you'll need to add a "Dynamic Properties" task, and change the "Connections/Text File (Source)/DataSource parameter dynamically. This will point the import file to the new file.
You mentioned a UNC file path. This will only work if the user executing the DTS package has access to that file path (NOTE: user is inherited from the executor of the proc if not set in the DTS command string). This is difficult if you are not using Windows authentication.
You should be able to figure it out from here!
cl
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply