I would like thank everyone who attended my webinar on SSIS 2012 Deployment Models. Since I can never expand the size of the question window in the webinar, I miss a lot of questions. For all those questions I missed, I am posting the questions and the answers here.
Q: PROJECT DEPLOYMENT MODEL: CONS! I can deploy the project. But I cannot download it from SSISDB. It becomes necessary for me to have the source code. Let me know if you have some work around?
A: Correct. I have not found a way to export packages from the SSISDB.
Q: I have SSIS 2008 R2 packages using file system deployment. Will they need any changes to be used in SSIS 2012?
A: If you open the packages in SQL Server Data Tools, it will run the packages through a quick upgrade wizard that will upgrade them to 2012. Just like moving from 2005 to 2008. Keep in mind, it will add the packages to a project and they will be in project deployment mode.
Q: Shouldn't both environments use the same variable, so when you execute the package you just select the environment? (because the project for that connection already have a variable assigned)
A: Not Necessarily. Each environment uses it's own collection of variables. When mapping a package execution to an environment, you have to map each environment variable to the package property that it will replace.
Q: In addition to the "Integration Services Catalogs" folder in SQL Mgmt Studio, you also have a DB under "Databases" for it... right?
A: Yes. However, this database holds information about the package executions. This is the data that is displayed in the execution reports for that server.
Q: If I have VS2010 already installed, would I need to install SSIS add ins only or exclusively install Data Tools to be able to edit,view packages?
A: You must have SQL Server Data Tools (formerly known as BIDS) in order to develop and work with packages in SSIS 2012. The packages are not backwards compatible.
Q: How can i add existing package to visual studio from ssis catalog?
A: So far, I have not found a way to do this.
Q: I was able to deploy all packages (using package model) in a project in one manifest. I believe you said that this is not possible.
A: This is one of those times I have to say "sort of". When you build a project that is in package deployment mode, Visual Studio creates an ispac file. Double click the file to launch the package deployment wizard, but it will convert the project to project deployment mode and deploy it to the SSIS catalog.
Q: how would you specify a production parameter value that is specific to just one package in the project? do you still define it in the environment?
A: Yes, those variables are still defined in the environment.
Q: I have heard that the Project deployment model requires colocation on a system with SQL Server 2012 (Dependent on the SSIS Catalog). Our environment has SSIS install on a separate server, is there any way around this?
A: Right now, there is no way around this. Because of the SSIS Catalog, they are dependent on each other.
Q: Can you deploy a single package in project deployment mode?
A: No. Unfortunately, this is no longer an option.
Q: At my work we deploy the package to each environment (Dev, QA, Staging, Prod) and packages are typically called by scheduled SQL jobs. The packages are set to use configurations that are stored in a SQL table on the server that the package is running on. With the project deployment model doing away with configurations how would you configure the jobs and packages to execute against the proper environment?
A: When adding steps to jobs, you will have the same options that you would see when manually executing a package that is stored in the SSIS Catalog. So, this means you will be able to create environments and point to them in the job step.
Q: In the project deployment model can you speak to the process for separation of function between the test DBA deploying a project to a test platform and then deploying the tested project to the production environment by the production DBA.
A: I imagine this could get tricky. If the packages will remain on the same box, then it would probably be beneficial to create new environments under each project. That way, permissions can be set on the production environment to restrict access.
Q: Project deployment looks good, however over a period we makes changes to few packages so how do you deploy that specific package.
A: That seems to be one of the down sides to project deployment. The entire project needs to be deployed.