Sql-server – Upgrading SSIS Packages to 2017

sql serverssis

The purpose of this question is to document my answer to what seemed to be a strange issue when changing the target SQL Server version of an SSIS package created in some older versions of SSIS prior to 2012 that use script tasks.

An SSIS package originally created in BIDS 2005 or 2008 had no issues when upgraded through the various versions of BIDS/SSDT over the years. Even now when the package was upgraded to SSDT and targeting SQL Server 2016 everything still worked as it always has in addition to being able to take advantage of the latest SSIS features (package parts, project parameters, etc.) and deploying the package through SSDT/Visual Studio to the same target SQL Server. SQL Servers with SSIS installed that were upgraded to 2016 or 2017 seemed to have no issues with packages that were in MSDB or the package store since the jobs continued to run as expected post-upgrade.

So as part of normal upgrade routine, decided to open an SSIS package and upgrade it like before with the previous SSDT changes. In previous SSDT changes, one release worked for a particular version of SSIS. Once they introduced the ability to target some previous versions of SSIS, that feature seemed to work as expected. I figured things would be the same with the latest SSIS and SSDT versions.

However, once changing the target version to SQL Server 2017 within SSDT, there are some things that showed up that SSDT did not catch/fix or could not handle for some reason. Some of the issues I ran into were:

Cannot change the target .Net Framework version as it always reverts
back to original settings in the script task properties
Errors would show up such as "'Dts' is not a member of 'Microsoft.SQLServer'", "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase' is not defined", "Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task.", and "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute' is not defined."
Building the script task would fail and one of the errors would be "The primary reference "Microsoft.SqlServer.ScriptTask, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91" could not be resolved because it was built against the ".NETFramework,Version=v4.5" framework. This is a higher version than the currently targeted framework ".NETFramework,Version=v4.0" and the other error I would see was "Namespace or type specified in the Imports 'Microsoft.SqlServer.Dts.Runtime' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases."

The good thing is changing the target back to SQL Server 2016 seemed to work and I could continue editing the packages as before without errors and build would work as expected but the bad news is deploying the 2016 package to an 2017 SSIS server did not get converted like it did during the SQL Server upgrade. So how would I be able to update the packages to target SQL Server 2017 and not have these issues that prevent that?

Best Answer

The main source of the problem is the tags changed in SSIS schema that 2016 and prior handled without issue. I had to view the code of the SSIS package to check for differences and SSIS 2017 seemed to be a bit more strict when it came to the schema within the PropertyGroup node of the Project tag for the script tasks.

Here is what one tag looked like that had errors:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
</PropertyGroup>

The fix that seemed to solve half of my problems were to add the missing entries so that the above now looked like the below code new entries (which are after </ProjectGuid> and before </PropertyGroup>) are:

ProductVersion
SchemaVersion
AppDesignerFolder
TargetFrameworkVersion
FileAlignment
ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch
TargetFrameworkProfile

Here is what it looked like after adding the changes:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
  <ProductVersion>8.0.30703</ProductVersion>
  <SchemaVersion>2.0</SchemaVersion>
  <AppDesignerFolder>My Project</AppDesignerFolder>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
  <FileAlignment>512</FileAlignment>
  <ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>true</ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Doing this did not fix the issue with inability to change .Net Framework target so there had to be something else so I looked through the code and found the problem. In the code I found the following after </ProjectExtensions>:

<PropertyGroup>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Since these were now located in the main PropertyGroup of the script task, they are considered redundant so they are no longer needed in the old location. Deleting these entries fixed the other half of the problem. After doing this, not only was I able to change the SQL Server target to 2017, I was also able to change the .Net Framework version and it would not revert. Also, the builds were able to build successfully again.

Fixing the problem this way was easier than to try opening every package and manually creating a new script task, copying the logic from the old one to the new one, making sure the variables and references were the same, and deleting the old task, among other things. That process would take too long especially if there are a lot of packages to go through.

Hopefully you find this useful if you run into this issue.

An approach for package deployment upgrade

dtutil.exe is the command line tool for deploying SSIS packages. Assuming standard install path, you'd be looking for the dtutil that is in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe to extract the packages as is. When a newer version of SSIS tooling touches a previous version package, the first thing that happens is an in memory upgrade to that version of SSIS. I mention this as if you didn't use source control, you might want to extract the packages "as is" out of the existing 2008 R2 instance before deploying to your 2012 instance. I have a script for that

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath LIKE '\SERV%';

If you like to live dangerously, you could change the final SELECT to skip the file system and directly deploy to the other server. This assumes you've already created the same folder structure over there.

'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /destserver MyShiny2012Box /copy SQL;"'+ F.FolderPath + '\' + P.PackageName + '"' AS cmd

I've eyeballed the above and it looks approximately correct but you should test it. It should render a command similar to the following

dtutil /sourceserver localhost /SQL "\SERV-ABC\Package" /destserv MyShiny2012Box /copy SQL;"\SERV-ABC\Package"

For that to work, you'd need to make sure dtutil is the 2012 version which should be in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe

From the comments

Don't confuse the tooling with the installed products and/or servers. Starting with 2005, there is a version of SSMS that was released with each SQL Server version (this gets murky as of SQL Server 2016). SSMS has an "object explorer". The Database Engine portion of Object explorer can be used to manage all versions of SQL Server and the common recommendation is to always use the latest version of SSMS in your environment as it'll have the most bug fixes etc.

Where this advice can fail you if you use the "other" options for Object Explorer. SSIS is the only thing I use with regularity so I'll address that.

You must use the version of SSMS that matches your target SQL Server instance for it to "work." In the screenshot, you're attached to the 2008 R2 server's Integration Services ... whatever that is but ignore it. Yes, ignore that blecherous tool as it's useless. It doesn't help you if you have multiple instances running and it only runs packages in ... 32 or 64 bit, I don't remember which.

Instead, use the one Object Explorer type that works across your instances - the database engine one. SSIS packages stored in "the database" are going to be in the msdb. Use the above query or go exploring those tables yourself. All the data is there. There are tsql procedures to move packages about, store them out, etc although I prefer dtutil myself.

Best Answer

Related Solutions

Sql-server – Namespacing database names on the server in order to test multiple different SSIS packages

Sql-server – Upgrading SSIS packages

An approach for package deployment upgrade

From the comments

Related Question