SSIS - Connection String Issue

MA2005

Hi

I have been trying to resolve a connection string issue all day, and havent figured out whats the problem. I keep getting the Acquire Connection error everytime I run my package. Can you someone review the format of my connection string, and confirm that it is indeed the correct format:

I have two conn strings specified in my config file, one is to connect to a remote database and one is for local. I have validated the connectivity from my server to these databases via Management Studio, and it seems to connect fine. But when I run my package it keeps failing.

SQL Server Authentication(Remote Database Server):

Data Source=server_name;Username=useraccount;Password=xxxxx;Initial Catalog=dbname;Provider=SQLNCLI.1;

Windows Authentication(Local Database on the server):

Data Source=xxxxxx;Initial Catalog=xxxxx;Provider=SQLNCLI.1;Integrated Security=SSPI;

I would really appreciate if someone can help me with this, I have been trying to troubleshoot this all day today, and still havent had any luck. Thanks.

Phil Brammer


What is the error exactly

Rafael Salas

Under which account is the package running This is critical when using windows authentication as the user running the package needs access to the database specified in the connection manager.

Phil Brammer

Rafael Salas wrote:

Under which account is the package running This is critical when using windows authentication as the user running the package needs access to the database specified in the connection manager.

To back up Rafael, your connection strings look properly formed, if that helps.

MA2005

I get the following errors when I do the Package Validation, when finishing up the Package Installation Wizard(Utility):

Error: The AcquireConnection method call to the connection manager "DatabaseName" failed with error code XxC0202009.

Error: An OLE DB Error has occured: Invalid Authorization Specification

Error: An OLE DB Error has occured: Invalid Connection String Attribute

MA2005

The SQL Server Service Account is the run as account if this package was scheduled as a job. But right now, I am just executing it without even scheduling it, and its returning Acquire Call Method errors. Please refer to the erros, no idea whats happening

Phil Brammer

Missed it the first time through...

It's "User ID=", not "Username="

Rafael Salas

Ha! good catch Phil... here is an example just in case:

Provider=SQLNCLI.1;Data Source=XXXX;Password=XXXX;User ID=XXXX;Initial Catalog=XXXX

Data Source=XXXX;Initial Catalog=XXXX;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

MA2005

Thanks, Phil, it passed validation. But its failing when I run it as a job on SQL Server. Here is the error, that I was initially talking about:

Event Name: OnError

Message: Executing the query "exec dbo.uspSaveMain_CEAImport " failed with the following error: "Invalid object name 'WebStrategy.LAF.LoanApplication'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Operator: NT AUTHORITY\SYSTEM

Source Name: WSToCLIPS_Process

Source ID: {9892CB71-755D-454F-93DB-8FC7CECC524B}

Execution ID: {478D2AA4-D6A2-419A-9CDD-B02C94038E9C}

Start Time: 1/24/2007 3:20:24 PM

End Time: 1/24/2007 3:20:24 PM

Data Code: -1073548784

MA2005

basically the following error is appearing all over the place in the event viewer, as I am writing to Windows Application Log:

Event Name: OnError

Message: Invalid object name 'WebStrategy.LAF.LoanApplication'.

Operator: NT AUTHORITY\SYSTEM

Source Name: InitiateCLIPSProcess

Source ID: {C0DA9C9E-3BB7-4B87-893A-0258FAFD2FE3}

Execution ID: {478D2AA4-D6A2-419A-9CDD-B02C94038E9C}

Start Time: 1/24/2007 3:20:24 PM

End Time: 1/24/2007 3:20:24 PM

Data Code: 0

MA2005

So basically here is what I am running against now: Cant seem to find anything on google, anyone

Invalid object name. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Phil Brammer

What are you using as your source An OLE DB or a data reader

Phil Brammer

You might want to build your SQL in an expression first... I'm not sure about the using the parameters on the stored procedure.

MA2005

OLE DB. Just wanted to let you know, this package runs seamlessly on my local SQL server, where both the source and destination exists locally. Both are OLE DBs. As soon as move this package over to the DEV environment, it keeps coming back with this Invalid Object Name error. Thanks.

Phil Brammer

MA2005 wrote:

OLE DB. Just wanted to let you know, this package runs seamlessly on my local SQL server, where both the source and destination exists locally. Both are OLE DBs. As soon as move this package over to the DEV environment, it keeps coming back with this Invalid Object Name error. Thanks.

But which OLE (SQL Server auth or the Windows Auth) connector throws the error