I need to redevelop an ETL process and I'm evaluating options.
I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that SSIS 2012 is significantly improved and will help me develop the packages far more efficiently, but I don't know about the versioning considerations.
Can I run an SSIS 2012 package on a SQL Server 2008 R2 server? Or can I run it on a SQL Server 2012 database and connect to SQL Server 2008 R2 databases? Are there any issues that I'm going to come across connecting in this way?
Best Answer
You would need to save the package to a SQL 2012 instance and run it from there. It will connect to R2 just fine.
This is because a SQL Server 2012 SSIS package cannot be saved to a lower version of SQL Server or run from a lower version of SQL Server. That said, an SSIS package (in any version of SQL Server) can connect to any data source that is ODBC or OLE DB compliant. So an SSIS Pacakge created in SQL Server 2012 can connect to an Oracle Database as a connection, it can connect to a text file as a connection or it can connect to SQL Server 2008 R2 as a connection.
Also the benefit of keeping your development in SSIS 2012 is that you don't have to worry about upgrading that package when you finally move all of your infrastructure to SQL Server 2012. You can also take advantage of the new features in SSIS 2012, as you rightly pointed out.