June 26, 2014 at 1:19 pm
Can a SSIS 2005 package connect to a Sql Server 2012 database? I'm not getting it to work. I even tried to change the connection string to use the Sql Server 2012 provider but I wasn't able to do that - - it didn't like the change and said it was an invalid provider.
Is my only option to convert the package to SSIS 2012 and run it from Sql Server 2012, maintaining the other connections to 2005 databases? I don't think I can convert the package and expect it to run from a 2005 server. Or can it?
June 26, 2014 at 3:09 pm
I may have answered my own question.
I took the package from the Sql Server 2005 server, copied it to the Sql Server 2012 server, ran it through the update wizard, changed the connection to point to the Sql Server 2012 database, then ran it from the SQL Server 2012 server. As expected, it ran fine.
I then copied the updated package back to the Sql Server 2005 server. I couldn't open the package in Visual Studio because I didn't have Visual Studio 2010 on that server, but I was able to kick off the package from the DTEXEC utility. And it seemingly ran fine.
But I want to be confident this will work all the time because I remember reading something about not being able to run an SSIS 2012 package on a Sql Server 2005 server.
Can any one point me to a definitive answer?
June 27, 2014 at 5:42 am
I am very surprised that this works and don't recommend it.
I have never tried, but I don't see why there would be any problem connecting to a 2012 instance from a 2005 SSIS package. Which providers did you try and what were the error messages?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 27, 2014 at 7:21 am
Hey Phil, you're right. This is much ado about nothing.
My mistake was trying to directly change the connection string in the properties window of an existing connection. Knowing that the Sql Server 2012 native client provider was SQLNCL11, I change the provider there as well as the server name for the Sql Server 2012. That's when I got the invalid provider message.
I did this because, when I tried to make the change at the connection manager, it couldn't find the name of the Sql Server 2012 server in the server list.
I found out later the server had been taken down for some reason. So, this morning, I tried to make the connection again by changing the name to the Sql Server 2012 server. Voila. It worked. I was able to make the connection.
I haven't run the package yet but I did test the connection and there were no problems there.
If you hadn't questioned why I did what I did and why I needed to do it, I probably would have figured that was the only route available to me. So thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply