date and time diffs: datareader

Showing posts with label datareader. Show all posts

Sunday, March 25, 2012

DataSet/DataReader Performance

Hi,

I've run some tests on fetching data from a SQL Server 2005 database using a DataSet and a DataReader.

In my database, I'm calling a stored procedure that fetches one record from a table. The Sql Server Profiler says the call takes between 20 - 50 ms (within SqlServer). The C# application and Sql Server are on the same machine.

DataSet - performance: average 575ms (for steps 3 & 4) :

1.) Create Connection

2.) Create Command

3.) Create Adapter

4.) Fill DataSet using Adapter

DataReader - performance: average 200ms (for steps 3 & 4):

1.) Create Connection

2.) Create Command

3.) Execute Reader

4.) Read with Reader

Does this performance jibe with what others have seen?

Can the performance be improved either on the DataSet or the DataReader?

Any feelings on how performance will be affected with larger record sets?

This is by design. A datareader is a readonly forward cursor which is optimized for reading data. It should be prefered whenever reading data from a datasource.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for the reply. Are there variants that can be set to opimize the read performance of the datareader (meaning to decrease the 200ms time to respond)?

Thanks,

Andy

|||

Hi,

the only thing that would be really affecting the performance would be to decrease the retrieved data with choosing some parameters / filters in the condition.

HTH, Jens Suessmeyer-

http://www.sqlserver2005.de

Thursday, March 22, 2012

dataset or datareader?

i need help to know what is the best practice

i have a stored proc which returns 4 different resultselts

will that be easy to use dataset or datareader?

my purpose of using dataset/datareader is to load the data in a class

thanks.

The DataReader is substantially faster than using a DataAdapter to fill a DataSet. Since you are just looking to fill other objects with the read data anyways, the DataReader would definitely be your best bet. Using the DataSet in this context would just be an extra (and unnecessary) step.

|||

Datareder is faster but it also work in connected envorionment. So be sure that if you are development n tier application and want to pass data between tiers and want to access them remotely then it would not work. Then you need to work with data sets. However in general Data Reader is faster.

|||

the above underline staement is not correct.,

you can work with datareader if you are developing in n tier application and currently i have n tier env and passing the datareader from daab to web app and i'm closing my datareader in the web_app.

|||

the above underline statement is not correct.,

you can work with data-reader if you are developing in n tier application and currently i have n tier env and passing the data-reader from daab to web app and i'm closing my data-reader in the web_app.

|||

nisarkhan:
you can work with data-reader if you are developing in n tier application

Nisarkhhan is correct -- there is no problem doing this. I do it too. All you do is use a DataReader to build some type of collection and pass the collection around. Or you can make the collection a property of a class and pass the class around. I too do this all the time.

Monday, March 19, 2012

Datareader, DBNull and ? Operator

Hi!

I was trying to use the ?? operator with an DBDatareader. eg:

long lValue = Convert.ToInt32(objReader["ParentID"] ?? -1);

which throws the following exception:"... Object cannot be cast from DBNull to other types."
I think the reason is, that DBNull.Value isn′t actually null. Therefore I changed my code to:

long lValue = objReader["ParentID"] != DBNull.Value ? Convert.ToInt32(objReader["ParentID"]) : -1;

Is there a better way of doing this ? Can one use the ?? operator with a Datareader anyway?

Thanks for your help!

PS: sorry for my poor english.

Hi,

the ?? operator is used in conjunction with nullable types. The way you wrote it using the ?: operator is just fine.

Grz, Kris.

|||

Thanks a lot!

datareader wont read first line of DB

hi there, i have some code which cycles through my table in my DB and fills some textboxes. For some reason if there are 5 lines it will only get 4, yet strangly i used the code somewhere else for the same kind of thing and it reads all the lines, and anyone help?

int a = 0;int b = 0;

//This is the sql statement.

string sqlMultiL ="SELECT * FROM tbl_stock_part_multi_location WHERE stock_ID =" +Convert.ToInt32(Request.QueryString["qsStockID"]);// + ") AND EXISTS (SELECT * FROM tbl_stock_part_multi_location WHERE stock_ID =" + Convert.ToInt32(Request.QueryString["qsStockID"]) + ")";

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmdMultiL =new SqlCommand(sqlMultiL, myConnMultiL);

myConnMultiL.Open();

SqlDataReader drMultiL = sqlCmdMultiL.ExecuteReader();

//This reads the first result from the sqlReader

while (drMultiL.Read())

{

try

{

((TextBox)Panel2.FindControl("txtDesc_L" + a.ToString())).Text =Convert.ToString(drMultiL["description"]);

for (b = 1; b <= 5; b++)

{

((TextBox)Panel2.FindControl("txtQty" + b.ToString() +"_L" + a.ToString())).Text =Convert.ToString(drMultiL["qty" + b.ToString()]);

}// end of for b loop

}

catch (Exception exMultiL)

{

lblError.Text = lblError +Convert.ToString(exMultiL);

}

//increase the counter

a++;

// you can add a break if you only want 5 rows returned:

if (a >= 5)break;

}

also i dont know if anyone could answer this is if the value in my DB is not = to one in the DDL how can i handle that so it dosnt throw an error and cause my code to stop?

Thanks in advance

Jez

Change to

for (b = 0; b <= 5; b++) //You need to start from the 0

{

a++; //Here you missed

((TextBox)Panel2.FindControl("txtQty" + a.ToString() +"_L" + a.ToString())).Text =Convert.ToString(drMultiL["qty" + a.ToString()]); //Need to change to a too

}// end of for b loop

|||

hi gave that a go and still no luck :( i have tried changing it to 0 before by my controls start from 1 etc. As for changing the controls all to a.ToString() i think thats wrong because it would just do txtQty1_L1, txtQty2_L2 etc instead of going from 1 - 5 and then to the next L

Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

DataReader source output help

I have configured my DataReader to use an ADO.net (ODBC) connectivity (entered Select * from AMPFM) in Sqlcommand and can see my database columns listed in the Advanced Editor / Column mappings window. My process needs to perform a straight column to column population from AMPFM table into my dbo.visitfinancials table. How do I point the output to the above table?

Add an OLE DB destination. Select the DataReader source, connect its green arrow to the OLE DB destination. Double click on the OLE DB destination and configure it for the appropriate connection manager (will have to click "New..." to create a new one) and table name. Be sure to use "Table or view - fast load" in the Data access mode drop down list.

Then select mappings on the left. Map your columns by dragging one to the other. When done, click OK. Run it. If your data types match, this is as easy as it gets.|||

That worked great, thanks Phil. Now of course, you knew my data types would not all match. I am getting the error "Column "ID" cannot convert between unicode and non-unicode string data types. I believe I came across one of your posts that stated to use a derived column to cast the field. My field (ID) is varchar (254) not null. Where would I enter this derived casting procecure?

|||In a derived column, add a new column.

This expression should do the trick.

(DT_STR,254,1252)[your_input_field]|||

Looks like I have ran into a snag. I navigated to DataReader Source / show advanced editor / Input and Output properties / Datareader Output / Output columns / clicked on the add column radio button and received the following error message:

Error at Data Flow task[DataReader Source[1]]: The component does not allow adding columns to this input or output. Additional information: Pipeline component has returned HRESULT error code 0xC0208019 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

Any ideas?

|||You need to use Derived Column transformation for that...it it an item in the toolbox in the data flow.|||

OK, I have added a derived column object from the toolbox, connected it to the datareader and then tried to connect it to my OLEDB data source but get an error saying there are no available inputs. I deleted the mapping for "ID" field between DataReader source and destination OLEDB and then tried to connect "ID" from derived column to the OLE DB data source adn get the same message. I feel like I'm getting close but not quite there yet. I need additiona assistance please.

|||You have the wrong OLE DB component. You have a source on there and you need the OLE DB DESTINATION component instead. It's toward the bottom of the list in the toolbox.|||

I believe I used the wrong terminology in my previous posts and confused the issue. I do have a DataReader source, Derived column control and OLEDB destination. Thanks.

|||The data reader source should be connected to the Derived Column, which should be connected to the OLEDB destination. Three boxes, two lines.

Datareader Source connects to no longer existing ODBC-link

I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).

Hi Johndahl,

A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.

Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.

-David

|||

Hi David

Thanks for the reply.

The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.

Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...

Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.

Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob

Datareader Source connects to no longer existing ODBC-link

Hi Johndahl,

