We are considering installing SQL 2014 to take advantage of columnstore updateable indexes for an upcoming ETL process but am wondering if an SQL 2014 database can be accessed with SSIS 2012. We are planning on a centralized SSIS server, but the SSIS group has already begun developing with SSIS 2012 and they are hesitant to switch over at this time to SSIS 2014 as they are afraid it will negatively impact their development timeline. Any thoughts?

Our initial thoughts for considering SQL 2014 for the database was to take advantage of updateable columnstore indexes to enhance the performance of our ETL process. Are you saying that when SSIS 2012 is used to update a SQL 2014 database, you don't believe that we would take advantage of this 2014 feature?

jhamilton 47458 (7/29/2014)Our initial thoughts for considering SQL 2014 for the database was to take advantage of updateable columnstore indexes to enhance the performance of our ETL process. Are you saying that when SSIS 2012 is used to update a SQL 2014 database, you don't believe that we would take advantage of this 2014 feature?

Yes you can. SSIS will merely send some SQL statements to SQL Server which the database engine itself will execute.SSIS does not care what SQL Server does behind the scenes.

As long as we're talking about straight T-SQL commands, you should be fine. If you start trying to access objects directly through SSIS, you may hit issues. Forward compatibility is not guaranteed like backward compatibility.

Grant Fritchey (7/29/2014)As long as we're talking about straight T-SQL commands, you should be fine. If you start trying to access objects directly through SSIS, you may hit issues. Forward compatibility is not guaranteed like backward compatibility.