Saving the Full Resultset of Execute Sql Task directly into Sql Server 2005 table

Hi friends,

I couldn't find links for this issue.

1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.

2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.

How can we achieve this?

Thanks

Subhash Subramanyam

Seems like it would be easiest to do it within SQL Server instead of SSIS. Probably by using a SELECT INTO statement in your Execute SQL Task. If you needed the resultset in SSIS, you could read it from your newly created table.
|||

Thanks for your reply JayH. I think you did not get my entire scenario.

1) I am using Oracle Source and Sql Server Destination

2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable

3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Thanks for your reply JayH. I think you did not get my entire scenario.

1) I am using Oracle Source and Sql Server Destination

2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable

3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.

Thanks

Subhash Subramanyam

In that case you'll have to do it all in a script task. SSIS does not provide any mechanism to do what you want, and I don't think ADO.NET does either, though this is really more of an ADO.NET programming question now than an SSIS question.

I think you'll need to iterate through your columns to create a CREATE TABLE statement to create the table. Then you can iterate through the rows to create INSERT INTO statements. All of which would be executed inside the script.
sql