-David

|||

Hi David

Thanks for the reply.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob

DataReader Source can not configurate

when I configurate the datareader source using the ODBC connection manager. it show the follow error message:
"Error at Data Flow Task[DataReader Source [562]]: Cannot acquire a managed connection from the run-time connection manager"
this ODBC is connect to IBM DB2.

Can anyone help on this?

Frank,

see this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

Thanks.

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Best,

Michael Sh

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

Best,

Michael Sh

|||

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

Thanks
Mark

|||

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Best,

Michael Sh

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

Best,

Michael Sh

|||

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

Thanks
Mark

|||

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and Column Types

Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?

Darren,

I am afraid there is no way to influence this mapping. The Data Reader Source adapter uses only the CLR type (the DataType column from the table's schema) to determine which DT_... type to choose. The ProviderType field could not be used as it has different meaning for different providers.

The mapping files would definitely help here, but that infrastructure is not used by this component.

I do not have any good advice, but explicit data conversion to the DT_STR type or building your custom ADO .NET adapter are options I see available at this moment.

Thanks.

|||

Bob,

Thanks for confirming what I already suspected, but I had to ask. I'm thinking a MSDN feedback request for the ability to supply mapping files would be coming your way.

Conversion works, but I'm concerned about the impact of doubling the buffer size each time I do this. Most columns I am working with are DT_WTR, but need to be DT_STR, it is 2 x buffer every time. Is this really twice the size or is there some fancy pointer type work going on? I will probably test when I have time as I had some other ideas about custom components for such conversion, but it depends on what the impact really is.

A custom provider had been considered but currently rejected due to time. It took me long enough to get the managed provider written :)

Thanks

|||

Hi Darren,

I believe you are right about the buffer size. It might impact your package performance, but it is not sure how significant that could be. It may depend on many factors. If you get a chance to measure the impact in your configuration, please share results with us.

If the "power" stays with us, we should be able to provide much better story with managed providers in the next version.

Thanks.

|||

I have done some playing around with this.

For information my theory goes like this. If I use a Data Conversion transform I am increasing the number of columns in my buffer so I get less rows per buffer. This seems inefficient. On the other side we know that copying data between buffers has a cost. So lets test which is more efficient, the larger row size versus the cost of moving between buffers, and keeping a small row size.

I wrote a simple asynchronous component that allowed you to select columns from the input buffer which are then copied directly to the output buffer. The one feature is that any DT_WSTR column is reproduced as DT_STR. So the buffer sizes/structure are the same for input and output except for the change in type, and any associated overheads of each type. One would think that unicode types require twice the space of non-unicode, so this should make the asynchronous component test even faster as this allows even more rows to fit into the output buffer of my component.

For a baseline I used a Script Component -> Union All. The script component generated a variable number of rows, as determined by a package variable. The columns produced are 1 integer column (row count), and 9 x 50 character DT_WSTR columns fully populated.

For testing I used the same script component and two methods of converting the columns -

Script Component -> Data Conversion -> Union All

Script Component -> DeUnicodeAsynchTestComponent -> Union All

Tests showed that the data conversion was 1.5-2.5 times slower than the baseline. The asynchronous component was then 2-2.5 times slower than the data conversion. Times were averaged across 6 executions. The range in times are for different row counts, 100,000 to 1,000,000.

N.B. These ratios are for my local machine, and I would fully expect results to vary on different hardware and with different resource constraints. These are for illustration only. If you want to know how this equates to your environment, test it for yourself, and use real hardware, not a test system.

So, whilst it may not look pretty leaving the buffer alone is the way to go. Trying to remove columns or change columns is a non-starter as this means creating a new buffer, the cost of which far outweighs the benefit of the smaller row size in the buffer. When you do need to work on columns, use a synchronous component such as the Data Conversion or Derived Column transformations, and don’t worry if you end up with more columns that you will use at the end. (Obviously don’t create columns for the sake of it!)

DataReader returns different results when there are null fields

If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked.
The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.

// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();

SqlDataReader myReader = cmd.ExecuteReader();

if (myReader.Read()) {

thanks
Rodin your select or sproc, use ISNULL or COALESCE around the columns that have null values.

cs|||Thank you

I did that and now it works perferct

Rod

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 Tongue Tied |||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.