Import Excel Spreadsheets(plural) using Data Touch Points to prime a SQL Server Stored Procedure to INSERT

Question

So I have to load multiple Excel spreadsheets to our Database. It's actually Customer Contact information...Emails...Texts...etc.. Each file layout being different.

• So we'll start with a Foreach Loop Container to process each file in its source File Folder
• Then I'm assuming we'd then use a Data Flow Task to process through the Foreach Loop Container individual file's data touch points
• Within the Data Flow Task, again I'm assuming, do we then utilize an OLE DB Source to identify the Foreach Loop Container individual file? And if so, how do I do that?
• Looks like we'll probably have to use a Derived Column Task to massage some of the data touch point textual data as to how they want to store it based on spec
• And then finally use a OLE DB Destination with T-SQL that will execute the SQL Server Stored Procedure which will INSERT the data rows to our application. I believe our 3rd Party Application provides the INSERT SQL Server Stored Procedure so we will have to use some of the data touch points from the Excel spreadsheet as well as the Derived Column as parameters to the OLE DB Destination and SQL Server Stored Procedure

I apologize...just trying to talk this through.

Has anyone done this in the past?

I'm just a little fuzzy on the front-end...the Foreach Loop Container and utilizing each file's data touch points to the back-end which I'm a little fuzzy on as well...the OLE DB Destination and utilizing a SQL Server Stored Procedure utilizing the specific Excel file's data touch points as parameter inputs to the 3rd Party Application's INSERT SQL Server Stored Procedure.

Any help you could provide would be GREATLY appreciated...referenceable web sites...perhaps a YouTube

Thanks for your review and am hopeful for a reply.

Answer 1

Hi @Bobby P ,

So I have to load multiple Excel spreadsheets to our Database. It's actually Customer Contact information...Emails...Texts...etc.. Each file layout being different.

If the structures of excel spreadsheets are different.
We should create many Excel Sources and OLEDB Destinations in Data Flow Task.
Please refer to Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS).

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.