SQL Server Performance

Use GlobalVariable in DynamicPropertiesTask

Discussion in 'SQL Server DTS-Related Questions' started by bnaude, Dec 6, 2005.

  1. bnaude New Member

    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
  2. pallavi New Member

    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
  3. bnaude New Member

    Hi - thanks for your reply.

    Rgds
    Bennie

Share This Page