skaszyk


I am importing records from a flat file to a database table. If a record is in the table but NOT in the flat file, I need to update a date column in the table.

Any ideas




Re: Detect Missing Records in Flat File

Phil Brammer


First page of this forum, "Checking to see if a record exists, if so update, else insert"

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1211340&SiteID=1






Re: Detect Missing Records in Flat File

skaszyk

Thanks Phil, I read that post before asking my question.

Since the row exists in the table but NOT in the flat file, a lookup doesn't work. Unless you know of a way to connect a Lookup to a flat file. As I mentioned, I need to know when a row in the database doesn't exist in a flat file.






Re: Detect Missing Records in Flat File

Phil Brammer

A lookup does too work, you just have to load a staging table first with the contents of the flat file.

You can have more than one data flow task in a given package, so in the first data flow, load up the staging table with the file, and then in the second data flow, use an OLE DB source against your table and then use the lookup transformation against your staging table.





Re: Detect Missing Records in Flat File

skaszyk

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead





Re: Detect Missing Records in Flat File

Phil Brammer

skaszyk wrote:

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead


If the file is hugh, something on the order of +1,000,000 rows, I'd say yes, but flat files are very fast to process, and I would not think you'd gain anything by using your method versus mine. For most things I do, though, I always push data into staging tables first, before working with the data. Having the data in a table offers the ability to perform SQL upfront while leveraging the database engine, among other things.





Re: Detect Missing Records in Flat File

PedroCGD

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped

Regards!






Re: Detect Missing Records in Flat File

Phil Brammer

PedroCGD wrote:

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped

Regards!



Pedro, he's already done that. Please read the entire thread first.





Re: Detect Missing Records in Flat File

PedroCGD

Sorry phil, but my page wasn't refreshed and didn't see some posts...

Regards!