SSIS Data Flow task - csv source using file name as destination table name

Mark Mills 1 Reputation point
2022-06-09T15:32:30.643+00:00

Hello,

I am a beginner to SQL Server and creating my first SSIS task and have a requirement to read one or more csv files, capture the file name of each, and create a table with the same name of each file. By base file name I mean, for example, my_data_20220609101253.csv and create a data flow task to create a table named dbo.my_data20220609101253. The csv file name will change on a weekly basis and I need to be able to run my SSIS package to use the source (csv) file name as the destination table name (less the .csv file extension). So far, I have created a .dtsx file that I created in SSMS and loaded into Visual Studio (integration service) but it creates a table with a hard-coded (static) source file name and hard-coded (static) destination table name. There could be multiple files in the source location but I need the script to pick the one(s) beginning with the pattern expression matching my_data_YYYYMMDDhhmmss. If there happens to be more than one source file matching this pattern then I need to loop through the source directory creating a matching named table for each file. There could also be other files in the source location that do not match the pattern and these should be skipped. The file or files to process should match this pattern expression my_data_YYYYMMDDhhmmss. Examples below.

  • SOURCE: my_data_20220609101253.csv DESTINATION: dbo.my_data_20220609101253
  • SOURCE: my_data_20220616073529.csv DESTINATION: dbo.my_data_20220616073529

Thank you in advance!
Mark

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,453 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2022-06-10T06:05:41.88+00:00

    I am a beginner to SQL Server and creating my first SSIS task and have a requirement to read one or more csv files, capture the file name of each, and create a table with the same name of each file

    That's more then a difficult task, even for a pro.
    Even to start, how to "guess" the data type for the columns?
    I don't think SSIS is the rigth product to solve it, a pure programmable solution like .NET would fit better.

    0 comments
  2. ZoeHui-MSFT 32,821 Reputation points
    2022-06-10T08:28:22.767+00:00

    Hi @Mark Mills ,

    I think it is really hard to meet your requirement via SSIS.

    As far as I know, you can load the file name to the table column with FilenameColumnname property.

    As Olaf said, if you are good at C# or VB code, you may consider to use script to see if it could meet your need.

    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