I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on an instance of SQL Server 2008.
The SSIS packages (and the databases they connect to) will be upgraded to SQL Server 2012 over several months.
If the packages remain on the SSIS 2008 (consumed by the SQL Server Agent Jobs on the SQL Server 2008 instance), can the 2008 instance Agent connect to the 2012 server?
Best Answer
Yes, most likely. This is a short answer, but it sounds like you are really just asking - Can a job on SQL Server 2008 calling an SSIS package built on 2008 connect to a database in SQL Server 2012 and do some work within that database?
SSIS can connect to many OLEDB/ODBC compliant connections. You could, for instance use an SSIS 2008 (or 2005 or 2012) Package to read data from one Oracle database, do some transformations and the write the results of that to another Oracle database, never talking to SQL except the SSIS engine.
So an SSIS package in 2008 should be able to work with a connection to 2012. Some caveats like - if you are using a deprecated command structure in an execute SQL task - it will fail.. Or test it and verify first. But this should work, if you are just asking if - from within the SSIS package can I talk to SQL Server 2012.