Using SSIS packages build in VS2010 when migrating SQL server from 2008 to 2016

  • blom0344

    SSCrazy

    Points: 2039

    With eof of Windows2008R2, lots of our customers are planning new server setups, usually employing SQL server 2016 instances on new local servers. We have complex SSIS solutions combined with SSAS/SSRS originally developed with VS2010 + SQL server 2008.

    A couple of customers we migrated upgrading the solution to VS2015.

    This caused massive problems mainly with scripts tasks (like missing DLL registrations).

    Is there any chance we could work with SQL server 2016 AND not upgrade the SSIS packages?

     

     

  • frederico_fonseca

    SSChampion

    Points: 14777

    nope - even if you don't upgrade yourself, the engine will do that on the fly - and will fail for same reason.

    you need to figure out a way to prevent those missing dll regs - potentially by having a manual (or automated in c# or other tool) that goes through the upgraded packages and adds the missing references after the upgrade.

    note that sometimes between SSIS versions objects you need to have both old and new versions of the DLL's for the upgrade to work if the dll version is affected by the SSIS package version.

  • Jeff Moden

    SSC Guru

    Points: 997320

    As a bit of a sidebar, this is why I jump through flaming hoops to ensure that we never use SSIS.  I've not had a problem avoiding it because most of what it does can be done in T-SQL.  IMHO, it can usually be done a whole lot easier in T-SQL, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • blom0344

    SSCrazy

    Points: 2039

    Well sidebar or not, we live in a world crowded with legacy solutions inherited. My team inherited everthing developed based on SSIS from way back when. As much as I loathed the tool in the beginning, it is absolutely usefull especially with sequence containers and those lovely lookups.

  • sterling3721

    SSC-Addicted

    Points: 472

    I use SSIS heavily and I upgraded numerous SSIS packages from SQL 2008/2008R2 to SQL 2016, including ETL from multiple SQL servers to SQL server; exporting data from SQL server to Oracle; importing data from Oracle to SQL server; importing data from Excel/csv/flat file to SQL server; sending data from SQL server to ftp, etc.

    It's pretty smooth so far since the packages were tested on a testing server before upgrading,

    the missing pieces were found and resolved during testing; otherwise, it's a no-go for migration.

  • blom0344

    SSCrazy

    Points: 2039

    I took the following approach:

    1. Downloaded and installed VS2015  Community Edition.
    2. Opened copy of solution (with all components in folder solution)
    3. Message about not being able this type of project.
    4. Donloaded ISO  of  VS2015 Data Tools, mounted it and installed Data Tools.
    5. Create new project offers the creation of SSIS/SSAS/SSRS type of projects.
    6. Opened copy of solution (with all components in folder solution) that was made in VS2010
    7. The solution opens, but the normal migration wizard does not start. I can save the solution, but the SLN file still shows version 2010.  Look and feel is still the sharp edged GUI of  VS2010  instead of the rounded version of  2012/2013/2015

    My 2 cents : No upgrade to newer version seems to occur, possibly due to using the  CE instead of the  isolated/integrated version (professional) ??

     

  • Thom A

    SSC Guru

    Points: 98776

    Even if you were to us 2008 packages in SQL Server 2016, that wouldn't solve the missing DDLs; they'd still be missing. If you're missing DDLs for the packages to run on the new server(s) you need to download and instance the right software that contains those DLLs.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • blom0344

    SSCrazy

    Points: 2039

    You are absolutely right. However, the missing DLL's are part of the MSFT 2008R2 operating system, which means that our predecessor never evaluated the chance future operating systems might work differently. We have a workaround for that problem , though. The challenge seems to be to get the proper installation of tooling to perform the update in the first place..

  • Thom A

    SSC Guru

    Points: 98776

    blom0344 wrote:

    You are absolutely right. However, the missing DLL's are part of the MSFT 2008R2 operating system, which means that our predecessor never evaluated the chance future operating systems might work differently. We have a workaround for that problem , though. The challenge seems to be to get the proper installation of tooling to perform the update in the first place..

    So the problem, in truth, has nothing to do with SQL Server nor SSIS, and specifically that the package(s) are using features that the operating system (Windows in this case) no longer provides or works differently. That should be and is an expectation that you should have when updating the underlying operating system.

    This feels like what you are effectively asking isn't "how do I not upgrade the packagesfrom migrating from SQL Server 2008 to SQL Server 2016" but "How do I upgrade the packages, but get them to use deprecated/removed features of Windows 2008 on Windows 2016?" For that, you need to update your packages to use the new standards, much like you would with any upgrade path.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • blom0344

    SSCrazy

    Points: 2039

    Well, in this case customers are upgrading and expect old solutions to migrate seamlessly. We are trying to make the best of it  🙂

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply