Flat file import to SQL 2005

Question

Flat file import to SQL 2005

chugghin

Say Hey Kid

Points: 672
More actions
July 2, 2010 at 9:01 am

#147907

I'm not new to SQL, but I am new(ish) to SSIS packages. I have a flat file (csv) that needs to be imported on a weekly basis but it's a rolling file (new records are appended to the .csv file). My problem is that I obviously get a PK error when importing this file since the previous week's data is already in the table. My question is how can I get around this and import the new records using the SSIS package? Ordinarilly I would do this in MS Access since I can import the file as a table and do a left join from it to the destination table to import only the records that are missing, but the csv file has user-entered fields that greatly exceed Access's 255 byte/char field limit. Any help is appreciated. I would prefer not to dump and reload assuming the file will continue to grow and therefore take longer to process.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 1

You can use a lookup transformation to lookup on your destination table... before your destination task and try to lookup the record if it is already there in your destination table then dont insert it

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

chugghin Say Hey Kid Points: 672 More actions · Answer 2

Sorry - but again, new to SSIS packages. I did the 'Lookup' wizard before the destination step and the lookup step fails w/the error "Row yielded no match during lookup". Is it only processing one row at a time?

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

ROW YIELD NO MATCH DURING LOOKUP will come when you have New rows coming from your source,

(dont do anything for Green arrow, unless you want to do updates to existing rows)

in lookup,you should not take green arrow instead take only red arrow to your destination...

your lookup TX will show two arrows one green and one red...use the red arrow...and drag it to your destination it will show a message box to CONFIGURE ERROR OUTPUT SELECT in ERROR column REDIRECT ROW there SAY OK...and run again...

see this link:

http://blog.raffaeu.com/archive/2008/06/20/sql-ssis-insert-only-new-rows.aspx

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

chugghin Say Hey Kid Points: 672 More actions · Answer 4

Got it to work - sort of. I had to take the PK off to get it to work. I saw the debug progress as it went through the steps - getting 89 records initially and outputting 52 from the lookup error redirect. I did a VLOOKUP in Excel to verify the 52 and they were correct. I just don't understand why I still got the PK error on the 52.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

I dont know what your primary key is, may be you have to check the data coming from the source, what is the column you are looking up on?

you should run some queries to find out if the data is correct in source. no duplicates and all in PK column.

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

chugghin Say Hey Kid Points: 672 More actions · Answer 6

I may've figured it out though I'm not sure how I got the PK on the data in the first place. The source file data has blank data in the PK column (not null but "" [blank]). Since PKs can't have null values in them - one made it in when I initially created the table but this go-around there are several blanks thereby creating a PK violation. I suppose w/the lookup though it wouldn't matter much anyway - just can't have dupes is all. Would be nice to have the table indexed though for speedy queries. Thanks for the heads up about the lookup feature though. That did it!