Post reply

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

  • September 26, 2020 at 10:24 am

    #3791074

    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?

     

     

  • September 26, 2020 at 11:45 am

    #3791079

    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.

  • September 27, 2020 at 3:12 am

    #3791135

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 27, 2020 at 6:59 pm

    #3791191

    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.

  • September 27, 2020 at 7:54 pm

    #3791193

    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.

  • September 27, 2020 at 8:00 pm

    #3791194

    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) ??

     

  • September 28, 2020 at 7:49 am

    #3791231

    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.
    Larnu.uk

  • September 28, 2020 at 8:09 am

    #3791233

    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..

  • September 28, 2020 at 8:25 am

    #3791234

    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.
    Larnu.uk

  • September 28, 2020 at 10:06 am

    #3791254

    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 9 (of 9 total)

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