There are many changes and enhancements to be found with SSIS 2012. I covered as many of the changes as possible in my session today. If you missed it, you can view the recording here.
There were several questions during the session which I will attempt to answer here:
Q: How would you run the package locally?
A: I’m assuming this means a non-deployed package, for example, on a workstation. Of course, for any of these options, SSIS must be installed on the workstation.
• Run the package from within SSDT
•You can double-click the package to bring up the Execute Package Utility from dtexecui.exe
- You can also run it from the command line with the dtexec.exe utility. Check this article on how to map values to the parameters.
Q: Do the new CDC tools replace the old SCD widget?
A: The new CDC task, source, and transform work with Change Data Capture, an Enterprise Edition feature. These new CDC objects make it easier to work with CDC as far as connecting to the CDC special tables and directing the appropriate changes within the data flow. They do not replace the Slowly Changing Dimension wizard or the handling of the changes.
Q: Can you give an example for CDC please?
A: There wasn’t enough time in the session to drill down into CDC besides just pointing out the new objects. My colleague Steve Wake has a presentation on CDC 2012.
Q: Can the deployment catalog be scoped to per project or per package specific variable rather than environment specific values?
A: You can override both project and package parameters with environment variables or hard-coded values.
Q: What is the difference between variables and parameters?
A: The difference is all about scope. Variables are configured inside the package. Parameters are configured outside the package. Inside the package, you can change the value of a variable. Inside the package, parameters are read-only. You can map parameters to variables within the package. At deployment, you can assign values to parameters or map them to environment variables. The purpose of parameters is to aid configuration at deployment.
Q: If you deploy a project of packages then delete a package from the project in the dev solution, then redeploy, is the deleted package removed from the IS catalog?
A: Yes, if you redeploy the project, then the package will be gone from the catalog.
Q: In the Flat File Connection manager - will it continue to "break" or fail when executed in a SQL Server agent job when a source system changes, randomly adds a column?
A: To figure this out, I added an extra column to the test file with missing values that I demonstrated in the session. The package did not fail when running in SSDT or in a job. It did not add the extra column, but it did import the extra value.
I made the change in yellow to the file
This is how the data viewer looked
Q: How easy to migrate SSIS packages to 2012?
A: Wow, there was so much for me to cover today that I didn’t even bring this up! If you open an earlier version SSIS package in SSDT, a wizard launches which upgrades the project to 2012, however, the project will still be in the old package deployment model. I haven’t been involved with an upgrade to 2012 project at this point, so I am not sure what other issues you may run into. I know that you will no longer be able to run any DTS packages. Of course you will have to test well!
Q: Can we install SQL server 2012 on a machine which already has SQL Server 2008 or 2008r2. Can they co-exist?
A: You can install database engine instances in different versions on the same machine, but NOT SSIS.
Q: Are there Attunity connectors for SQL Server 2012 for Oracle and Teradata?
A: Yes, Enterprise Edition only.
Q: I noticed the 32 bit runtime is still a setting. Does this mean we still have to always remember to use this setting when using an Excel connection manager? Microsoft hasn't created a 64 bit connection manager for Excel yet for SSIS?
A: I have been avoiding working with Excel in SSIS like the plague! Take a look at this resource: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Q: If you create a copy of the package in the project, do you manually have to set the GUID?
A: No, it automatically creates a new GUID for you!