One dataflow for sevral Oracle Instances ?

Hi,

I designed a dataflow which extracts, transforms and load (the main goals of an ETL ....) data from an Oracle db to an Sql Server 2005 db. The issue is that I would like to run this dataflow on several Oracle instances in a row. This intances are not on the same Oracle Servers. Is there a way to set only one data source for all these intances and to use a loop to execute the dataflow for each?.

Regards

Ayzan

You could place the Data Flow inside a For Each Loop. Select the enumerator best suited to the list of servers you have, and obviously pass this out through a variable(s) so that you can use the specifics in an expression on the Oracle connection.|||

Ok, that's what I tried to do. But how do you pass variables to the Data Source. by editing the XML source ? Do you have any example ?

Regards

Ayzan

|||

You need to pass the current loop value through to the connection, which is used inside the loop. Store it in a variable, then use an expression on the connection string property of the connection, used by the source component in the data flow. This article demonstrates some of the concepts:

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||Right click on your Connection Manager and then Properties. Under the Expressions property click on the Ellipses [...].

In the Property Expressions Editor choose ConnectionString for the

property and then click on the Ellipses [...] next to the expression.

In the expression builder your expression will be similiar to the follwing

"Data Source=" + @.[User::InstanceName] + ";User

ID=MyUser;Provider=OraOLEDB.Oracle.1;Persist Security

Info=True;Password=MyPassword;"

You just need to replace the parts of the connection string that are

applicable to your environment (Instance Variable Name, UserID,

Provider, Password).

This assumes you are using the tnsnames.ora to declare the instances.

Larry Pope|||

Thank you all, that works really fine.

Regards

Ayzan

One dataflow for sevral Oracle Instances ?

Hi,

I designed a dataflow which extracts, transforms and load (the main goals of an ETL ....) data from an Oracle db to an Sql Server 2005 db. The issue is that I would like to run this dataflow on several Oracle instances in a row. This intances are not on the same Oracle Servers. Is there a way to set only one data source for all these intances and to use a loop to execute the dataflow for each?.

Regards

Ayzan

You could place the Data Flow inside a For Each Loop. Select the enumerator best suited to the list of servers you have, and obviously pass this out through a variable(s) so that you can use the specifics in an expression on the Oracle connection.|||

Ok, that's what I tried to do. But how do you pass variables to the Data Source. by editing the XML source ? Do you have any example ?

Regards

Ayzan

|||

You need to pass the current loop value through to the connection, which is used inside the loop. Store it in a variable, then use an expression on the connection string property of the connection, used by the source component in the data flow. This article demonstrates some of the concepts:

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||Right click on your Connection Manager and then Properties. Under the Expressions property click on the Ellipses [...].
In the Property Expressions Editor choose ConnectionString for the property and then click on the Ellipses [...] next to the expression.
In the expression builder your expression will be similiar to the follwing
"Data Source=" + @.[User::InstanceName] + ";User ID=MyUser;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Password=MyPassword;"
You just need to replace the parts of the connection string that are applicable to your environment (Instance Variable Name, UserID, Provider, Password).
This assumes you are using the tnsnames.ora to declare the instances.
Larry Pope
|||

Thank you all, that works really fine.

Regards

Ayzan

OLEDBcommand is too slow

Hello,

I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

Is there an alternative ?

it's not the component's fault it is slow. SQL faster at set based operations.

You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.|||

Thanks!

I've tested this solution, but it seems to persisted a certains slowness.

My dataflow uses these components:

OLEDB Source --> Lookup --

|||As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

Is the final SQL update correctly indexed?

Listing two components in your data flow and saying they slow is the vaguest statement you could say.

Many reasons, more possible solutions.sql

OLEDB AS400 Pipeline threads

A simple dataflow :

Data source OLEDB AS400 : - data access sql command from variable

SELECT 'DEN' AS "Company","TDEN.IHD".* FROM "TDEN.IHD" WHERE (ORDNI1 > 48960)

Data destination : OLEDB Sql Server File

- No Problem when Data Source is executed As Sql command

Purpose :

Load (new) data from AS400-files and load them in the corresponding sql server table. Therefore each day the maximum ordernumber in the sql database is searched And given to a script which makes an sql command user::SqlSelect.

All This this to prevent loading each day all records again.

I choose to make the data access by an variable because I have more then one company with the same formatted data files. e.g.

Company XXX has a file named FXXX.AAA

Company YYY " FYYY.AAA and so on ....

All data for all companies has to be imported in one sql server tabel. Ofcourse with a company field.

- ODBC Does not allow sql command from variable

