I'm sure having multiple versions of Visual Studio installed on my laptop could be messy. I need to be able to build SSIS packages and deploy to both SQL 2008 instances as well as a 2019 instance.
Is this possible with a single install of Visual Studio? Is VS 2019 compatible with SQL Server 2008?
Thanks
Take a look here:
That lists the IDE versions of VS and the add-ons needed for maintaining packages of different versions.
Also note that SQL Server 2008 and 2008 R2 are no longer supported, unless you're hosting instances in Azure SQL Managed Instance. SQL 2012 will also be out of extended support this year, and SQL 2016 and 2017 will end mainstream support this year.
Lordy... they really need to come out with 2022 before they do that. There's no way that I want to upgrade to 2019. It reminds me of all the problems they had with 2012 until they came out with SP3.
SSIS packages can connect to many different source and destination systems - there really is no reason to deploy to a 2008 instance of SSIS when you could just as easily deploy to the 2019 instance and configure the source/destinations to be those older 2008 instances.
I would consider it a waste of time to specifically build a package for 2008 - in the old package deployment model, just to come back to it later and upgrade it to a 2019 version. Which is not just a simple lift and shift - but requires a lot more time and effort to evaluate the changes (depending on deployment model - of course).
Have you looked at other tools other than ssis to do what you want to do?
Powershell
Python
Etc
To be honest, I've never had a need for SSIS and have help a lot of people that I worked with give up that bad habit. 
I also haven't had the need to slow things down with Python, PowerShell, etc, etc, ad infinitum. Bulk Insert, the ACE Drivers, and some other (should be) common knowledge works a treat.
A good friend of mine got seriously burned by having several hundred SSIS packages and they were migrating to a new version of SQL Server on a brand new bit of hardware. That's before MS made it a little easier to do such migrations. It took her about a week to get all those packages moved.
In a previous job, I had changed all SSIS packages to stored procedures using the methods I previously described. Everything ran a whole lot faster and, when it came to upgrade and move to new hardware, it was all a part of the initial restore from tape because it was all in the form of stored procedures. And, nothing broke in the process.
1 Like
I believe there is more to ssis than just bulk inserting data though. It has its place and like everything else it can definitely be misused. And it is a huge pain to work with often.
It would be educational to see evidence of slowness in python and powershell though
Have you ever been involved in a migration/upgrade as I've described? And do you use Python and/or Powershell to do your Bulk Inserts?
Also, what is the "there is more to SSIS than just bulk inserting" that you're talking about? I will agree that parallel runs are useful but you don't actually need to fire up an SSIS instance to do that.