Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.

Datareader and dataset

Hi

I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.

The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.

I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?

regards

JimSorry about that - found and article on the web site which told me that I was going about it the wrong way - should have read it first!

The article was at http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.aspx#storedprocs
and showed me how to access data from a stored procedure and put it into a dataset and a dataview.

regards

Jim

Dataflow Tab:There is no ODBC Source option in the Toolbox

I need to extract data from tables in a database that I can only access via ODBC.

I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.

However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.

This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process

How do I extract data from tables in a database via ODBC?

Thanks in advance

Dave

There is too, though it's not marked as such. Use the Data Reader Source.|||

You can use the script component as source.

|||

Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.

I do think there is a peformance overhead and its slower than the ODBC connector in DTS.

|||

well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes

|||

Vijay Thirugnanam wrote:

You can use the script component as source.

True, though it shouldn't be faster than using the prepackaged source connectors.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.

|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?|||

Hope I'm not too late in giving my feedback here. The issue that we have faced here is where there are not too many lookups object, but it keeps giving the same error message.

What I have tested was to optimize the usage of the Lookup object by not selecting the "Use a table or a view:" selection. Instead, I chose the "Use Results of an SQL Query:" selection. I had only 3 lookup objects, 2 of which are looking up to a table with less than 10000 records and less than 8 columns in average. But the other table that we did the lookup has about 2 million worth of records, with about 10 - 12 columns.

I did the changes for all of the lookup object and some how everything was working again. I think it would be the same issue with the memory restrictions as well.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?|||

Hope I'm not too late in giving my feedback here. The issue that we have faced here is where there are not too many lookups object, but it keeps giving the same error message.

What I have tested was to optimize the usage of the Lookup object by not selecting the "Use a table or a view:" selection. Instead, I chose the "Use Results of an SQL Query:" selection. I had only 3 lookup objects, 2 of which are looking up to a table with less than 10000 records and less than 8 columns in average. But the other table that we did the lookup has about 2 million worth of records, with about 10 - 12 columns.

I did the changes for all of the lookup object and some how everything was working again. I think it would be the same issue with the memory restrictions as well.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.

|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?

Databases missing from Backup Task of Maintenance Plan

I'm moving databases from SQL7 to another server with SQL2005.

I have created new databases in SQL2005 via a restore from backup files created in the old server. Now I want to back up these new databases but they don't show in the Backup Task of a new or existing Maintenance Plan in Management Studio. I can backup them manually though. Any ideas?

I assume you have restored this database to a local 2005 server. If this is the case, they should show up. Maintenance plans can not make connections to SQL 7. Can you see this database via Management Studio?|||

Rob,

Yes, that's correct. I restored the databases to another server running SQL2005. None of the databases show up in any task of Management Studio's Maintenance Plans (eg Backup, Shrink, ReOrg)

-Bill

|||

Found the problem - the compatibility level was set to SQL Server 7.0 by default ( under database properties->options). As soon as I changed the compatiblity level to SQL Server 2000, the databases showed up in the Maintenance Plan tasks.

Thank you for your responses.