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
