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)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_misc_68tv.asp
-
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?
Andy
-
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
Brian Knight
Free SQL Server Training Webinars
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply