NewbietoSSIS


Hello ALL

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

I also want to update some users using a different text file.

Please help me with the best way to do this

Thanks in advance





Re: Load a text file with email addresses and compare against a database table that has email addresses and user_id

jwelch


You'd use a data flow with a Flat File Source to read the text file, a Lookup to check whether the email address exists, and an OLE DB Command to issue the DELETE statement. Or you write the matching rows to a "temp" table using an OLE DB Destination, and use an Execute SQL task after the data flow to issue a batch DELETE statement. UPDATES can be done the same way.

Working through the tutorials in Books Online will introduce you to some of these concepts.







Re: Load a text file with email addresses and compare against a database table that has email addresses and user_id

NewbietoSSIS

jwelch

Could you please help may be with an example or something I will really appreciate that.

becuase I don't know what OLE DB command is.

so are you suggesting like so

text file connection

|

|

\/

lookup

|

|

\/

OLE DB COmmand (what is this ) (issue the delete or update command)

I want to be able to do delete from table A where A.email_address = text_file.email_address

Thanks for your help







Re: Load a text file with email addresses and compare against a database table that has email addresses and user_id

jwelch

The OLE DB Command is one of the transforms in the toolbox in Visual Studio (same place you found the lookup).

The basic flow you have above is correct.






Re: Load a text file with email addresses and compare against a database table that has email addresses and user_id

NewbietoSSIS

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = and is set in the to param 0 to the value coming from the lookup.

Is that right






Re: Load a text file with email addresses and compare against a database table that has email addresses and user_id

Phil Brammer

NewbietoSSIS wrote:

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = and is set in the to param 0 to the value coming from the lookup.

Is that right



Looks like it.