SSIS connection string environment variable problem
-
I've got an SSIS project that uses an environment variable for it's connection string.
This is the connection string: Data Source=localhost;Initial Catalog=RESDataMart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
It's a perfectly valid connection string and it works on every machine I've installed the project on except one.
I'm getting the error: An Error occurred while setting the value of property "InitialCatalog". The error returned is 0x80020009" The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes...
I cut and pasted that string from an environment where it works. I've tried editing just the Initial Catalog. I've tried changing it to use SQL authentication instead. I get the same error.
I've had problems in the past with corrupt connection managers but I'm using an environment so I'm pretty well stumped.
"Beliefs" get in the way of learning.
-
make sure your connection string isn't being overridden by any parameters in your "dtexec.exe" command.
course just a wild guess LOL
-
Robert Frasca (12/22/2016)
I've got an SSIS project that uses an environment variable for it's connection string.This is the connection string: Data Source=localhost;Initial Catalog=RESDataMart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
It's a perfectly valid connection string and it works on every machine I've installed the project on except one.
I'm getting the error: An Error occurred while setting the value of property "InitialCatalog". The error returned is 0x80020009" The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes...
I cut and pasted that string from an environment where it works. I've tried editing just the Initial Catalog. I've tried changing it to use SQL authentication instead. I get the same error.
I've had problems in the past with corrupt connection managers but I'm using an environment so I'm pretty well stumped.
All it would take to create this problem is for the environment variable on that particular machine to have an extra semi-colon in it's value at that particular point. So it may not be the SSIS package that has the problem, but the "environment".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Just to reiterate. It is not a problem with the connection string. There are absolutely no extra semi-colons and it has been checked 100 times at least. In fact, it installs and runs perfectly well on six other servers without requiring any intervention. The only thing left to try is uninstall SQL Server 2016 entirely and try reinstalling it. The only thing that is different from the other servers is that SSIS was not installed during the initial install, it was installed later and sp1 was applied. Actually, applying sp1 fixed a couple of things for us so I know that's not the problem.
"Beliefs" get in the way of learning.
-
Robert Frasca (1/2/2017)
Just to reiterate. It is not a problem with the connection string. There are absolutely no extra semi-colons and it has been checked 100 times at least. In fact, it installs and runs perfectly well on six other servers without requiring any intervention. The only thing left to try is uninstall SQL Server 2016 entirely and try reinstalling it. The only thing that is different from the other servers is that SSIS was not installed during the initial install, it was installed later and sp1 was applied. Actually, applying sp1 fixed a couple of things for us so I know that's not the problem.I can't think of anything that would work on multiple other servers and fail on just one, without something being different on that one, that perhaps was assumed to be correct. I'd take a very close look at the Properties of the connection manager and look at the Expression being used, to ensure that there's nothing unusual going on because of conditions present on this one server that might, for whatever reason, differ from the others. I'd go so far as to try and edit the SSIS package directly on that particular box, and see what values are being supplied. If it's being executed via DTSEXEC.EXE, then be sure to also check the command line to ensure that the value for any package variable is not being over-ridden, and I'd even be sure to make the package log that connection string value somewhere, so you can see how/where/if the corruption is taking place, before de-installing SQL Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
It seems that i have similar issue.
Everything works on several environments, but i get the same error on another env:"An Error occurred while setting the value of property "InitialCatalog". The error returned is 0x80020009" The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes..."
Just like Robert, i checked my connection string million times.
Checked package parameters using "All executions report" and connection string seems to be fine.
This thing is just exploding my head. - sha4lo - Monday, January 23, 2017 5:52 AM
This actually turned out to be a data error although I didn't really see anything wrong with the data that was there. We override the connection string in the environment at runtime with a value from a table in certain cases. I KNEW from other testing that the string in my environment variable was valid so I cut and pasted it, replacing the value in the table and it started to work. The string in the table is created dynamically from a UI and the only difference was that the one in the table, (the erroneous one), did not have the "Persist Security Info=True;" clause in it. I thought that was the default which is why I'd kind of ignored it's absence. It certainly didn't have anything to do with semi-colons.
"Beliefs" get in the way of learning.
-
In my case connection string is overridden in Management Studio on package level (Integration Services Catalog -> Right click on deployed project and Configure)
What type of authentication do you use? Windows or SQL?
I use Windows auth.EDIT:
"This is the connection string: Data Source=localhost;Initial Catalog=RESDataMart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
Didn't notice previous message. It seem that you use Windows auth too.
I'll try to add "Persist Security Info=True;" and see how it works.Thanks!
- sha4lo - Monday, January 23, 2017 7:18 AM
Actually I use SQL authentication for the connections I was having problems with. The windows auth connections have always worked fine. Do you have an environment associated with the project? If so, and the project is wired to use the environment connection string parameters, the string that is executing may not be what you think it is. The environment parameters trump the default values and you can tell if they're being used if the connection string name is underlined. If you do have an environment, right-click it and select properties. Click on Variables to the the variables page and examine the values associated with the connection string parameters.
"Beliefs" get in the way of learning.
-
Well, it turned to be deployment/configuration issue.
For some reason ServerName property on "Configuration Manager" tab had full connection string instead of actual server name.
Someone or something overrode this property after deployment/configuration.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply