July 28, 2014 at 3:00 pm
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?
July 28, 2014 at 3:03 pm
Yes, you can connect from SSIS 2012 to SQL 2014.
There are no notable difference between SSIS 2012 and 2014, aside from the development environment (SSIS 2014 uses VS 2013), so the SSIS group shouldn't expect that much of a negative impact.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 28, 2014 at 3:07 pm
Thank you for your prompt response. This definitely helps in our decision making.
BTW - I was just reading one of your articles about SSIS deployments and greatly appreciate your knowledge share as SSIS is something that I am just learning about now.
Jacki
July 29, 2014 at 6:14 am
While you can connect to it, you won't be able to access 2014 objects and may run into issues with objects that contain 2014 only constructs (clustered columnstore).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2014 at 6:24 am
Grant Fritchey (7/29/2014)
While you can connect to it, you won't be able to access 2014 objects and may run into issues with objects that contain 2014 only constructs (clustered columnstore).
I don't really see an issue when using SSIS 2012 to read/write from SQL Server 2014.
SSIS either issues just a select statement or it just sends data. How SQL 2014 handles it is of no concern for SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 29, 2014 at 7:48 am
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?
July 29, 2014 at 7:55 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 29, 2014 at 8:00 am
Thank you Koen for the clarification.
Jacki
July 29, 2014 at 8:17 am
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2014 at 11:33 am
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.
How are you going to access objects directly? Through SMO?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 29, 2014 at 11:52 am
The processes being created are data loads. I do not believe that there is any object manipulation, i.e. table creates, index creates, etc. I am not very familiar with SSIS but I do not think that we are talking about any SMO type tasks.
July 30, 2014 at 5:01 am
Koen Verbeeck (7/29/2014)
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.How are you going to access objects directly? Through SMO?
No, I'm thinking about the SSIS objects that directly manipulate tables and may not expect certain data types. That sort of thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2014 at 5:06 am
Grant Fritchey (7/30/2014)
Koen Verbeeck (7/29/2014)
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.How are you going to access objects directly? Through SMO?
No, I'm thinking about the SSIS objects that directly manipulate tables and may not expect certain data types. That sort of thing.
Your words are shrouded with mystere and riddles 😀
About which objects are we talking here? As far as I know there are no new data types in SQL 2014.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 8:38 am
Koen Verbeeck (7/30/2014)
Grant Fritchey (7/30/2014)
Koen Verbeeck (7/29/2014)
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.How are you going to access objects directly? Through SMO?
No, I'm thinking about the SSIS objects that directly manipulate tables and may not expect certain data types. That sort of thing.
Your words are shrouded with mystere and riddles 😀
About which objects are we talking here? As far as I know there are no new data types in SQL 2014.
You're buying the next beer.
Not sure what I was thinking of, instead of data types, let's just say objects.
Does it work with in-memory tables if you link to them from 2012? I don't know, but I think it's worth asking.
Again, I never assume forward compatibility and I'm surprised that anyone would just blithely say it's fine. Not without extensive testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2014 at 11:50 am
Grant Fritchey (7/30/2014)
Koen Verbeeck (7/30/2014)
Grant Fritchey (7/30/2014)
Koen Verbeeck (7/29/2014)
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.How are you going to access objects directly? Through SMO?
No, I'm thinking about the SSIS objects that directly manipulate tables and may not expect certain data types. That sort of thing.
Your words are shrouded with mystere and riddles 😀
About which objects are we talking here? As far as I know there are no new data types in SQL 2014.
You're buying the next beer.
Not sure what I was thinking of, instead of data types, let's just say objects.
Does it work with in-memory tables if you link to them from 2012? I don't know, but I think it's worth asking.
Again, I never assume forward compatibility and I'm surprised that anyone would just blithely say it's fine. Not without extensive testing.
What? I bought the previous one! 🙂
As usual you are (partially) right. I tested it and you can read/write without issues to a clustered columnstore index, but the memory optimized table has one issue: if you want to write against it you have to disable the tablock option in the fast load options of the OLE DB destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply