DTS Query using Global Variable

  • I am trying to pass one global variable into a DTS package then set other dynamic properties with the Query option. For example if I pass in global variable gstrSourceTextFile = "MyFile" to the DTS package, can I use that global variable in queries say to set the UDL Path of the Destination DB. I have already built a stored procedure usp_GetDestDBForSourceTextFile which only accepts source text file and returns the destination DB. But when I use EXEC usp_GetDestDBForSourceTextFile gstrSourceTextFile it doesn't seem to work. The whole point of this is to only have 1 global variable input and all other be table driven. Any suggestions....Jeff

  • Haven't had much experience passing in variables to DTS packages...let me do a little reading and see if I can help.

  • Haven't found much info. Here is a link below that may be useful, but from the sound of it, you already have a handle on how to do it, its just not working. If you'd like, you can email me the code(stored proc and dts code) and I'll try and take a look at it. (jwiner1@cfl.rr.com)


  • Do you know the stored proc is executing for sure? How about adding some logging to the proc so you can see if it's running and what value was passed?


  • Are you using SQL Server 2000 or 7.0? There's a huge difference when you deal with this topic. This is a great question though and should be fun. This sounds like FAQ material.

    Brian Knight


Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply