August 5, 2011 at 4:05 pm
Hi !
I have the following expression in my connection string and i was getting the error message.So can some one help me with the following connection string expression ???? Thanks in advance
Connection string Expression :
"Data Source="+ @[User::V_VirtualIP] +","+ @[User::V_PortNO] +";Initial Catalog=master;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-Package2-{CB9550AD-8DD8-4768-BCC7-AC8566787460}VA10DWVSQL007.master;Auto Translate=False;"
August 5, 2011 at 7:06 pm
I'd compared the concatenated version (wahtever string gets sent for the connection) to this : http://connectionstrings.com/
It's usually a small typo or using the wrong driver. Really hard to debug without access to the server AND the real connection string.
Fortunately you have access to both.
If you get a more usefull error message and google doesn't help out, come back here and we'll see what we can do.
August 7, 2011 at 1:44 pm
Hi !
I guess i am going wrong with the expression in the connection string where i was using the virtualIP Address and Portno parameters for connecting to server ,SO can some one help in the SSIS expression where i can connect to server by using VirtualIP and Portno.Thanks in advance
Please find the error msg i was getting as below
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xx.xxx.xx.x,PPPP.master" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task [SSIS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at Package2 [Connection manager "10.234.66.8,1433.master"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "SQL Server Network Interfaces: Connection string is not valid [87]. ".
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
August 7, 2011 at 2:02 pm
Never had that issue... stepping aside.
August 7, 2011 at 2:38 pm
Hi !
I even Tried in the expression by selecting the server-name property and gave the following expression . but still having the connection issue .can some one help me with the expression in connection string . Thanks in advance 🙂
@[User::V_VirtualIP] +","+ @[User::V_PortNO]
August 8, 2011 at 6:09 am
My connection string to SQL Server usually looks like this (using Windows authentication):
Data Source=myServer;Initial Catalog=myDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;
Try connecting to the server using the editor, and if that succeeds, look at the created connection string and then turn it into an expression.
Maybe it easier to put an expression on the ServerName, instead of on the entire connectionstring.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2011 at 6:26 am
Hi !
I do have a list of Virtual IP Address and Port Numbers of different servers in a table and i would like to make the dynamic connection and in that process i was using the ServerName Property Expression as @[User::V_VirtualIP]+","+@[User::V_PortNO] and the connection string looks like below
Data Source=,;Initial Catalog=master;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package2-{2BACFCD4-6CFB-449D-B9E4-E8B0C11097DA}xx.xxx.xx.x,1433.master;
August 8, 2011 at 6:53 am
Is the application name necessary?
The servername is empty, is that because the variables are also empty?
Try the following format for the server name:
TCP:myServerName,port
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2011 at 8:53 am
Yes !In dynamic connection we could only see empty values ..so the connection string looks like this . and in expression i was giving the myservername+","+ portno where myservername and portno are my variable and in dynamic connection this values keep on changing .
August 9, 2011 at 1:03 am
So the variables are empy.
How do they get populated?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2011 at 6:47 am
Thanks every one now its working the expression in server name property would be like as below
@[User::V_VirtualIP] +","+ @[User::V_PortNO]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply