-1

Recently we moved few databases from SQL Server 2008 to 2012. Couple of SSIS package on SQL Server 2008 were dependent on it.

So to update the packages so that they point to new DB Server, we modified the server name in the connection string in config file. Also we updated the SQL Server native client version from 10.1 to 11.0 in Config file.

After doing so as well, package is still failing and in the error message, we can see that it is still getting the connection string from SSIS package instead of Config file.

Am I missing something or did I screwed things up????

Any help would be appreciated.

  • do you have SSIS project file for that package? – Biju jose Nov 12 at 9:12
  • Did you switch to project deployment or is it still package deployment model? – Antoine Hernandez Nov 12 at 18:51
1

Does SQL Server Native Client 11.0 exist on the server that is running those packages? What are the arguments being passed to dtexec? If it doesn't have at least /rep ewi add that in there and then kick off the package.

What I expect you'll see and why I'm putting this down as an answer.

In the output that follows from requesting (Error, Warning, Information) events SSIS will report as Information or Warning, that it attempted to apply a configuration from this file (myconfig.dtsconfig) and something went wrong it (couldn't access the file, the format was bad, a particular configuration was bad - something was wrong).

BUT DON'T WORRY, I'LL JUST HAPPILY USE MY DESIGN TIME VALUES. I HOPE THAT'S OK!

Ok, it's not quite that loud about it but yes, when applying configurations SSIS took this baffling decision to validate everything to the Nth degree and if something is "off", the package won't start and you're hit with VS_NEEDSNEWMETADATA which is like a syntax error and the package won't even start.

But hey, you indicated the package should use a configuration file when it runs but in the event SSIS doesn't find the file or can't use it, SSIS will try to soldier on with design-time values. I had a friend who managed to populate production over a number of days because a bad configuration file resulted in the production package using development source settings.

Depending on what's wrong determines the resolution. As the packages were using the configuration file beforehand, it seems unlikely that file system permissions are to blame. That reduces the problem to the contents of the file. Either eyeball it/read the error warning/information output message or copy the file to a location with Visual Studio installed and open the package there and see what VS says when it attempts to update/configure the dtsconfig file.

Not the answer you're looking for? Browse other questions tagged or ask your own question.