Thank you to everyone who attended my School of Wrox presentation "Upgrading and Overhauling Your SSIS Packages for 2012" last month. There were many remaining questions at the end of the session that we weren't able to fit. The questions below are the unaddressed ones asked during the session that are specific to upgrading to 2012. Enjoy!
Will SSIS 2008 package run AS IS in 2012?
Yes, the SSIS 2012 service will temporarily convert the package in its 2005 or 2008 format to the 2012 format when the package runs. The package may not execute properly if it references providers or assemblies that are not available, so upgrading the package before deploying is highly recommended. For more information, see: http://msdn.microsoft.com/en-us/library/bb522577.aspx.
Can we integrate file based execution in catalog in DB and generate reports about the executions?
Storing packages for execution on the file system is only possible in the Package Deployment Model. To utilize automatic logging, you need to deploy your SSIS packages to the SSIS Catalog.
Is it mandatory to create a Project deployment even if there is only one package in the Project?
You can run one package using the Package Deployment Model, but you will not have access to the 2012 features, such as automatic logging and parameterization. To use the new 2012 features, you will need to use the Project Deployment Model, no matter how many packages the project includes.
If you need to change one package within the Project, do you redeploy the whole Project or you can select to redeploy the changed package only?
You will need to redeploy the whole project. For additional information on this, please see this blog post: http://www.mattmasson.com/index.php/2012/07/can-i-deploy-a-single-ssis-package-from-my-project-to-the-ssis-catalog/
Is there a manifest file created during the deployment (just like in 2005) which a DBA uses to deploy the packages?
Yes, in the Project Deployment Model, an .ispac file is created which can be used to deploy packages.
Can we have more info on the that stored proc that is used to call the SSIS jobs?
To execute an SSIS package, you can use the create_execution stored procedure. For more information, please see: http://msdn.microsoft.com/en-us/library/ff878034.aspx.
Is there a way to have a "global" set of parameters that would apply to all projects?
Environments are applied at a project level, but can be programmatically created. To mimic a global set of parameters, I would try a programmatic environment creation script that could be applied to each project, keeping in mind that it would be copied multiple times.
Can we still use dtexec to run the packages from the cmd prompt?
Yes, you can still use the dtexec executable. Note that behind the scenes, the executable is still calling the stored procedure referenced above. For more information, see: http://msdn.microsoft.com/en-us/library/hh231187.aspx#server
Will the upgrade tool have issues with a package that has third party data flow tasks?
To use custom components, you can update configuration files, as described here: http://blogs.msdn.com/b/mattm/archive/2011/08/09/making-your-existing-custom-ssis-extensions-and-applications-work-in-denali.aspx. In the long run, it is recommended to upgrade your components to 2012.
Comments