need faster data transfer :: oledb

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are way to many reasons for it to be slow. It could be your server, their server, network, etc.

The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.

When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are so many "it depends" answers to this that its hardly worth replying.

There is a webcast on performance tuning that you may wish to watch:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

And info here about a performance tuning whitepaper:

Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)

-Jamie

|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.

Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Are there indexes in place on the destination tables?

Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?

Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?

Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?

=================

The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)

Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)

Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.

Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)

*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Only you can answer those questions. Test and measure is my advice to you.

There is some useful performance tuning info in the whitepaper that I linked to earlier.

You might also want to look at using the SQL Server Destination if at all possible.

-Jamie

|||

<<after filtering ,i.e which need to be transfered>>

This is where I would look first. That and network lag.

|||

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

|||

samssb wrote:

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.

need faster data transfer :: oledb

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are way to many reasons for it to be slow. It could be your server, their server, network, etc.

The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.

When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are so many "it depends" answers to this that its hardly worth replying.

There is a webcast on performance tuning that you may wish to watch:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

And info here about a performance tuning whitepaper:

Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)

-Jamie

|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.

Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Are there indexes in place on the destination tables?

Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?

Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?

Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?

=================

The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)

Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)

Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.

Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)

*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Only you can answer those questions. Test and measure is my advice to you.

There is some useful performance tuning info in the whitepaper that I linked to earlier.

You might also want to look at using the SQL Server Destination if at all possible.

-Jamie

|||

<<after filtering ,i.e which need to be transfered>>

This is where I would look first. That and network lag.

|||

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

|||

samssb wrote:

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.sql

Need call a DB function in the middle of the dataflow process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.

If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.

The data flow is Like:

SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB

Thanks in Advance

Jessie

HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:

EXEC ? = DB_Function(?)

Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.

HTH,
Ccote

|||

Hi, Ccote,

Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.

before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.

In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.

The OLE command does not let me to add any new column to the OLE command output.

I got error when I click on REFRESH, “Invalid parameter number ‘

Where I’m missing here?

Thanks

Jessie

|||

I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?

Thanks

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. 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.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. 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.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.