DataSet table as DataFlow Destination Component

Is it possible to use a datatable as destination component in dataflow ?

Sample code Fr MS:
Create the source component.
IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();

destination.ComponentClassID = WHAT ? for dataset Table ?

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

thanks in advance
PatrickI saw that the datareader can be used as Destination.
So, my "ask for" is closing.
Thanks
Patricksql

Datareader Destination as source for other datareader source ?

HI!

as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?

how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?

Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?

THANKS, HANNES

Hannes,

You would need to build an ADO.Net provider for SSIS packages in order to do this. We have built one at Conchango but I think my boss is reluctant for us to just give it away.

-Jamie

|||

Hi Jamie!

I could understand your boss..

I would expect there is some integrated way because it is essential to get data from one dataflow to an other dataflow.

Do you know any othere method (without temp tables) to move data from one dataflow to to next?

I do not understand the difference between datareader and oledb source/dest? is there reader only for .NET Programming, oder is there a special purpose for this objects i do not realize (it seems to be the same escape for integration with other .NET data consumer which could read the Datareader...)

THANKS, HANNES

|||

hannes,

I have just realised that I completely misunderstood your first post. I apologise for that.

The answer I gave previously applies if you want to pass data from one PACKAGE to another. I realise now that what you want to do is pass data from DATA-FLOW to another. You have 2 options for doing that. Raw files or ADO recordsets.

I compare the two here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx and its pretty conclusive about which you should use.

Again, my apologies for misreading your initial post!

-Jamie

P.S. Hannes, are you still at MISAG?

|||

That is it, but what I cannot understand why there is no in memory way.

THANKS

PS: No! - AVENESS - see signature for further details (for the moment only in german)

|||

There is - the ADO recordset destination provides this functionality. Admittedly you have to write code to access it again in a script source component but this is because the metadata of the recordset is not (and cannot be) persisted in the Object variable at design-time.

-Jamie

DataReader Destination Adapter Error!!!

Hi, Buddies,

I have a very simple package in SSIS using a datareader destination as the output result. But when I ran the package, it always timeout and shows: "write 0 rows to the datareader". Why this happened, Can anyone help?

Thank you very much!

AlbertWhat happens if you increase the value of ReadTimeOut (it's a custom property of Data Reader Dest adapter)?

What does your package look like? Can you share it?|||How do you execute the package?

The goal of DataReader Destination is to let you use the result of executing data flow in other applications via DtsClient ADO.NET provider.

So the way you use it is by configuring this "other" application (e.g. Reporting Services or Analysis Services, or other app that supports ADO.NET data source) to use DtsClient ADO.NET provider, configure the provider to execute the particular package and get data from specific DataReader Destination, then run this application. When the application starts to read data from its data source, we instantiate the package and stread data from DataReader Destination to the client application.

If the package containing DataReader Destination is executed stand-alone, without the client reading from DataReader Destination, you get the message "component "DataReaderDest" (NN)" wrote 0 rows".

SQL Books Online contain more detailed infromation and instructions on setting this up.

DataPump performance problem

I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component:

DataSource = OLEDB Source;

Destination = OLEDB Destination

The source is a View returning about 100000 records; if i execute it in query analyser it takes few seconds. But if i use it as DataPump Source it takes HOURS to execute it...

I've noticed that If i modify the SELECT query inserting a "TOP 10000000000", it takes few seconds..

Why? Am i missing something?

Could I ask what would be wrong with just always using Select TOP 100% in your query? If adding the TOP 100% explicitly speeds things up that much, then that is what I would do.|||

Netoblivion wrote:

I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component:

DataSource = OLEDB Source;

Destination = OLEDB Destination

The source is a View returning about 100000 records; if i execute it in query analyser it takes few seconds. But if i use it as DataPump Source it takes HOURS to execute it...

I've noticed that If i modify the SELECT query inserting a "TOP 10000000000", it takes few seconds..

Why? Am i missing something?

Read this article:

SELECT *... or select from a dropdown in an OLE DB Source component?
(http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx)

as this could well be the problem.

Let us know!!

Thanks

-Jamie

|||Thanks Jamie that really helped me to solve this problem!!!

dataflow to excel - Convert numbers stored as text to numbers Excel Cell Error

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:\@.; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.

So close.. Thanks for any help or information.

You received some sugestion on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1392693&SiteID=1

Opening new threads for same problem makes more dificult to help you.

Dataflow slows dramatically down after passing 1.6 million records ?

If i have a dataflow witch moves records from one OLE DB Source to a OLE DB Destination, the step slow down when getting above passing 1.5 - 1.6 million records.The Source is a SQL 2000 and the Destination is SQL Server 2005. the Package is run from a laptop with 1.5 GB ram

What can i try to help it ?Check the perf counters to see if the package starts swapping when the slowdown occurs. If this is the case then there is not much to be done. You could try adding more memory or making the destination run faster (i.e. drop indicies, faster spindle, etc). Is the destination on the same PC as the package is running? If so then they are competing for memory so you could run the package on a different PC. Also, the next CTP has a minor change that you may find helps with this.

Thanks,|||Start by checking in Task Manager how much memory the package (i.e. dtexec.exe) uses - does it grow all the time?
And when it slows down, are you out of memory + swapping starts + CPU usage goes down?
If this is the case - do you expect memory usage to grow like this, given your data flow? Which component could be gobbling up the memory? Can you change its settings to improve the situation? Or change the data flow to not use the troublesome component?
Or is there a memory leak that creates a problem?
If the bottleneck isn't memory at all, you obviously have to look in other places...

Cheers,
KDog|||

Matt David wrote:

Check the perf counters to see if the package starts swapping when the slowdown occurs.

How do you check the perf counters?
Regards,
Henk|||Use the Windows Performance Monitor (Administrative Tools-->Performance). In there, there is an IS object containing various counters that you can use to monitor performance.

There is a help file provided that explains how to use Windows Performance Monitor.

-Jamie

dataflow error

This error seem to be very silly.did anyone come across this error.

I have been transferring data from textfile to a table using oledb destination.

The number of records in the text file are 2,091,650

Its was running just fine couple of days ago when the incoming data was little small then this...(arround 300,000).Now it seem to have a problem.

Here is the flow

1.File System task ->I copy the file to different location

2.Execute sql task->truncate tables

3.DataFlow task->I check for only the error files in this data flow.and all valid rows i transfer to a different text file.

4.Dataflow->filesource i connect to new text file created earlier.Here i convert fields to repective datatype and i insert if new or update record.

I dont know whats going on...

When i run my package it runs through the first three perfectly fine.When it comes to fourth step it sits there.....it dosent go to the tasks within this dataflow at all...and begining i have flat files source...

What could be the reason...

when i look at progress tab...i was able to look at the progress of other tasks but when it comes to this task it shows start>>>>>time and it sits there...

Are you using the same file connection manager for the "new" file created in step 3 and used in step 4? If so, try creating a second file connection manager for the file and use that connection manager in step 4.|||Do you have step three hooked up to step four via a precedence constraint?|||

I checked it out using a same file connection and different connection manager as well...but it didnt work...seems to really silly problem....

And all my flows are perfectly connected with precedence constraint as well.

Then only change which was made to this package was increase in volumn of data.

How can i fix this?Is there any other solution?

I also checked executing the last dataflow task alone as the file had data in it.

But it sits there and dosent proceed.

|||

Suresh,

could you troubleshoot this by making a simple package to only process your flat file? If it fails, try cutting the file in pieces to find out if it has some formatting errors.

HTH.