Skip to main content

Upgrading and Overhauling SSIS Q&A

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Reporting Services 2008 Configuration Mistake

To start working with the management side of SQL Server Reporting Services 2008, I decided to set up a report server and report manager. Unfortunately, I made a mistake while setting up my configuration that left me a little perplexed. Here are the steps I took to cause, track down, and solve the issue. Problem: I began by opening the Reporting Services Configuration Manager from the Start Menu. I clicked through each of the menu options and accepted the defaults for any question with a warning symbol, since warning symbol typically designate an action item. After two minutes, all of the warning symbols had disappeared, and I was ready to begin managing my report server. Unfortunately, opening up a browser and trying to open up the report manager resulted in the dreaded " The report server has encountered a configuration error. (rsServerConfigurationError) " message. Sherlock-ing it: I put on my sleuthing hat and went to the log file directory: C:\Program Files\Microsoft...
47 comments
Read more

Execute SQL Task Designer Limit

After migrating a package from DTS to SSIS, I had a problem with an Execute SQL Task. I couldn't change any characters in the SQLStatement property; I couldn't add any new characters; I could delete characters, but not retype them! After googling several variations of "integration services" "read only" and "Execute SQL Task", I deleted about half of the entry in a fit of frustration. Lo and behold, I could type again. Apparently, there is limit on the size or number of characters that can be entered in the SQLStatement property. From my experimentation, I came up with a limit of 32767 characters. The interesting thing is that the restriction only seems to be on the designer. If you set the SourceType to "Variable" and use a variable that contains more than 32767 characters, the task will execute. Also, if you use the "Direct Input" SourceType and modify the package XML to set the SQLStatement longer than 32767 characters, ...
10 comments
Read more

Manipulating Excel Spreadsheets in SSIS

Tom, an attendee at last weekend’s SQLSaturday Olympia , asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package. First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies . This will need to be done on any machine where you plan on running this package. Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name): Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim excel...
52 comments
Read more