SQL Server Performance Forum – Threads Archive

Use GlobalVariable in DynamicPropertiesTask

Hi I’d like to set a GlobalVariable (e.g. EffectiveDate) in a ‘DynamicPropertiesTask’ right at the start of my DTS package. I created a DynamicPropertiesTask and specified a SOURCE of ‘Query’. I put my SQL in the Query box like this select EffectiveDate from Task_table
where Jobname = ‘Job_1’ and this works OK. What I’d like to be able to do is run this package with a few different variations of a bat file, and so I’d like to be able to specify the ‘Jobname’ value in a GlobalVariable in the bat file. I know how to do that … but the sql box does not seem to allow me to specify a global variable in my SQL (in the place of ‘Job_1’. IS there a way to do this, e.g. select EffectiveDate from Task_table
where Jobname = ? and then to tell DTS that the ? refers to GlobalVariable Jobname? Any help appreciated
Thanks
Bennie

---

Hello there I have done similar thing in a my project. U just want to pass the jobname dynamically to the package.
for that what u do is just put the jobnames that u want tp pass dynamically in a table lets say "DynamicJobNames"
then create an activex use connection command and recordset objects to fetch each file name for example
myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local);——–"
mySQLCmdText = "Select * from DynamicJobNames"
myRecordset.Open mySQLCmdText,myConn,1
If myRecordset.RecordCount < 1 then
Main = DTSTaskExecResult_Failure
Else
for i = 1 to myRecordset.RecordCount
JobName = myRecordset.Fields("JobName").value select EffectiveDate from Task_table
where Jobname = JobName I have done this for fetching the file name and then then data from those files is transfered to the sql db after formatting it Try this Pallavi

---

Hi – thanks for your reply. Rgds
Bennie

---

]]>