StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is one possible option. You can achieve this using the <code>Data Conversion</code> transformation within the <code>Data Flow Task</code>. Following example shows how this can be achieved. The example uses SSIS 2005 with SQL Server 2008 database.</p> <p><strong>Step-by-step process:</strong></p> <ol> <li><p>Create a file named <code>FlatFile.CSV</code> and populate it with data as shown in screenshot #<strong>1</strong>.</p></li> <li><p>In the SQL database, create two tables named <code>dbo.CSVCorrect</code> and <code>dbo.CSVWrong</code> using the scripts provided under <strong>SQL Scripts</strong> section. The fields in the table <code>dbo.CSVWrong</code> should have the data types <em>VARCHAR</em> or <em>NVARCHAR</em> or <em>CHAR</em> so that it can accept the invalid records.</p></li> <li><p>On the SSIS package, create an OLE DB connection named SQLServer to connect to SQL Server database and create a Flat File Connection named CSV. Refer screenshot #<strong>2</strong>. Configure the flat file connection CSV as shown in screenshots #<strong>3</strong> - #<strong>7</strong>. All the columns in the flat file connection should be configured as <strong>string</strong> data type so that the package doesn't fail while reading the file.</p></li> <li><p>On the Control Flow tab of the package, place a <code>Data Flow Task</code> as shown in screenshot #<strong>8</strong>.</p></li> <li><p>On the Data Flow tab of the package, place a <code>Flat File Source</code> and configure it as shown in screenshots #<strong>9</strong> and #<strong>10</strong>.</p></li> <li><p>On the Data Flow tab of the package, place a <code>Data Conversion</code> transformation and configure it as shown in screenshot #<strong>11</strong>. Click on the <code>Configure Error Output</code> and change the <strong>Error</strong> and <strong>Truncation</strong> column values from <em>Fail component</em> to <strong>Redirect row</strong>. Refer screenshot #<strong>12</strong>.</p></li> <li><p>On the Data Flow tab of the package, place an <code>OLE DB Destination</code> and connect the <strong>green</strong> arrow from <strong>Data Conversion</strong> to this OLE DB Destination. Configure the OLE DB Destination as shown in screenshots #<strong>13</strong> and #<strong>14</strong>.</p></li> <li><p>On the Data Flow tab of the package, place another <code>OLE DB Destination</code> and connect the <strong>red</strong> arrow from <strong>Data Conversion</strong> to this OLE DB Destination. Configure the OLE DB Destination as shown in screenshots #<strong>15</strong> and #<strong>16</strong>.</p></li> <li><p>Screenshot #<strong>17</strong> shows the Data Flow Task once it has been completely configured.</p></li> <li><p>Screenshot #<strong>18</strong> shows data in the tables <strong>before</strong> the package execution.</p></li> <li><p>Screenshot #<strong>19</strong> shows package execution within Data Flow Task.</p></li> <li><p>Screenshot #<strong>20</strong> shows data in the tables after the package execution.</p></li> </ol> <p>Hope that helps.</p> <p><strong>SQL Scripts:</strong></p> <pre><code>CREATE TABLE [dbo].[CSVCorrect]( [Id] [int] IDENTITY(1,1) NOT NULL, [SNo] [int] NULL, [SName] [varchar](50) NULL, [QuantityNumeric] [numeric](18, 0) NULL, CONSTRAINT [PK_CSVCorrect] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO CREATE TABLE [dbo].[CSVWrong]( [Id] [int] IDENTITY(1,1) NOT NULL, [SNo] [varchar](50) NULL, [Quantity] [varchar](50) NULL, [SName] [varchar](50) NULL, [ErrorCode] [int] NULL, [ErrorColumn] [int] NULL, CONSTRAINT [PK_CSVWrong] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO </code></pre> <p><strong>Screenshot #1:</strong></p> <p><img src="https://i.stack.imgur.com/yLYde.png" alt="1"></p> <p><strong>Screenshot #2:</strong></p> <p><img src="https://i.stack.imgur.com/nLoDX.png" alt="2"></p> <p><strong>Screenshot #3:</strong></p> <p><img src="https://i.stack.imgur.com/IHhqX.png" alt="3"></p> <p><strong>Screenshot #4:</strong></p> <p><img src="https://i.stack.imgur.com/dmbDz.png" alt="4"></p> <p><strong>Screenshot #5:</strong></p> <p><img src="https://i.stack.imgur.com/ArRMr.png" alt="5"></p> <p><strong>Screenshot #6:</strong></p> <p><img src="https://i.stack.imgur.com/c3IUN.png" alt="6"></p> <p><strong>Screenshot #7:</strong></p> <p><img src="https://i.stack.imgur.com/C4gZz.png" alt="7"></p> <p><strong>Screenshot #8:</strong></p> <p><img src="https://i.stack.imgur.com/NCrZ1.png" alt="8"></p> <p><strong>Screenshot #9:</strong></p> <p><img src="https://i.stack.imgur.com/UXXOw.png" alt="9"></p> <p><strong>Screenshot #10:</strong></p> <p><img src="https://i.stack.imgur.com/onYNm.png" alt="10"></p> <p><strong>Screenshot #11:</strong></p> <p><img src="https://i.stack.imgur.com/RmE7R.png" alt="11"></p> <p><strong>Screenshot #12:</strong></p> <p><img src="https://i.stack.imgur.com/Sviup.png" alt="12"></p> <p><strong>Screenshot #13:</strong></p> <p><img src="https://i.stack.imgur.com/vVW4a.png" alt="13"></p> <p><strong>Screenshot #14:</strong></p> <p><img src="https://i.stack.imgur.com/ydWE9.png" alt="14"></p> <p><strong>Screenshot #15:</strong></p> <p><img src="https://i.stack.imgur.com/xLVAe.png" alt="15"></p> <p><strong>Screenshot #16:</strong></p> <p><img src="https://i.stack.imgur.com/g1TeZ.png" alt="16"></p> <p><strong>Screenshot #17:</strong></p> <p><img src="https://i.stack.imgur.com/5I3ih.png" alt="17"></p> <p><strong>Screenshot #18:</strong></p> <p><img src="https://i.stack.imgur.com/rrUm0.png" alt="18"></p> <p><strong>Screenshot #19:</strong></p> <p><img src="https://i.stack.imgur.com/PdV8z.png" alt="19"></p> <p><strong>Screenshot #20:</strong></p> <p><img src="https://i.stack.imgur.com/xssiz.png" alt="20"></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