Put data directly into DataTable or dot net object ?

Question

Post reply

Put data directly into DataTable or dot net object ?

blasto_max

SSCertifiable

Points: 5025
More actions
December 14, 2013 at 1:49 pm

#302257

Currently, I just create an OLEDB connection, run a query on it and load the result set into a SSIS object. I don't know what this Object really is. After that, I load this Object into a DataTable in my C# code. Instead of doing all this, is there a way to directly load this result set into a data table ? Also, is there a way to get the number of rows in the result set from the object itself ? Right now, I load it into a data table and then get the number of rows as myDataTable.Rows.Count;
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What you've described so far is HOW you do your corrent task. But not, WHAT this task really is.

If you'd describe the task you're faced with, we might be able to help you finding an alternative.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

If you use OLE DB connection, the result object is ADO Recordset.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

blasto_max SSCertifiable Points: 5025 More actions · Answer 3

LutzM (12/15/2013)
What you've described so far is HOW you do your corrent task. But not, WHAT this task really is.
If you'd describe the task you're faced with, we might be able to help you finding an alternative.

Thanks. I need to iterate a result set, do some simple processing on it and then write that processed data to a file or even a database. So, I find it inefficient to first load a result set into an SSIS object and then load it into a DataTable for processing. why not load it into a DataTable in the first place ?

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

blasto_max (12/16/2013)
LutzM (12/15/2013)
What you've described so far is HOW you do your corrent task. But not, WHAT this task really is.
If you'd describe the task you're faced with, we might be able to help you finding an alternative.
Thanks. I need to iterate a result set, do some simple processing on it and then write that processed data to a file or even a database. So, I find it inefficient to first load a result set into an SSIS object and then load it into a DataTable for processing. why not load it into a DataTable in the first place ?

Loading into another in-memory object is not going to help too much. You are still going to have enormous memory consumption if you load millions of records .

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

LutzM SSC Guru Points: 107049 More actions · Answer 5

Why not doing all the data manipulation inside the database?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

blasto_max SSCertifiable Points: 5025 More actions · Answer 6

LutzM (12/16/2013)
Why not doing all the data manipulation inside the database?

What do you mean by that ?

LutzM SSC Guru Points: 107049 More actions · Answer 7

Based on your description

I need to iterate a result set, do some simple processing on it and then write that processed data to a file or even a database.

I'm wondering if it wouldn't be easier to load the data directly into the database and call a stored procedure to do "some simple processing".

It depends on what that "simple processing" actually is...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

blasto_max SSCertifiable Points: 5025 More actions · Answer 8

LutzM (12/16/2013)
Based on your description
I need to iterate a result set, do some simple processing on it and then write that processed data to a file or even a database.
I'm wondering if it wouldn't be easier to load the data directly into the database and call a stored procedure to do "some simple processing".
It depends on what that "simple processing" actually is...

Actually, I have to extract data, change it in C# script and then prepare batch INSERT statements in the C# code. After that, I have to execute these insert statements on a target database.

For now, I am assuming that I have to use only SSIS and not SQL server side code to do the job.