Post reply

passing paramter to ssis pakage from sql server 2005 job

  • June 17, 2008 at 5:01 am

    #129532

    Hi all,

    I have to pass global paramters to ssis pacakge which i suppose to call from a asp.net 1.1 page.

    I am making a call to sp_start_job system stored proc which help me to run the job .

    Now my problem is that i have to pass the paramter from the asp.net 1.1 page to sp_start_job and frm there to SSIS pakage ???

    is there any way out frm dt ...

    Thanks in advance 🙂

  • June 17, 2008 at 5:41 am

    #829686

    Here are three options:

    The easiest solution would be to write your parameter information to a table and use an Execute SQL Task to get the information from the table into your variables in the package.

    The second solution is to modify the job step before starting the job. This would require your login to have permissions to modify jobs, so I would not recommend this.

    The third option would be to use a package configuration file for your SSIS package and have the web page save the values for your parameters in the config file before starting the job.

    Since you are using the job agent, and I assume you are not dynamically creating the job, you cannot have two users running the package at the same time, so this should be pretty easy to manage.

  • June 17, 2008 at 6:08 am

    #829695

    thanks for your reply ...

    but i am using asp.net 1.1 and i suppose that if i am not having Microsoft.SQLServer.ManagedDTS.dll

    so i am not able to use the 3rd option suggested by u....

    so i am left with only one option that is 1st one but my concern on

    that is - if 2 person simultaneosly triggers the sp_start_job then they will be facing the problem that they might get the wrong data ???

  • June 17, 2008 at 6:15 am

    #829701

    The job agent does not support running the same job at the same time, so if you need to support multiple users simultaneously running the package you have a major design issue to start with.

    If you want to support multiple users at the same time, you either need to dynamically create the job (so option 2 would work) or you need to directly launch the package and not use the job agent (making it possible for you to simply use SETVALUE at the command line or passing in the parameter values through the SSIS object model).

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

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