Helpful information and examples on how to use SQL Server Integration Services.
Today’s post is from Carla Sabotta, lead writer for the SQL Server Integration Services team. It covers the new Integration Services Migration Wizard, which has been renamed to the “Project Conversion Wizard” in recent builds. Enjoy!
In SQL Server “Denali”, you typically deploy your SSIS projects to the new Integration Services server. The server has improved features for configuring runtime values for packages, managing package execution, and troubleshooting package execution and performance.
To deploy a project that you created in SQL Server 2005, 2008, or 2008 R2 to the server, you first convert the project to the new project deployment model. If any of the packages in the project were created in these earlier versions of SQL Server, you’ll also need to upgrade the packages to the package format that is used by SQL Server “Denali” SSIS.
Keep in mind that SQL Server “Denali” doesn’t support migrating DTS packages.
You use the Integration Services Project Conversion Wizard to convert a project to the project deployment model. For more information about the Wizard options, see Integration Services Project Conversion Wizard in Books Online.
Depending on whether you convert the project in BIDs or in SQL Server Management Studio, the wizard performs somewhat different tasks.
To upgrade package files when a project is converted, run the wizard in BIDs. To upgrade package files separately from a project conversion, run the SSIS Package Upgrade Wizard.
You run the wizard in BIDs by right-clicking the project in Solution Explorer and then clicking Convert to Project Deployment Model.
You run the wizard in SQL Server Management Studio by right-clicking the Projects node in Object Explorer and then clicking Import Packages. The Projects node appears in Object Explorer when you are connected to the SQL Server Database Engine and the Integration Services server contains the SSISDB catalog.
You can configure the Execute Package Task to make values available to the child package by mapping parent package variables or parameters, or project parameters, to child package parameters. The project must use the project deployment model and the child package must be contained in the same project that contains the parent package.
When you convert a project that contains an Execute Package Task to the project deployment model, you can use the wizard to configure the Execute Package Task to access a child package that is contained in the same project as the parent package. You must also select the child package to convert to the format that is used by SQL Server “Denali” SSIS, in the Select Packages page of the wizard.
For more information about this Task, see Execute Package Task in Books Online.
When you convert a project to the project deployment model, you can convert the package configurations to parameters. You can then use these parameters to set runtime values for package executions.
You select which package configurations you want to replace with parameters by clicking the checkbox next to a package in the Select Configurations page of the wizard. You can add package configurations contained in other projects to the list of available configurations by clicking the Add Configurations button.
It’s recommended that you remove all configurations from the project, by selecting the Remove configurations from all packages after conversion checkbox. If you don’t select this option, only the configurations that you selected to replace with parameters are removed.
You select the parameter name and scope for the configuration properties in the Create Parameters page of the wizard. If the scope of a parameter is the project, the parameter value is passed to all packages in the project.
You set the parameter values and specify whether the values are required at runtime, in the Configure Parameters page.
When the wizard completes, you’re ready to deploy the project to the SSISDB catalog on the Integration Services server.
If at some point you want to modify the project in BIDs, or use it as a template to create a new project, you use the Integration Services Import Project Wizard to load the project in BIDs. For more information about how to access the Import Project Wizard, see Import an Integration Services Project in Books Online.
We have several hundred SSIS packages running in 2005. A significant number of them use both script tasks and script componenets. None of the scripts appear to get migrated, sometimes errors are thrown (mostly on a script component) and sometimes no error but when i attempt to look at the script in the script task, all i get is default script for c# and i lose my original vba script!. Is there a separate 'script conversion' tool? Is there an api i can use to write my own script converter?
That doesn't sound like the expected behavior - even if there are script compilation errors, you should be able to view the script in the VSTA designer and correct the issue.
Please open an issue for this on Microsoft Connect (https://connect.microsoft.com), and include some of the packages you are having problems with.
I just found out that upgrading 2005 scripts to Denali is unsupported in CTP3. There should be an error message about this in the execution / upgrade logs. This has been resolved for the next release.