Connection in transformation component

Hi,

I'm on writing a custom data flow component (transformation). For this I need access to an external datasource. As best practice, it seems that you should use a connectionmanager in the package.

But I don't really know how to access them.

I've overwritten the AquireConnections() to lookup in the RuntimeConnectionCollection for the connection managers. But It seems it doesnt get called. If I call it my own, for example in validate (just for debugging purposes) the collection is empty, although there are two connectionmanagers in the package.

What am I doing wrong or has someone a code snippet for me.

Thanks T.

To make the package connections available within the AcquireConnection or ReleaseConnection method, (make sure you use the latter to close any connection resources), you first have define the component as having one or more connections. You do this in the ProvideComponentProperties method, so just add it and give it a name, description if you feel like it.

IDTSRuntimeConnection90 runtimeConnection = ComponentMetaData.RuntimeConnectionCollection.New();

runtimeConnection.Name = "Reference Data";

This acts as a placeholder, so that if you were to now look at the built-in Advanced UI Editor, on the first page you will see the connection listed, ready for you to choose one of the existing package connections.

Connection Bussy.

Hello to everyone,

I have developed a package with multiple Data Flow Tasks, all of the data goes to a sql server database. This package worked perfectly well on a development machine (with win 2k3), but now I 've deployed the package on a production server (with win 2k3 too), and when I execute the package with a web service (network service user) I get the following error:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command". Source: My DataFlow TimeStamp:7/7/2007 2:18:08 PM Error: The "input "OLE DB Destination Input" (27)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (27)" specifies failure on error. An error occurred on the specified object of the specified component.

I wonder if it could be permission issue with the Network Service user or maybe SQL SERVER must have any parameter set that I'm not taking into consideration.

any clue?

regards

Sounds like a problem with SQL Server rather than SSIS. Did you try searching the web it? I did and I came up with this:

FIX: "Connection is busy with results for another command" error message occurs when you run a linked server query

(http://support.microsoft.com/default.aspx/kb/822668/)

-Jamie

|||hello, thanks for your response.

well, I was taking into consideration to install ServicePack 2, so i'm downloading it now. I guess if this could be a problem with the version of SQL SERVER, On a previous machine I was using the Enterprise Edition and everything was running well and now I'm just with the Standard one. Could it be an issue to take care of?. I'll install SP2 and I'll tell you what happened.

best regards.|||
I found the answer,

well, my package has 20 DataFlow Tasks, and when i debugged the hole package i realized that I always had 3 or 4 failing tasks with the "Connection is busy...." error message, so I decided to link them with a Completion Precedence Constraint (the blue one) and it worked fine.

I wonder if this problem occurs is because a lack connections on the Sql Server connection pool or something related.

thanks anyway, It worked for me.|||

I received the same error today and only have 4 data flows although it has been working without any issues for the last few weeks. Is there a SQL server or SSIS parameter that needs to be bumped up somewhere?