Cannot Acquire Connection on Database

Question

I've seen many issues posted, but none of the solutions appear to help.  I also see a pattern that I've not seen described that may provide insight to my issues as well as some of the others' whose problem was not solved.

I am building my package(s) programmatically and they contain several connections; SQL Server, flat files, etc.  As my C# code executes, building my packages, each component in my Data Flow task that requires me to execute the AcquireConnection method of the runtime wrapper results in an VS_ISBROKEN upon executing the Validate method on that wrapper.

When I open the package in the DTS Designer, the component looks fine, it just requires me to Edit it, clicking the Columns button for example in an OleDB Source component.  Otherwise, the component has no outputs.

When the component being created in code uses a connection from the package connection manager, that connects to a SQL Server database, that is my default database on the source server, the code successfully creates the component.  However, if my default database (as specified in my login properties on that server) are master or some other database, the AcquireConnection(null) on the component wrapper and subsequent Validate() return the VS_ISBROKEN.

If, however, I change my default connection to the source database that I am trying to connect to, all is well; I get the VS_ISVALID for my Validate() and my component produces an output collection containing the columns of the result set returned from the SQL command.

A ReinitializeMetaData() call on the component that is broken yields in a try/catch block throws a COM error, 0xC0202009.  A ReinitializeMetaData() call on a component whose status is VS_ISVALID, throws no error.

Bottom Line:

When I build Data Flow components which use a connection that calls a database that IS NOT my default database on the source server, my component fails validation.  When I set my default database to the intended database, and re-execute my C# code, the component is created successfully.

Environment:

Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
Microsoft .NET Framework
Version 3.5 SP1

Installed Edition: Enterprise

Microsoft Visual Basic 2008   91904-270-2326001-60907
Microsoft Visual Basic 2008

Microsoft Visual C# 2008   91904-270-2326001-60907
Microsoft Visual Studio 2008 Tools for Office   91904-270-2326001-60907
Microsoft Visual Studio Team System 2008 Development Edition   91904-270-2326001-60907
Microsoft Visual Studio Team System 2008 Development Edition - ENU Service Pack 1 (KB945140)   KB945140
Microsoft SQL Server Analysis Services Designer
Version 10.0.1600.22

Microsoft SQL Server Integration Services Designer
Version 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

This is loaded on a Virtual Machine running Windows Vista.

Any information/suggestions that anyone may have would be helpful.

Paul

Answer 1

...Oh Yeah, the question:  Why is this happening, why does the datasource connect only to the database that is the default database on the source server?

One more thing, once the AcquireConnection method is called, the Initial Catalog and Data Source properties are set to empty, even if the database is the same as the default database on the source server.

Answer 2

This is not very well documented. Before you call ReinitializeMetaData, you have to call AcquireConnections. And before you call AcquireConnections you have to go to each component RuntimeConnection and initialize ConnectionManager property with the appropriate connection manager object. Only then you will be able to make it work. Give it a try and let us know how it goes.

---

SSIS Tasks Components Scripts | http://www.cozyroc.com/

Answer 3

CozyRoc:

Thanks for your response.  I've "think" I've done that; I have a class method that creates the connections that I'll be using in the packag and then I execute the following:

            dfComponent.RuntimeConnectionCollection[0].ConnectionManagerID = p.Connections[strConnection].ID;
            dfComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(p.Connections[strConnection]);

where strConnection is the string name of the connection that I have already built.

When ever I create a component that is acquiring the connection to a data source, other than my default database on my server, it fails the AcquireConnection method (the Validate method returns the VS_ISBROKEN.  If, however, I am acquiring the connection to a data source that is my default database, all is well.

Are the above lines what you are talking about, or is there something else.

I've created a class that has a collection of methods which build my component/tasks/package.  I call each of the methods or collection of methods as necessary.  Even when I have all of the code in line, I still am having problems.

Answer 4

This looks correct. This was the element in question.

When you setup a data source, you can explicitly select database to which you want your data source to point. If you don't select any, I think it will work with the default database on the server. So the question is do you explicitly specify the database in your data source when you build the package?

---

SSIS Tasks Components Scripts | http://www.cozyroc.com/

Answer 5

Yes I do.  Another anomaly that I see as my code it running, once I execute the AcquireConnection(null) method, the Data Source as well as the Initial Catalog are reset in my connection string of my package.  And when that connection is the same as my default database, I'm ok.  I understand that when I throw T-SQL at my server, it's going to my default, and that's likely the reason that I can get it to work sometimes; it's not really working, it's just that I'm not seeing it fail. 

I am sure I have done something wrong, I just don't know what it is.

Maybe I'll go back to "Hello World"

Again, I am running this on a VISTA VM using VS2008 with 4GB of ram.

Thanks for your help

Answer 6

Have you tried specifying the initial catalog in the connection string instead?

---

SSIS Tasks Components Scripts | http://www.cozyroc.com/

Answer 7

Yes, the connections are specified, programmatically, and called during the construction of the data flow task.  When the connection is used in the following

            dfComponent.RuntimeConnectionCollection[0].ConnectionManagerID = p.Connections[strConnection].ID;
            dfComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(p.Connections[strConnection]);

the connection string still retains the Data Source and Initial Catalog properties.  However, once the AcquireConnection(null) method is called, the p.Connections[strConnections].ConnectionString no longer has a vaule for the Intial Catalog, or Data Source.  It is here that, I believe, the only reason there is a successful execution of the T-SQL code against the data source is that data source is the default database under the account I am using to execute my code.

To recap, I'm pulling from multiple data sources, using C# in a VS2008 development environment on a Vista VM.  Could I be ignoring important environment settings, pooling, etc.?

Please keep your thoughts and ideas coming.

Paul

Answer 8

What types of datasources do you have?  Do you have any non-SQL Server databases, like Informix?

Answer 9

Paul,

I'm not sure if this was discussed, but have you tried specifying InitialCatalog property in the properties collection of the ConnectionManager like this:

manager.Properties["InitialCatalog"] = <your initial catalog>

and then calling AcquireConnection(null) method.

Another suggestion. You may set the catalog/database in the connection by executing the query:

USE[<initial catalog>]

---

SSIS Tasks Components Scripts | http://www.cozyroc.com/