Errors
[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. 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.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. 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.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Is there anyone who had the same problems ?

By the way the sql command from variable is double checked a thousand times :)

Thanks

Ronny

0x80040E00 is not an SSIS error code. Instead it is most likely coming from the provider so you should see if the AS400 OLEDB provider you are using lists this error and what it means.

Matt

|||

Hello Matt,

This could be , but why was it then working under Sql server 2000 ? Also the ODBC connections in DTS200 were far more ?ntelligent".

regards

Ronny

OLE Db Destination task in ASYNC_NETWORK_IO wait state

I have established an SSIS dataflow that should move 20,000 records from a source table to a target table. Only new records should be added to the target table – existing records should be ignored. The problem I am reporting occurs when the target table is initially empty (there are no existing records, so everything should come over). I am using the Slowly Changing Dimension task to limit inserts to new records. The tasks in my data flow are:

OLE DB Source è Slowly Changing Dimension è Derived Column è OLE DB Destination

The problem is that the data flow locks up before completing. This occurs if the Data Access Mode of the OLE DB Destination task is “Table or view – fast load”. If I look at the Activity Monitor in SQL Server Management Studio once it has locked up, I can see two processes in the suspended state. Based on the queries, I can identify one of the processes as performing the lookup for the Slowly Changing Dimension task. The other represents the BULK INSERT associated with the OLE DB Destination task.

The Slowly Changing Dimension task is locked waiting for the OLE DB Destination task as determined by looking at the “Blocked By” column in the Activity Monitor. Its wait state is LCK_M_S. The OLE DB Destination task has a wait type of ASYNC_NETWORK_IO.

QUESTIONS: Why is the BULK INSERT in the OLE DB Destination task waiting? What does ASYNC_NETWORK_IO wait state indicate? How do I prevent this from happening?

I am running the SSIS package in SQL Business Intelligence Studio on a workstation against a SQL Server 2005 server. The same situation is seen when I run the package directly on the SQL Server 2005 systems in SQL Business Intelligence Studio rather than on my workstation.

Turn off the table lock option for the OLE-DB Destination. Leaving this on means that the second buffer of data is blocked, specifically the lookup, as when the first passed to the destination the lock was acquired, and now prevents any more lookups happening.

You could also investigate adding a NOLOCK hit to the lookup SQL statement.

|||I had already tried turning off the table lock option, though I failed to mention it. Adding the NOLOCK option onto the query within the Slowly Changing Dimension task did indeed allow the data flow to complete.

Thank you.

OLE DB DESTINATION and SQL Server Destination

Hey All:

I was totally confused.

When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.

then execute the task with failure.

Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.

This Dataflow worked.

i can not figour out why.

By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.

Who can tell me some reasons for this?

Have you searched Books On-Line?

The SQL Server destination requires that you have SQL Server running on the same machine that you are executing the package on. Also, just to have SQL Server running on the machine isn't enough; it has to be your destination. The SQL Server destination is an in-memory operation, essentially.

The OLE DB Destination isn't bound to those constraints.

|||

Using the OLE-DB destination is fine, and is probably the most common choice. Obviously you will then use an OLE-DB connection, selecting the OLE-DB provider for SQL Server. That is all good, don't worry.

You have however and advanced choice with the SQL Server Destination. It has options that make it the faster than OLE-DB, and one way they do this is by using the shared memory connection method. As you might guess from the name it means that the SSIS package pipeline and the SQL Server must be on the same machine. This makes for hard work when you wish to develop against a server running on a different machine to the development tools. For this reason I generally avoid it, unless I am really concerned with insert performance, and more often than not the bottle neck is elsewhere so the SQL Server Destination is over kill anyway.

|||

Many thanks

But the new issue is that dam slow~~

60,000 rows from a static table which server loactes in Germany to the US server costs over 30 minutes.

even worse than <select .. openquery()>

why?

|||

Solved!

i used a txt flatfile as a buffer intermedia.

|||If the "buffer" is between two SSIS tasks or packages, then use a raw file. Raw files are faster to read and write than txt files, as they are the pipeline engine's buffer structures from memory straight onto disk, without any translation or interpretation. See teh Raw File Source & Destination.|||

DarrenSQLIS :

Rock~

OLE DB Command and Stored Procedure that returns value and/or error

Guys,

could someone please tell me : am I supposed to use the OLE DB

Command in a dataflow to call a stored procedure to return a value? Or

is it just supposed to be used to call a straightforward insert

statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a

dataflow I would like to effect an insert of a record to this table and

retrieve the identity value of the inserted record... and I'd like to

store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by

clicking on Add Column) I just get an error dialog that says "the

component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?

Thanks

PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

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

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

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

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