Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)sql

Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)

Problems connecting to "(LOCAL)" from SSIS

I've recently moved from June CTP to RTM. I'm now having a problem connecting to the (LOCAL) server in SSIS via a Native OLE DB\Microsoft OLE DB Provider for SQL Server connection. The error is:

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

I'm connecting with Windows Authentication, and I can connect to (LOCAL) with Windows Authentication via Management Studio. If I change the server name to the actual name of my development box then it will connect. I'd like to use (LOCAL) though so it's easily portable between different development environments.

Anyone have any ideas?This is not an SSIS problem; the underlying OLE DB provider is evaluating server names.

Have you tried with "localhost" or "."?

Thanks.|||This doesn't appear to work unless you have TCP/IP enabled. It is disabled by default. It seems that "(local)" no longer forces the use of Shared Memory.

-Doug

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem: SSIS, ODBC and Progress Database

Hi,

The project I′m working needs to import data from a Progress Database. I had configured my ODBC (MERANT 3.60 32-BIT Progress SQL92 v9.1D) driver and everything seems to be ok.

But when I tried to create a DataRearder Source pointing to my ODBC Data Source, and open the next tab I received this error message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Anyone can help ?

DataReaderSource requires a managed ADO.Net connection, so try to create a ADO.Net:ODBC connection first then point to that connection at DataReaderSrc.

Thanks

Wenyang

|||

Wenyang,

Since the first time I did what you said. I create a ADO.NET:ODBC connection and I received a successifull message.

But when I create create a DataReader Source and point to my ADO.NET:ODBC connection I receive this message:

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Appreciate your attencion.

[ ]′s

|||

um...normally this error is only seen when you use a wrong type of connection manager(e.g. a native one) at DataReaderSrc.

If you did use .Net provider for ODBC in your connection manager, did you see success when pushing the button "Test Connection" in your connection manager? We cast the connection to IDbConnection, I see no reason why you succeeded there but failed in DataReaderSrc on this error...

Thanks

Wenyang

|||

were you able to slove this problem..

I am also facing similar problems.

|||

Even i am facing similar kind of problem. But i my case i trying to connect to JDE through ODBC driver.

When i test the connection in connection manager, it succeds. but when i try to pull data using DataSource reader, it gives the same error,

"Error at DAta Flow Tast[DataReader Source [1]]: Cannot acquire a managed connection from the run-time connection manager."

Can any body help me ?

Regards,

Chetan

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

I had a similar issue, the error I was getting:

"Error at Data Flow Tast[DataReader Source [35]]: Cannot acquire a managed connection from the run-time connection manager."

I changed to the ADO.NET ODBC as suggested above and it worked for me, THANKS for the tip!

Before changing it to ADO.NET ODBC it would work in the connection manager for the test but not the data read task. You would think Microsoft would either give better error messages or not allow the connection to be selected or something instead of just giving generic messages.

|||

Hi, I am facing problem to connect to progress db in .net,

Would you please let me know of the connection string and other settings ,

I currently get connection string wrong format problem , but the weird part is when i test connection, it passes.

but then i get error when i try set anything else.

Thanking you.

|||

Problem solved

|||

Hi,

Could you please share your experience with us?

Thanks

Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

Problem with WMI tasks : waiting for files in parallel in a SSIS package

Hi,

I have a problem with the task "event watcher".

I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"e:\\\\temp\""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.

I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.

Could you help me with this issue?

Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent

Thanks a lot.

Julien.

I forgot to tell you that the package work when I execute it on debug, on the local machine but not on the server with a sql job.

Thanks.

|||

I don't know anything about WMI, but it looks like you're waiting for files to be created. Have you looked at the File Watcher Task that's available for download from SQLIS.com?

http://www.sqlis.com/23.aspx

Hopefully someone will have an idea about the WMI problem, but this might get you around the problem in the interim...

|||

Hi,

I don't really want to add tasks that are not supported by Microsoft, that's why I tried this WMI stuff.

Any idea?

Thanks.

Julien.

Problem with variables

Hi,

I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try

Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.

The destination variable is a date ant it is writable.

Any ideia of what is happening or any alternative sugestion?

Thank you in advance

Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.

|||

Try using these function !! It may solve u r problem...

Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite(varName, vars)

Try

vars(varName).Value = value

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Private Function readVariable(ByVal varName As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockForRead(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Dev

|||Thank you both very much. I will try to do as you suggest.
|||

Anthony Martin wrote:

Variables are re-set to their original values after package execution.

Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc

It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.

Does that make sense?

|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.

I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.

Problem with variables

Hi,

I'm new to ssis and unfortunatly I'm having problems already. I'm trying to set a value to a variable in a Script Component using the following code:

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try

Dim vars As IDTSVariables90
Me.VariableDispenser.LockForWrite(varName, vars)
Me.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

It runs without any trouble and no errors. I've made several tests on the input variable and it's just fine. At the end the destination variable does not change. Everyone who is using that code does not complain.

The destination variable is a date ant it is writable.

Any ideia of what is happening or any alternative sugestion?

Thank you in advance

Variables are re-set to their original values after package execution. You can monitor their values during run-time by adding a breakpoint and putting a watch on the variable.

|||

Try using these function !! It may solve u r problem...

Private Function writeVariable(ByVal varName As String, ByVal value As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite(varName, vars)

Try

vars(varName).Value = value

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Private Function readVariable(ByVal varName As String) As Object

Dim result As Object

Try

Dim vars As Variables

Dts.VariableDispenser.LockForRead(varName)

Dts.VariableDispenser.GetVariables(vars)

Try

result = vars(varName).Value

Catch ex As Exception

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

Throw ex

End Try

Return result

End Function

Dev

|||Thank you both very much. I will try to do as you suggest.
|||

Anthony Martin wrote:

Variables are re-set to their original values after package execution.

Correct in what you see, but a bit of while lie, as there is no explicit reset, there is just no Save of the package state. The instance of the package that is executing is just thrown away at the end, so you never see what happened unless you ask, breakpoints etc

It is perhaps most confusing when in the designer and executing a package, that you cannot just examine a variable value when you feel like it, and the values are not there to view at the end of execution. The simple reason for this is that the instance in the designer is not actually the instance being executed, as the designer itself cannot execute a package. You'll notice that as part of execution a package is saved. This is because it needs to be saved, so it can be loaded by the debug host, an entirely separate process that needs to load the package from scratch. This debug host (dtsdebughost.exe) is what really runs the package for us, and it then sends little messages to the designer so we can see what is going on, and get the pretty colours and numbers. Similarly if we look at a variable value in the designer, even whilst a package is executing, we are looking at an offline copy. To see the real live copy in the debug host we need to use the Watch window and a breakpoint, so that the designer can ask explicitly for the current value.

Does that make sense?

|||It makes a lot of sense Anthony. Maybe my code worked but I just could not see the change. I looked at the package explorer and it kept it's original value.

I feel a little embarassed for making you guys waste your time with something so trivial.
Again, thank you.
|||(Darren?) A common mistake, I know I made it. It is confusing, and certainly not immediately obvious as what you see all appears OK on the surface, just the values being wrong! Not a waste of time at all to ask.