question

BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 asked Monalv-msft edited

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

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.





sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @BobbyP-1695 ,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.