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

Question

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

blom0344

SSCrazy

Points: 2076
More actions
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?

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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: 1004340 More actions · Answer 2

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)

blom0344 SSCrazy Points: 2076 More actions · Answer 3

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 SSChasing Mays Points: 658 More actions · Answer 4

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: 2076 More actions · Answer 5

I took the following approach:

Downloaded and installed VS2015 Community Edition.
Opened copy of solution (with all components in folder solution)
Message about not being able this type of project.
Donloaded ISO of VS2015 Data Tools, mounted it and installed Data Tools.
Create new project offers the creation of SSIS/SSAS/SSRS type of projects.
Opened copy of solution (with all components in folder solution) that was made in VS2010
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: 99130 More actions · Answer 6

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

blom0344 SSCrazy Points: 2076 More actions · Answer 7

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: 99130 More actions · Answer 8

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

blom0344 SSCrazy Points: 2076 More actions · Answer 9

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