StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>One possible way of achieving this is by using <code>ForEach Loop container</code> in the <code>Control Flow</code> tab and then placing a <code>Data Flow task</code> within the Control Flow task. I have explained the process in the below example. This example uses <code>SQL Server</code> back-end as the destination and <code>Excel 97-2003</code> format <code>.xls</code> as the source files. Please <strong>note</strong> that the Excel files should be of same format.</p> <p><strong>Ste-by-step process:</strong></p> <ol> <li><p>Create a table named <code>dbo.Location</code> as shown in screenshot #<strong>1</strong>. This example will populate this table by reading three different Excel files having the same layout. The screenshot shows an empty table before the package execution.</p></li> <li><p>Create two Excel files in path <code>c:\temp\</code> as shown in screenshots #<strong>2</strong> - #<strong>4</strong>. Notice that both the Excel files have the same layout but different content.</p></li> <li><p>On the SSIS package, create three variables as shown in screenshot #<strong>5</strong>. Variable <code>FolderPath</code> will contain the path where the Excel files are located; <code>FileExtension</code> will contain the Excel file extension (here in this case it is <strong>*.xls</strong>) and <code>FilePath</code> should be configured to point to one valid Excel file (this is required only during the initial configuration of the Excel connection manager).</p></li> <li><p>Create an <code>Excel connection</code> in the <strong>connection manager</strong> pointing to one valid Excel file as shown in screenshot #<strong>6</strong>.</p></li> <li><p>Create an <code>OLE DB Connection</code> in the <strong>connection manager</strong> pointing to the SQL Server.</p></li> <li><p>On the SSIS package, place a ForEach Loop container and a Data Flow task within the ForEach loop container as shown in screenshot #<strong>7</strong>.</p></li> <li><p>Configure ForEach loop container as shown in screenshots #<strong>8</strong> and #<strong>9</strong>. By doing this, variable <code>User::FilePath</code> will contain the full path Excel files located in the folder <code>c:\temp\</code> with the help of variables <code>FolderPath</code> and <code>FileExtension</code> configured on the <code>Collection</code> section.</p></li> <li><p>Inside the data flow task, place an <code>Excel source</code> to read Excel file data and O<code>LE DB destination</code> to insert data into SQL Server table <strong>dbo.Location</strong>. Data flow task should look like as shown in screenshot #<strong>10</strong>.</p></li> <li><p>Configure the Excel source as shown in screenshots #<strong>11</strong> and #<strong>12</strong> to read the data using Excel connection.</p></li> <li><p>Configure the OLE DB destination as shown in screenshots #<strong>13</strong> and #<strong>14</strong> to insert the data into SQL Server database table. </p></li> <li><p>On the <strong>Excel connection</strong> in the connection manager, configure the Expressions <code>ExcelFilePath</code> and <code>ServerName</code> as shown in screenshot #<strong>15</strong>.</p></li> <li><p>Sample execution of the data flow task is shown in screenshot #<strong>16</strong>.</p></li> <li><p>Screenshot #<strong>17</strong> displays the data in the table dbo.Location after package execution. Please note that it contains all the rows present in Excel files shown in screenshots #<strong>3</strong> and #<strong>4</strong>.</p></li> <li><p>On the <code>Data Flow task</code> properties, Set the <code>DelayValidation</code> to <strong>True</strong> so that the SSIS doesn't throw errors when you open the package.</p></li> </ol> <p>Hope that helps.</p> <p><strong>Screenshot #1:</strong></p> <p><img src="https://i.stack.imgur.com/nDrCm.png" alt="1"></p> <p><strong>Screenshot #2:</strong></p> <p><img src="https://i.stack.imgur.com/QizPX.png" alt="2"></p> <p><strong>Screenshot #3:</strong></p> <p><img src="https://i.stack.imgur.com/qKiu0.png" alt="3"></p> <p><strong>Screenshot #4:</strong></p> <p><img src="https://i.stack.imgur.com/mqDUD.png" alt="4"></p> <p><strong>Screenshot #5:</strong></p> <p><img src="https://i.stack.imgur.com/DYWxo.png" alt="5"></p> <p><strong>Screenshot #6:</strong></p> <p><img src="https://i.stack.imgur.com/kHd4X.png" alt="6"></p> <p><strong>Screenshot #7:</strong></p> <p><img src="https://i.stack.imgur.com/NASkM.png" alt="7"></p> <p><strong>Screenshot #8:</strong></p> <p><img src="https://i.stack.imgur.com/Hue96.png" alt="8"></p> <p><strong>Screenshot #9:</strong></p> <p><img src="https://i.stack.imgur.com/Cht39.png" alt="9"></p> <p><strong>Screenshot #10:</strong></p> <p><img src="https://i.stack.imgur.com/c5XiD.png" alt="10"></p> <p><strong>Screenshot #11:</strong></p> <p><img src="https://i.stack.imgur.com/sy8bL.png" alt="11"></p> <p><strong>Screenshot #12:</strong></p> <p><img src="https://i.stack.imgur.com/b6wxb.png" alt="12"></p> <p><strong>Screenshot #13:</strong></p> <p><img src="https://i.stack.imgur.com/PUaHF.png" alt="13"></p> <p><strong>Screenshot #14:</strong></p> <p><img src="https://i.stack.imgur.com/HUiVZ.png" alt="14"></p> <p><strong>Screenshot #15:</strong></p> <p><img src="https://i.stack.imgur.com/4Osn9.png" alt="15"></p> <p><strong>Screenshot #16:</strong></p> <p><img src="https://i.stack.imgur.com/Snhgy.png" alt="16"></p> <p><strong>Screenshot #17:</strong></p> <p><img src="https://i.stack.imgur.com/zBSzG.png" alt="17"></p> <p><strong>Screenshot #18:</strong></p> <p><img src="https://i.stack.imgur.com/8T8XX.png" alt="18"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload