September 11, 2015 at 4:18 pm
We have SQL Server 2014 EE SP1 and are using Project Deployment via SSMS. Just curious, I read that if a project is deployed to the same location with the same name, it will overwrite the original. And based on the catalog properties it might create an additional version of the project and keep a copy of the orignial.
My concern is what happens to projects that have environments and variables that are linked to the ssis project's parameters and the ssis project parameters have changed since the previous deployment? With subsequent redeployments of a project to the same location, are the environments and variables retained or updated? Does the versioning keeps track of changes to the environments, variables and ssis project's parameters.
Thanks for your help.
September 12, 2015 at 12:12 am
HookSqlDba7 (9/11/2015)
We have SQL Server 2014 EE SP1 and are using Project Deployment via SSMS. Just curious, I read that if a project is deployed to the same location with the same name, it will overwrite the original. And based on the catalog properties it might create an additional version of the project and keep a copy of the orignial.My concern is what happens to projects that have environments and variables that are linked to the ssis project's parameters and the ssis project parameters have changed since the previous deployment? With subsequent redeployments of a project to the same location, are the environments and variables retained or updated? Does the versioning keeps track of changes to the environments, variables and ssis project's parameters.
Thanks for your help.
Good question.
SSIS Environments are unaffected by project deployments. The linkage between the project and package parameters and any environment variables remains in place after deployment.
If there are any new or amended parameters in your project which require changes to one or more SSIS environments, these changes must be handled separately. This can be either through the GUI, or through 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
September 27, 2015 at 3:48 pm
Thanks Phil. Just want to make sure I understand this redeployment of projects using Project Deployment. This is new to me. I have also added two Project Versioning question.
For example, lets say the developer uses Visual Studio and creates a SSIS Project named ProjectA with 2 project parameters. The DBA uses SSMS (Integration Services Catalog) to deploy ProjectA. The DBA also uses SSMS (Integration Services Catalog) to create and link the environments and variables to the appropriate parameters within ProjectA. Then the developer (using Visual Studio) makes a change to ProjectA and adds a 3rd project parameter. The DBA uses SSMS and redeploys ProjectA (with the same name to the same location within Integration Services Catalog).
1) At this point, should the DBA create an environment (and variable) or modify an existing environment (and variable) to accommodate or link to the new project parameter.
2) Is the above example a correct assessment of how Project Deployment should be done by DBAs?
Lastly, regarding versioning. I read where SSIS will handle the task of retaining the previous version and the versioning information will appear in the Versions dialog within SSMS. And this will allow the DBA to rollback to the previous version if needed. Now, in my example, lets say the developer needs the DBA to revert back to the previous version of ProjectA.
3) Within SSMS, on the Project Versions Dialog, the DBA selects the previous version and click the "Restore to Selected Version" button. Would the previous version of ProjectA's project automatically be configured with the original 2 project parameters and would these 2 project parameters still be linked to the appropriate environments and variables within the Integration Services Catalog in SSMS? 4) Or, would there need to be some manual work completed by the DBA?
5) On the Project Versions Dialog, is the Active Version (the one checked) the version that will be executed by the associated SQL Server Agent Job?
Thanks in advance.
September 28, 2015 at 5:09 am
1) At this point, should the DBA create an environment (and variable) or modify an existing environment (and variable) to accommodate or link to the new project parameter.
When packages are executed, they can access/inherit only a single environment's values (even though the project itself may contain references to multiple environments). So the answer to your question is, probably, that an existing environment should be modified to include the new parameter and that parameter should be mapped to the new project parameter by Configuring the project.
2) Is the above example a correct assessment of how Project Deployment should be done by DBAs?
Speak to the DBAs and see what they think.
I have automated the deployment of our SSIS projects so that all that is required is the click of a button. TeamCity then takes the latest versions from our production branch, builds and deploys.
The creation of new environment variables and their association to a project remains, for us, a manual process at the moment – even though this can all be scripted.
3) Within SSMS, on the Project Versions Dialog, the DBA selects the previous version and click the "Restore to Selected Version" button. Would the previous version of ProjectA's project automatically be configured with the original 2 project parameters and would these 2 project parameters still be linked to the appropriate environments and variables within the Integration Services Catalog in SSMS? 4) Or, would there need to be some manual work completed by the DBA?
This is guesswork on my part, as I have not had to do a restore to previous version for a long time, but I suspect that the rollback process does not affect environments or their configuration. You should test this to be sure.
5) On the Project Versions Dialog, is the Active Version (the one checked) the version that will be executed by the associated SQL Server Agent Job?
What happened to question (4)? 🙂
Yes, if you are referring to the 'Current' flag.
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
September 28, 2015 at 7:55 am
Thanks Phil for being so thorough with your responses. Btw, question 4 was listed at the end of question 3.
September 28, 2015 at 8:45 am
HookSqlDba7 (9/28/2015)
Thanks Phil for being so thorough with your responses. Btw, question 4 was listed at the end of question 3.
Ah yes, I somehow missed the number but still managed to answer the question 🙂
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply