Passing parameters to a DTS

[TomR100]

Hello,
The DBA requested that we stop performing a BCP in our stored procedures. He suggested that we call a DTS and pass parameters there.

Is this possible?
Does anyone have any examples?

The developer that created the original proc creates a #TEMP table and blows it away at the end of the run. Do we need to create a table in the database in order for the DTS to work?

Thank you,
TomR100

 

[JohnDTampaBay]

Why would you need to pass a parameter to the DTS package? Does the source data filename change?

1 - Create a DTS package that will create a global temp table (##temp) and import the source data file into it. If the source filename will change, use an ActiveX script or Dynamic Properties task to populate the source filename from a DTS global variable.

2 - If the source filename will change, add code to your stored procedure which will populate the DTS global variable that represents the source filename.

3 - Change the BCP call in your stored procedure to call the DTS package.

After the package runs, the global temp table should be populated. Don't forget to drop the global temp table at the end of your procedure since it will persist after the session ends.

These are very broad instructions. You can find more information on calling DTS packages from procedures and populating global variables from procedures at

http://www.sqldts.com.

Good luck!


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.