Share via

Dynamic Power Query Source (Excel File) to SQL Table with Type Conversion

William Zorn 1 Reputation point
Mar 8, 2022, 3:04 PM

I am working with the Power Query Source module and I have a large number of Excel files (all different) that I need to process and import into a SQL database. Part of the requirement is that the number of excel files can change and the data in each of the excel files may change.

All of the information needed to process each excel file will be stored in a SQL table (ie: file path, Power Query m-query, destination table). I've come up with a process that loops through each excel file, but I get errors because I'm using a single Data Flow for all files. The first problem is that the metadata doesn't match when a different Excel file is used. The second issue is the data types. I have to manually set specific data types using the Advanced Editor for each of the outputs on the Power Query Source module in order for the data to be loaded into a SQL table.

Can this be automated this way?

Control Flow

180988-capture1.jpg

Data Flow

181057-capture2.jpg

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,619 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
40,601 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 38,951 Reputation points
    Mar 9, 2022, 8:29 AM

    Hi @William Zorn ,

    I'm afraid that it is hard to meet your requirement.

    Suppose that each file has different schema with consistent column headers, data types, and number of columns.

    So that you need to manually set specific data types.

    I have not find a way to do that automatically till now.

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments No comments

  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.