Newbie looking for direction

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

Without rewriting the stored procedure to do batch processing, you can use an OLE DB Command transformation in your data flow to execute that stored procedure.|||

kbutterly wrote:

Happy Friday afternoon, all,

My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.

I have been only working with SSIS for a bit, so please bear with me.

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.

I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.

Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?

Thanks and have a great weekend,

Kathryn

You can execute SQL code (i.e. stored procedures) from the pipeline using the OLE DB Command component.

Try that first and see how you get on with it.

-Jamie

|||

kbutterly wrote:

I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.

You could load data to a staging table - and run the Stored procedure on that

IMHO - OLE DB Command task performs too slow - so SQL based solution might be best bet performance wise|||

ViewMaster,

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

Thanks for helping a newbie,

Kathryn

|||

Jamie and Phil,

thanks for pointing out the OLE DB Command. I will look into it.

Kathryn

|||

See if this helps:

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

|||

kbutterly wrote:

The approach you suggest is the logical way to do it, but can it all be done through transaction services? I mean can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?

What is "transaction services"?

|||

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

|||

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB. My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

|||

kbutterly wrote:

Jamie,

'Transaction services' is my brain's translation of integration services... ;-) Sorry!

Kathryn

Oh OK. Well in answer to your question "can I query the Excel table, put the results in a temporary table, and run the stored procedure on the table, all from with transaction services?", the answer is "Yes, absolutely".

-Jamie

|||

kbutterly wrote:

Good morning,

OK, I have looked into the OLE DB command and it looks like exactly what I need. We aren't loading much data, maybe 500 to 1500 rows, so the speed or lack of it, isn't an issue.

The OLE DB command needs a connection manager for input that is of type OLEDB.

not true. The input is whatever is in the pipeline. The OLE DB Connection Manager that you define is whatever relational db you are going to execute the SQLagainst.

kbutterly wrote:

My data is coming in through Excel, so the type is EXCEL. Sorry to be so dense, but how do I transform the Excel to a format the OLE DB Command can use?

Use an Excel Source Adapter.

kbutterly wrote:

Visually, on my Data Flow tab, I have a data flow component named 'Source-Query'. That contains my SQL command to get the data out of the Excel workbook. I have tried to directly connect that data flow component to the OLE DB command, but I get the error that the type is incorrect. I have to do some kind of transformation, but I don't know what. Any help would be greatly appreciated.

Sorry to be such a bother, but I can't find any documentation or tutorials for newbies that are anything close to what I am trying to do. If you have such a reference, that would be great.

Thanks,

Kathryn

I don't know of any tutorial buts there's got to be something out there somewhere. Google turned up these:

http://www.developer.com/db/article.php/10920_3497511_2

http://msdn2.microsoft.com/en-us/library/ms141138.aspx

-Jamie