locked
sql server agent job RRS feed

  • Question

  • i have following job script.

    i need to make file connection and db connection passing in job as parameter.

    where in following script is should pass it.

    if i am passing here

    but when i change path it still getting old path in package.what i needs to change in package.also how to make connection dynamic so in each env when we deploy it will automatically change.we have sql job script for each env

    Wednesday, May 6, 2015 3:07 PM

Answers

  • Hi coool_sweet,

    According to your description, you want to dynamically change connection manager connection string based in different environments.

    To achieve this goal, we can make use of SSIS Environment Variables. For more details, please see:
    SQL Server Data Tools (SSDT) - Integration Services Project
    Step 1:  Create Parameters (Project or Package level as appropriate) and associate expressions, source queries, etc to these Parameters as appropriate.
    Step 2:  Parameterize connection strings.
    Step 3:  Deploy Project to the SSIS Catalog once package executes as desired within SSDT.
    SQL Server Management Studio (SSMS) - Integration Services Catalogs>SSISDB
    Step 4:  Create Environment & set up Variables within the Environment. These should coincide with the Parameters from the SSIS project.
    Step 5:  Configure each Project (and Packages individually, if needed).
    Step 6:  Execute package in SSMS via the Catalog. Will need to manually specify which Environment to use at execution time when running in this on-demand manner.
    Step 7:  Create Agent Job to execute on an ongoing basis. Associate the job to the appropriate Environment so it automatically detects values which have been set.

    For more information about the scenario, please refer to the following blog:
    http://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Proposed as answer by Harry Bal Friday, May 8, 2015 2:43 PM
    • Marked as answer by Katherine Xiong Tuesday, May 19, 2015 2:41 AM
    Friday, May 8, 2015 7:41 AM

All replies

  • Revise the connection manager setting.


    Arthur

    MyBlog


    Twitter

    Wednesday, May 6, 2015 6:00 PM
  • Hi coool_sweet,

    According to your description, you want to dynamically change connection manager connection string based in different environments.

    To achieve this goal, we can make use of SSIS Environment Variables. For more details, please see:
    SQL Server Data Tools (SSDT) - Integration Services Project
    Step 1:  Create Parameters (Project or Package level as appropriate) and associate expressions, source queries, etc to these Parameters as appropriate.
    Step 2:  Parameterize connection strings.
    Step 3:  Deploy Project to the SSIS Catalog once package executes as desired within SSDT.
    SQL Server Management Studio (SSMS) - Integration Services Catalogs>SSISDB
    Step 4:  Create Environment & set up Variables within the Environment. These should coincide with the Parameters from the SSIS project.
    Step 5:  Configure each Project (and Packages individually, if needed).
    Step 6:  Execute package in SSMS via the Catalog. Will need to manually specify which Environment to use at execution time when running in this on-demand manner.
    Step 7:  Create Agent Job to execute on an ongoing basis. Associate the job to the appropriate Environment so it automatically detects values which have been set.

    For more information about the scenario, please refer to the following blog:
    http://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Proposed as answer by Harry Bal Friday, May 8, 2015 2:43 PM
    • Marked as answer by Katherine Xiong Tuesday, May 19, 2015 2:41 AM
    Friday, May 8, 2015 7:41 AM