October 12, 2011 at 3:16 pm
I found this article (http://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/)
discussing how to do almost what I need, however, something appears to be missing. The areas regarding the "split" conditions are unclear. It looks like both have the same condition (which doesnt make sense). Anyways...
Scenario:
3 Tables : 1st is destination for import (TestTable), 2nd is the reference table (TestReferenceTable), 3rd is the exception table (TestExceptionTable).
CREATE TABLE TestTable(
[RowID] BIGINT IDENTITY(1,1) NOT NULL,
[Column01] INT,
[Column02] INT,
[Column03] INT,
[Column04] VARCHAR(5),
[Column05] VARCHAR(5)
);
CREATE TABLE TestReferenceTable(
[RowID] BIGINT IDENTITY(1,1) NOT NULL,
[Column01] INT,
[Column02] INT,
[Column03] INT,
[Column04] VARCHAR(5),
[Column05] VARCHAR(5)
);
CREATE TABLE TestExceptionTable(
[RowID] BIGINT IDENTITY(1,1) NOT NULL,
[Column01] INT,
[Column02] INT,
[Column03] INT,
[Column04] VARCHAR(5),
[Column05] VARCHAR(5)
);
Data:
INSERT INTO [TestReferenceTable]
SELECT 3001,3002,3003,'3004','3005'
Flat File (contents):
"Column01","Column02","Column03","Column04","Column05"
0001,0002,0003,"0004","0005"
1001,1002,1003,"1004","1005"
2001,2002,2003,"2004","2005"
3001,3002,3003,"3004","3005"
GOAL:
The goal is to import all the rows in the flat file to the [TestTable] where there is a matching value in the [Column01] column in the "reference" table and the "Column01" column in the flat file. All other rows go to the "exceptions" table. So at the end of the run with this data the 1 row from the flat file "3001,3002,3003,'3004','3005'" should be the only row found in the [TestTable] and all the others should be found in the [TestExceptionsTable].
*I know I could accomplish this with importing the flat file into a temporary table, but this issue may come up alot and having the flexibility to do it inside of an SSIS package would be really nice.
Thank You In Advance.
JT
October 12, 2011 at 4:21 pm
You're just doing imports, not trying to update anything in an excel spreadsheet right? If so, that URL you're referencing isn't really what you want. What you want is much simpler than that.
What you'll want to have is a Data Flow Task. In the Data Flow Task, you'll have a Flat File Source. Configure the Flat File Connection Manager of the source accordingly (comma for delimiters, quotes for text delimiters). Next, tie the source to a Lookup Transform. The lookup transform will have, as its source, a SQL query querying TestReferenceTable. Basically, "SELECT Column01 FROM TestReferenceTable". Connect Column01 from the source to Column01 from the Lookup. You don't need any columns returned from the lookup, so that's all you'll need there.
Now here's the key. Go into the Configure Error Output for the Lookup Transform. Make sure you change the first line of the error, the one corresponding to Lookup Error, from Fail Component to Redirect row.
You'll now have two possible outputs from your Lookup Transform. The green one, for success, will point towards an OLE DB Destination that goes to TestTable. The red one, for failure, will go to an OLE DB Destination that goes to TestExceptionTable.
Voila, you're done!
October 13, 2011 at 12:29 pm
VERY VERY NICE!!!
Perfectly explained and exactly what I was looking for.
Thank You very much!
JT
😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply