Running multiple queries under one ODBC connection

Question

Running multiple queries under one ODBC connection

dmusson17

Ten Centuries

Points: 1235
More actions
February 9, 2009 at 8:42 am

#127448

I'm using an ODBC driver that connects to an outside database that only accepts certain types of select statements and has row limits.
In my SSIS package I currently have a for loop that contains a data flow where I use a data reader source and an OLEDB destination to connect to the outside database, run my select statement, and then put the query results into a table. AS I loop through I update the contents of the select statement to pull in the next batch of records within the row limit constraints.
All is working well, but the way it is working is far from the most efficient. I am basically reconnecting to the database everytime I run a select statement which can total into the hundreds for a single ssis package run. The outside database accepts limit and offset parameters in the select statement but I cannot figure out how to connect to the database once in my ssis package and run all my queries under the one connection (while also updating the select statement as I go).
Any help or direction is much appreciated.
Dave

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

David Musson (2/9/2009)
I'm using an ODBC driver that connects to an outside database that only accepts certain types of select statements and has row limits.
In my SSIS package I currently have a for loop that contains a data flow where I use a data reader source and an OLEDB destination to connect to the outside database, run my select statement, and then put the query results into a table. AS I loop through I update the contents of the select statement to pull in the next batch of records within the row limit constraints.
All is working well, but the way it is working is far from the most efficient. I am basically reconnecting to the database everytime I run a select statement which can total into the hundreds for a single ssis package run. The outside database accepts limit and offset parameters in the select statement but I cannot figure out how to connect to the database once in my ssis package and run all my queries under the one connection (while also updating the select statement as I go).
Any help or direction is much appreciated.
Dave

Dave,

Have you tried setting RetainSameConnection property of your connection manager to True? This should preserve one and same connection for the lifetime of the execution.

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

dmusson17 Ten Centuries Points: 1235 More actions · Answer 2

Thanks for the reply.

I have, but as soon as I change retainsameconnection to true, the first time I try to connect to the data source in the package (in an data flow as a data reader source ODBC connection) it fails:

Error: 0xC0047062 at Data Flow, DataReader Source and run query [1]: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.

I have also tried "opening" the connection with an Execute SQL component before the Data Flow, but then that fails also.

Any additional direction and/or ideas are much appreciated!

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

David Musson (2/10/2009)
Thanks for the reply.
I have, but as soon as I change retainsameconnection to true, the first time I try to connect to the data source in the package (in an data flow as a data reader source ODBC connection) it fails:
Error: 0xC0047062 at Data Flow, DataReader Source and run query [1]: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.
I have also tried "opening" the connection with an Execute SQL component before the Data Flow, but then that fails also.
Any additional direction and/or ideas are much appreciated!

David,

I was able to reproduce this issue. It looks like serious limitation/bug on the part of Microsoft. They are incorrectly closing the connection in the Data Source Component without checking RetainSameConnection property. What I'm amazed is why none has reported this issue before. You can submit a bug report here.

As a workaround what you can do is create a source script component and feed the data coming from the ADO.NET provider to your data flow yourself.

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