{"id":12999,"date":"2023-07-04T05:34:00","date_gmt":"2023-07-04T03:34:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=12999"},"modified":"2024-03-04T15:19:05","modified_gmt":"2024-03-04T14:19:05","slug":"import-multiple-text-files-with-ssis","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/","title":{"rendered":"How to Import Multiple Text Files into SQL Server with SSIS?"},"content":{"rendered":"\n<h4 class=\"wp-block-heading has-text-align-left\" id=\"h-import-multiple-text-files-csv-or-txt-into-a-sql-server-table-using-an-ssis-package-and-a-dynamic-loop-called-the-for-each-loop\"><em>Import multiple text files &#8211; csv or txt &#8211; into a SQL Server table using an SSIS package and a dynamic loop called the for each loop.<\/em><\/h4>\n\n\n\n<p>This tutorial explains how to import multiple text files into SQL Server with SSIS using a single package and a dynamic loop to detect flat files. To start with, all text files (.txt, .csv, etc\u2026) must be in the same folder. This automatic integration uses dynamic variables and allows to integrate many files with the same structure at the same time. To load several flat files dynamically, use an SSIS loop, i.e. the SSIS <em>Foreach Loop Container<\/em> component.<\/p>\n\n\n\n<p>This package uses the same components as the previous <a href=\"https:\/\/expert-only.com\/en\/ssis\/import-csv-file-into-sql-server-with-ssis\/\"><strong>SSIS tutorial to import a single flat file into a SQL table<\/strong><\/a>. It is therefore the same data flow used, but this time with variables in the dynamic file path.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-1-download-text-files-to-import-using-ssis-and-create-a-table-premium\" >Step 1: Download text files to import using SSIS and create a table (Premium)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-2-create-the-file-loop-and-the-ssis-data-flow\" >Step 2: Create the file loop and the SSIS data flow<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-3-create-the-ssis-dynamic-variable-to-store-the-file-path\" >Step 3: Create the SSIS dynamic variable to store the file path<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-4-configure-the-ssis-foreach-file-loop-container\" >Step 4: Configure the SSIS Foreach File Loop container<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-5-add-the-file-name-to-import-using-ssis-derived-column\" >Step 5: Add the file name to import using SSIS derived column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#step-6-run-the-ssis-package-to-import-the-multiple-text-files-with-ssis-and-check-data-in-ssms\" >Step 6. Run the SSIS package to import the multiple text files with SSIS and check data in SSMS<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-1-download-text-files-to-import-using-ssis-and-create-a-table-premium\"><\/span>Step 1: Download text files to import using SSIS and create a table (Premium)<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p><strong>First, to access our resources, to support us and much more, subscribe to our SSIS Full training available on Udemy<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-uagb-buttons uagb-buttons__outer-wrap uagb-btn__default-btn uagb-btn-tablet__default-btn uagb-btn-mobile__default-btn uagb-block-bb3f1488\"><div class=\"uagb-buttons__wrap uagb-buttons-layout-wrap\">\n<div class=\"wp-block-uagb-buttons-child uagb-buttons__outer-wrap uagb-block-a328f467 wp-block-button\"><div class=\"uagb-button__wrapper\"><a class=\"uagb-buttons-repeater wp-block-button__link\" aria-label=\"\" href=\"https:\/\/www.udemy.com\/course\/data-integration-with-ssis-sql-server-integration-services\/?couponCode=EO-SSIS-24-BEA561FDF\" rel=\"follow noopener\" target=\"_blank\"><span class=\"uagb-button__icon uagb-button__icon-position-before\"><svg xmlns=\"https:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 448 512\"><path d=\"M438.6 278.6l-160 160C272.4 444.9 264.2 448 256 448s-16.38-3.125-22.62-9.375c-12.5-12.5-12.5-32.75 0-45.25L338.8 288H32C14.33 288 .0016 273.7 .0016 256S14.33 224 32 224h306.8l-105.4-105.4c-12.5-12.5-12.5-32.75 0-45.25s32.75-12.5 45.25 0l160 160C451.1 245.9 451.1 266.1 438.6 278.6z\"><\/path><\/svg><\/span><div class=\"uagb-button__link\"><strong><em>PREMIUM content: Enrol for the Full SSIS training in video<\/em><\/strong><\/div><\/a><\/div><\/div>\n<\/div><\/div>\n\n\n\n<p>The files are stored like this:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>C:\\data<\/strong>, contains these CSV files:\n<ul class=\"wp-block-list\">\n<li>Customers_Data_1.csv<\/li>\n\n\n\n<li>Customers_Data_2.csv<\/li>\n\n\n\n<li>Customers_Data_3.csv<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p><strong>Then create the destination table. <\/strong>The table uses the same structure as the tutorial for loading a flat file and fills a column with the full path to the source file.<\/p>\n\n\n\n<p><strong>Then, to access target table creation code, enroll in our SSIS Full training available on Udemy<\/strong>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/* \n\nThen, to access target table creation code, enrol in our SSIS Full training available on Udemy.\n\n*\/ <\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-2-create-the-file-loop-and-the-ssis-data-flow\"><\/span>Step 2: Create the file loop and the SSIS data flow<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First, use a loop from the control flow tab. Then select the dynamic Foreach Loop Container and drag it onto the workspace.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"840\" height=\"580\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-1-for-each-file-container.jpg\" alt=\"Use the SSIS Foreach Loop container and add the Data flow task to import multiple text files into a table with SSIS\" class=\"wp-image-11920\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-1-for-each-file-container.jpg 840w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-1-for-each-file-container-300x207.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-1-for-each-file-container-768x530.jpg 768w\" sizes=\"auto, (max-width: 840px) 100vw, 840px\" \/><figcaption class=\"wp-element-caption\">Use the SSIS Foreach Loop container and add the Data flow task<\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-3-create-the-ssis-dynamic-variable-to-store-the-file-path\"><\/span>Step 3: Create the SSIS dynamic variable to store the file path<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Create a variable named <em>FlatFilePath<\/em> of type String at the package level. To display the variables, click on the package and press <strong>F4<\/strong> or <strong>right-click in the data flow area<\/strong>. Here the variable is called <strong><em>FlatFilePath<\/em><\/strong> and the Data Type is <em><strong>String<\/strong><\/em>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"884\" height=\"290\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-2-create-path-variable.jpg\" alt=\"Create the FlatFilePath SSIS variable to store text file names dynamically\" class=\"wp-image-11925\" style=\"width:884px;height:290px\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-2-create-path-variable.jpg 884w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-2-create-path-variable-300x98.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-2-create-path-variable-768x252.jpg 768w\" sizes=\"auto, (max-width: 884px) 100vw, 884px\" \/><figcaption class=\"wp-element-caption\"><em>Create the FlatFilePath SSIS variable to store text file names dynamically<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>Set up the flat file connection as detailed in this tutorial with a data flow to load a flat file. It is the same principle but this time the dataflow allows to load several text files with a single SSIS package. Then modify the <strong><em>ConnectionString<\/em><\/strong> expression to use the dynamic variable instead of the hard path.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"704\" height=\"847\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-3-connection-variables.jpg\" alt=\"Modify the properties of the connection to the text file to import in the SSIS loop\" class=\"wp-image-11929\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-3-connection-variables.jpg 704w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-3-connection-variables-249x300.jpg 249w\" sizes=\"auto, (max-width: 704px) 100vw, 704px\" \/><figcaption class=\"wp-element-caption\">Modify the properties of the connection to the flat file<\/figcaption><\/figure><\/div>\n\n\n<p>Use the variable created before so that at each passage in the loop, the file name is dynamic.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"647\" height=\"529\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-4-map-connectionstring.jpg\" alt=\"Map the FlatFilePath SSIS variable to the ConnectionString property in the text files properties\" class=\"wp-image-11933\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-4-map-connectionstring.jpg 647w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-4-map-connectionstring-300x245.jpg 300w\" sizes=\"auto, (max-width: 647px) 100vw, 647px\" \/><figcaption class=\"wp-element-caption\">Map the FlatFilePath variable to the ConnectionString property<\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-4-configure-the-ssis-foreach-file-loop-container\"><\/span>Step 4: Configure the SSIS Foreach File Loop container<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Then double-click on the Foreach Loop Container sequence. In the Collection tab :<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Choose the folder where the flat files are located: <strong><em>C:\\Data<\/em><\/strong><\/li>\n\n\n\n<li>Then specify the name of the flat files to be loaded with SSIS: <strong><em>Customers_Data*.csv<\/em><\/strong><\/li>\n\n\n\n<li>Choose the full name of the file with the path: <strong><em>Fully qualified<\/em><\/strong><\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"981\" height=\"840\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-5-configure-csv-file-loop.jpg\" alt=\"Set the folder and filter to select the text files to be used in the SSIS loop\" class=\"wp-image-11939\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-5-configure-csv-file-loop.jpg 981w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-5-configure-csv-file-loop-300x257.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-5-configure-csv-file-loop-768x658.jpg 768w\" sizes=\"auto, (max-width: 981px) 100vw, 981px\" \/><figcaption class=\"wp-element-caption\"><em>Set the folder and filter to select the text files to be used in the SSIS loop<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>Then, in the <em>Variable Mappings<\/em> tab, link the <em>FlatFilePath<\/em> variable to index 0. This link dynamically transmits the name of the current file to the variable and therefore to the file connection.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"877\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-6-foreach-map-variable-1024x877.jpg\" alt=\"Bind the SSIS variable dynamically to the current file name\" class=\"wp-image-11947\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-6-foreach-map-variable-1024x877.jpg 1024w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-6-foreach-map-variable-300x257.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-6-foreach-map-variable-768x658.jpg 768w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-6-foreach-map-variable.jpg 1081w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Bind the SSIS variable dynamically to the current file name<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-5-add-the-file-name-to-import-using-ssis-derived-column\"><\/span>Step 5: Add the file name to import using SSIS derived column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Edit the derived column component and add the following two derived columns for:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Add the Customer ID in integer format<\/li>\n\n\n\n<li>Add the source file name from the dynamic variable<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"960\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-7-map-filename-derived-column-1024x960.jpg\" alt=\"Add the CustomerID and the Filename as derived columns\" class=\"wp-image-11953\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-7-map-filename-derived-column-1024x960.jpg 1024w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-7-map-filename-derived-column-300x281.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-7-map-filename-derived-column-768x720.jpg 768w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-7-map-filename-derived-column.jpg 1122w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Add the CustomerID and the Filename as derived columns<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"step-6-run-the-ssis-package-to-import-the-multiple-text-files-with-ssis-and-check-data-in-ssms\"><\/span>Step 6. Run the SSIS package to import the multiple text files with SSIS and check data in SSMS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now start the loading. The SSIS package loads the flat files into the database one by one, i.e., one data flow executed per file detected in the source folder. In this case, 3 iterations occur.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"480\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-10-execute-foreach_container.jpg\" alt=\"Execute the SSIS loop to import the text files into a single table and add the filename\" class=\"wp-image-11964\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-10-execute-foreach_container.jpg 700w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-10-execute-foreach_container-300x206.jpg 300w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\"><em>Execute the SSIS loop to import the text files into a single table<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>Clicking in the data flow, we notice that 16 lines are integrated in each iteration.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"720\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-11-execute-data-flow.jpg\" alt=\"Lines imported with the Integration Services data flow\" class=\"wp-image-11968\" style=\"width:700px;height:720px\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-11-execute-data-flow.jpg 700w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-11-execute-data-flow-292x300.jpg 292w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\"><em>Lines imported with the Integration Services data flow<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>Finally, check the results with the 2 following SQL queries. The result is consistent with 16 rows embedded for each source file, with no errors.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT [Filename], COUNT(*)\n  FROM [dbo].[Customers_with_filename]\n  GROUP BY [Filename]; \n\nSELECT * \n  FROM [dbo].[Customers_with_filename];<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"771\" height=\"889\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-12-check-results-ssms.jpg\" alt=\"Execute the SQL query in SSMS to check the data in the table\" class=\"wp-image-11972\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-12-check-results-ssms.jpg 771w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-12-check-results-ssms-260x300.jpg 260w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-load-multiple-text-files-12-check-results-ssms-768x886.jpg 768w\" sizes=\"auto, (max-width: 771px) 100vw, 771px\" \/><figcaption class=\"wp-element-caption\">Execute the SQL query in SSMS to check the data in the table<\/figcaption><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\">Conclusion on multiple text files import with SSIS<\/h3>\n\n\n\n<p>To avoid errors when importing of multiple text files into a SQL Server table with SSIS, check the <a href=\"https:\/\/www.w3.org\/International\/questions\/qa-what-is-encoding\" target=\"_blank\" rel=\"noreferrer noopener\">encoding<\/a> of the files to process. Also, the mapping of the variable is mandatory to make the package dynamic and avoid manual development.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">More on data import and export with SSIS<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/create-a-simple-ssis-data-flow\/\">Create a basic data flow with SSIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/create-a-connection-to-sql-server-database-in-ssis\/\">Create a connection to a SQL Server database in SSIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-text-file-into-sql-server-database-with-ssis\/\">Import an text file into a SQL database with SSIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-excel-file-into-sql-table-with-ssis\/\">Create a package to import Excel data into SQL Server<\/a><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"DhU7wT2Qrp\"><a href=\"https:\/\/expert-only.com\/en\/ssis-online-training\/\">SSIS Online Training<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SSIS Online Training&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/ssis-online-training\/embed\/#?secret=M6btoCzJkt#?secret=DhU7wT2Qrp\" data-secret=\"DhU7wT2Qrp\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Import multiple text files &#8211; csv or txt &#8211; into a SQL Server table using an SSIS package and a dynamic loop called the for each loop. This tutorial explains how to import multiple text files into SQL Server <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\" title=\"How to Import Multiple Text Files into SQL Server with SSIS?\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10709,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[516],"tags":[417],"class_list":{"0":"post-12999","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssis","8":"tag-file"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Import Multiple Text Files into SQL Server with SSIS? MS BI<\/title>\n<meta name=\"description\" content=\"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Import Multiple Text Files into SQL Server with SSIS?\" \/>\n<meta property=\"og:description\" content=\"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-04T03:34:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-04T14:19:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How to Import Multiple Text Files into SQL Server with SSIS?\",\"datePublished\":\"2023-07-04T03:34:00+00:00\",\"dateModified\":\"2024-03-04T14:19:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\"},\"wordCount\":856,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg\",\"keywords\":[\"File\"],\"articleSection\":[\"SSIS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\",\"name\":\"How to Import Multiple Text Files into SQL Server with SSIS? MS BI\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg\",\"datePublished\":\"2023-07-04T03:34:00+00:00\",\"dateModified\":\"2024-03-04T14:19:05+00:00\",\"description\":\"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Import Multiple Text Files into SQL Server with SSIS?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Import Multiple Text Files into SQL Server with SSIS? MS BI","description":"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to Import Multiple Text Files into SQL Server with SSIS?","og_description":"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.","og_url":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2023-07-04T03:34:00+00:00","article_modified_time":"2024-03-04T14:19:05+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How to Import Multiple Text Files into SQL Server with SSIS?","datePublished":"2023-07-04T03:34:00+00:00","dateModified":"2024-03-04T14:19:05+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/"},"wordCount":856,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg","keywords":["File"],"articleSection":["SSIS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/","url":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/","name":"How to Import Multiple Text Files into SQL Server with SSIS? MS BI","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg","datePublished":"2023-07-04T03:34:00+00:00","dateModified":"2024-03-04T14:19:05+00:00","description":"To import multiple text files into a SQL Server table with SSIS, use the dynamic for each file loop to detect files to be processed.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/journal-and-coffee-7BF9C361150_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to Import Multiple Text Files into SQL Server with SSIS?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/12999","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=12999"}],"version-history":[{"count":16,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/12999\/revisions"}],"predecessor-version":[{"id":30460,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/12999\/revisions\/30460"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10709"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=12999"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=12999"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=12999"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}