December 17, 2014 at 6:06 am
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
I am using the SQL server 2012 and Project Deployment model
So why are you using Windows Environment Variables then?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2014 at 9:15 pm
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
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2014 at 1:50 am
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
Sounds like you are doing it right.
Are you using connection strings rather than Project Connection Managers for a reason?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2014 at 2:06 am
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
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2014 at 2:27 am
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
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:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 22, 2015 at 3:39 am
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
,,,,,,,,,,,,
January 22, 2015 at 3:54 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 22, 2015 at 4:06 am
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
Deploying projects does not affect environments.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply