none
How to set system date to a variable in ssis package from SQL Job?

    Question

  • Hello,

    I have a SSIS package, which has a date variable that needs to be passed. I am scheduling a SQL job from SQL Agent.

    Can anyone please tell me how can I pass the GETDATE () to the variable from the job and How to pass a specific date to the variable in order to rerun the package for a particular date?

    Thanks in advance.

    Thursday, October 03, 2013 3:28 PM

Answers

  • Hope this former threads help

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce60c91c-1042-479d-9ae5-12c082d94d74/passing-date-parameter-thru-a-job-to-ssis
    
    
    http://www.bidn.com/blogs/DevinKnight/ssis/1655/passing-values-into-an-ssis-package-at-runtime-from-outside-the-package

    Thursday, October 03, 2013 4:18 PM
  • Step1: Create a job in SQL Server Agent. Step2: Go to "steps" option in menu. Step3: In General Tab, Select "Package Source" as "File System" then browse your package. Step4: Go to "Set values" tab here set "property path" as "\Package.Variables["variablename"].Value" and "Value" as "10-03-2013"
    Thursday, October 03, 2013 6:00 PM
  • Hi,

    I believe it is not a problem to you to pass the value to package variable, it is maybe a bigger challenge to get system date time in command prompt and pass it to the dtexec parameters (SET switch).

    I have created a bat file for myself that gets data and time in correct format and passes it to the dtexec SET switch to pass it to package variable. The package variable is named VarDataTime, type datetime.

    @ECHO OFF
    set VarDateTime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2% %TIME:~0,8%
    
    dtexec /FILE "\"D:\SSIS\Package1.dtsx\"" /SET "\"\Package.Variables[VarDateTime].Value\"";"\"%VarDateTime%\""
    
    @ECHO ON

    Just change the path to the package, add additional parameters to dtexec if needed and then run bat file from command prompt typed job step.

    If you are using SQL Server 2012 and project deployment to SSIS catalog then you have an easier job. You can then use stored procedure catalog.set_execution_parameter_value. This is project parameter which you have to map to your package variable. After setting parameter values execute the package with catalog.start_execution.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    Thursday, October 03, 2013 6:47 PM

All replies

  • Firstly you should have package configurations enabled on the job. Secodnly, the varibale that passes date and is used in the package, needs to be configured in the package configurations. Once this is done, you need to provide the value of the variable in the configurations and package should use that value of the variable during execution...

    if the package configurations are not enabled, you will have to open the package and run it by providing specific date to the variable..


    Thanks, hsbal

    Thursday, October 03, 2013 3:47 PM
  • Thanks for the reply. Can you tell me how should i pass the system date value from the SQL job? I am trying to schedule a job to run the pacakges automatically.
    Thursday, October 03, 2013 4:00 PM
  • Hope this former threads help

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce60c91c-1042-479d-9ae5-12c082d94d74/passing-date-parameter-thru-a-job-to-ssis
    
    
    http://www.bidn.com/blogs/DevinKnight/ssis/1655/passing-values-into-an-ssis-package-at-runtime-from-outside-the-package

    Thursday, October 03, 2013 4:18 PM
  • Step1: Create a job in SQL Server Agent. Step2: Go to "steps" option in menu. Step3: In General Tab, Select "Package Source" as "File System" then browse your package. Step4: Go to "Set values" tab here set "property path" as "\Package.Variables["variablename"].Value" and "Value" as "10-03-2013"
    Thursday, October 03, 2013 6:00 PM
  • Hi,

    I believe it is not a problem to you to pass the value to package variable, it is maybe a bigger challenge to get system date time in command prompt and pass it to the dtexec parameters (SET switch).

    I have created a bat file for myself that gets data and time in correct format and passes it to the dtexec SET switch to pass it to package variable. The package variable is named VarDataTime, type datetime.

    @ECHO OFF
    set VarDateTime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2% %TIME:~0,8%
    
    dtexec /FILE "\"D:\SSIS\Package1.dtsx\"" /SET "\"\Package.Variables[VarDateTime].Value\"";"\"%VarDateTime%\""
    
    @ECHO ON

    Just change the path to the package, add additional parameters to dtexec if needed and then run bat file from command prompt typed job step.

    If you are using SQL Server 2012 and project deployment to SSIS catalog then you have an easier job. You can then use stored procedure catalog.set_execution_parameter_value. This is project parameter which you have to map to your package variable. After setting parameter values execute the package with catalog.start_execution.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    Thursday, October 03, 2013 6:47 PM