Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Put data directly into DataTable or dot net object ? Expand / Collapse
Author
Message
Posted Saturday, December 14, 2013 1:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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.








Post #1522969
Posted Sunday, December 15, 2013 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 7,147, Visits: 13,213
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1523027
Posted Sunday, December 15, 2013 3:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
If you use OLE DB connection, the result object is ADO Recordset.

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

Post #1523068
Posted Monday, December 16, 2013 10:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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 ?

Post #1523340
Posted Monday, December 16, 2013 10:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
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/

Post #1523343
Posted Monday, December 16, 2013 11:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 7,147, Visits: 13,213
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1523376
Posted Monday, December 16, 2013 11:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
LutzM (12/16/2013)
Why not doing all the data manipulation inside the database?


What do you mean by that ?
Post #1523377
Posted Monday, December 16, 2013 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 7,147, Visits: 13,213
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1523381
Posted Monday, December 16, 2013 12:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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.
Post #1523398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse