Post reply

Calling Windows Environment variable into the Master package in Project Deployment model

  • December 17, 2014 at 6:06 am

    #313175

    Hi All,

    I am somewhat got used to the SSIS packages, and at one location i got struck and i need the help of you guys...

    1. I have Created a Master package and i am calling all my other packages say child packages from this master package.

    2. We created three Project level Connections in the project and one is Staging and Warehouse and Oracle Source

    We have defined the Warehouse and Oracle Connection strings in SQL server Table of the Staging DB .

    3. I have configured the ServerName of my Staging Server say 'localhost' in the windows environment variable and this is configured only in the Master package

    Now my problem is that my Master package is not able to read the 'ServerName' i have provided in my Windows Environment variable !! I need this since i may change my server to some other server where the server name might be different and so i am configuring this through the Environment variable

    when trying to Edit it i am getting this Error message

    The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[STAGING_CONNECTION].Properties[ServerName]'. (Microsoft.DataTransformationServices.Design)

    I am using the SQL server 2012 and Project Deployment model

    Pls suggest me whats the wrong

  • December 17, 2014 at 7:32 am

    #1765183

    I am using the SQL server 2012 and Project Deployment model

    So why are you using Windows Environment Variables then?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 17, 2014 at 9:15 pm

    #1765322

    Hi , I should not use ?

    I have tried the other method as Deployed my project in the SSIS catalog and then created a Environment variable in the SSIS catalog and assigned to a project parameter and this is done through my local server and i am running the Master package by creating the job

    But my requirement is something like on whatever server i am deploying i will configure the windows environment variable and the Master package should consider the Environment variable Server Name in that server and i just i need to execute the Job and not doing anything manually and it should be automatic on any server that i deploy for ..

    So i hope i was able to convey what i require ...if this can be done easily by the way your suggesting ..pls let me know the process and how it can be accomplished easily on other servers too..

    Thanks & Regards,

    Guitar_Player

  • December 18, 2014 at 12:50 am

    #1765340

    Guitar_player (12/17/2014)

    Hi , I should not use ?

    I have tried the other method as Deployed my project in the SSIS catalog and then created a Environment variable in the SSIS catalog and assigned to a project parameter and this is done through my local server and i am running the Master package by creating the job

    But my requirement is something like on whatever server i am deploying i will configure the windows environment variable and the Master package should consider the Environment variable Server Name in that server and i just i need to execute the Job and not doing anything manually and it should be automatic on any server that i deploy for ..

    So i hope i was able to convey what i require ...if this can be done easily by the way your suggesting ..pls let me know the process and how it can be accomplished easily on other servers too..

    Thanks & Regards,

    Guitar_Player

    In 2012, I see no need to use Windows EVs (WEVs) any more.

    You mention that you want to be able to deploy the project, configure one WEV and then be able to run it, without 'doing anything manually'.

    But in your original post, you also state that:

    We have defined the Warehouse and Oracle Connection strings in SQL server Table of the Staging DB .

    So there's clearly more to this task than you suggest, because the staging DB needs to be updated too.

    Instead, you have the option of

    1) Default all of your parameters and connections to dev values, so that you can always open them in SSDT.

    2) After deploying to a non-dev server, create a SQL Agent job to run the package.

    a) In your agent job, you can override any of the parameters and connections directly.

    b) Or your can create an SSIS Environment (in the SSIS catalog) to contain all of the connections and parameters and associate that with the project.

    Unlike a WEV, this makes the process more transparent.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2014 at 1:50 am

    #1765354

    Yes , I agree. So how i have tried the solution currently is that I have defined the

    1. Oracle source connection string as a Parameter_1

    2. Stg Source only ServerName property as Parameter_2

    3. Dest Warehouse only ServerName property as Parameter_3

    in my Project solution

    now i have defined the same connections in the Environment Folder in the SSIS Catalog below the projects and in that i have created DEV which have 3 variables referring to oracle ,stg,warehouse

    So now i have defined the job for calling the master package and in the configurations i have given the DEV environment

    and i ran the job and i believe its working fine.. Is this the Intended behavior of your saying ?

  • December 18, 2014 at 2:03 am

    #1765357

    Sounds like you are doing it right.

    Are you using connection strings rather than Project Connection Managers for a reason?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2014 at 2:06 am

    #1765358

    Are Connection Strings rather different than Connection Managers ? I dont know that !!!

    I am using the Project Connection Managers and i have parameterize them (Made them as Parameters) and mapping the DEV Environment variables which are in SSISDB catalog to these parameters which are inside the project

  • December 18, 2014 at 2:11 am

    #1765361

    Guitar_player (12/18/2014)

    Are Connection Strings rather different than Connection Managers ? I dont know that !!!

    I am using the Project Connection Managers and i have parameterize them (Made them as Parameters) and mapping the DEV Environment variables which are in SSISDB catalog to these parameters which are inside the project

    Good.

    Yes, to me a connection string is just something like

    "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=[ServerName]"

    which could be held in a string parameter.

    Which is potentially useful if you are going to use it in script.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2014 at 2:27 am

    #1765363

    Yes I agree !! For Oracle I am getting the Full Connection String and for the SQL DBs i am getting only ServerName property of those connection Managers ..:-)

    Thanks bro.. I will let you know if i am having any issues or need ant clarifications !!!

  • December 18, 2014 at 2:38 am

    #1765365

    Guitar_player (12/18/2014)

    Yes I agree !! For Oracle I am getting the Full Connection String and for the SQL DBs i am getting only ServerName property of those connection Managers ..:-)

    Thanks bro.. I will let you know if i am having any issues or need ant clarifications !!!

    Sure thing! From a fellow guitar player :smooooth:

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 22, 2015 at 3:39 am

    #1772118

    Hi Phil,

    I need info on the Deployment side..

    I have built my package and and deployed the package to the SSISDB Catalog..

    My project is something like... Master package which contains different child packages ..

    Now my questions is that , if at all i want to add some tasks in packages or do some modifications ..then how can i deploy them ?

    Here i am rebuilding the project and taking the '.ispac' and deploying to my server and so after deployment if we are doing the changes in the packages ,then how are we going to update those changes to the SSISDB catalog ?

  • January 22, 2015 at 3:42 am

    #1772119

    ,,,,,,,,,,,,

  • January 22, 2015 at 3:54 am

    #1772122

    Guitar_player (1/22/2015)

    Hi Phil,

    I need info on the Deployment side..

    I have built my package and and deployed the package to the SSISDB Catalog..

    My project is something like... Master package which contains different child packages ..

    Now my questions is that , if at all i want to add some tasks in packages or do some modifications ..then how can i deploy them ?

    Here i am rebuilding the project and taking the '.ispac' and deploying to my server and so after deployment if we are doing the changes in the packages ,then how are we going to update those changes to the SSISDB catalog ?

    Simple: build the project and deploy again.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 22, 2015 at 4:06 am

    #1772123

    I think it cannot be that much simple...since

    In the SSIS DB Catalog, i have configured the Environment variable which contains three parameters to the Project which got deployed .

    This projects Master package is called from a Job where i have defined the Environment variable which is getting linked to it..

    Now if i redeploy ..then again i need to re-configure the Environment variable and again i need to modify the Job environment variable since it will create a new environment variable id

  • January 22, 2015 at 4:21 am

    #1772129

    Deploying projects does not affect environments.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 15 posts - 1 through 15 (of 35 total)

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