I have a for loop that will insert records if a value does not exist in a table. I would like to check to see if a value already exist in a SQL table before executing an insert step from an SSIS package. If the value already exist then loop to new record. If the value does not exist, insert new row. How is this typically done in SSIS? Should I use an execute SQL task to check the database and assign a return value to a variable in this scenario?

That's typically done with a lookup in a data flow. where (1) is lookup the business key to check whether the key exists and (2) optional checks whether there are changes. If you don't use a data flow, then you can use an Execute SQL Statement (with a parameter for the key/value that you want to lookup): SELECT count(*) as counter FROM yourtable WHERE column1 = ? Then you can assign that counter column to a variable and use that variable in for example a Precedence Constraint. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I have a for loop that will insert records if a value does not exist in a table. I would like to check to see if a value already exist in a SQL table before executing an insert step from an SSIS package. If the value already exist then loop to new record. If the value does not exist, insert new row. How is this typically done in SSIS? Should I use an execute SQL task to check the database and assign a return value to a variable in this scenario? First of all, is there a good reason to use a Foreach loop here? why are you looping over the set of rows that you want to insert?http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Anyways SSISJoost has given you the perfect solution.. but for the future reference you can check the below.. there is a "FAQ - SSIS Information, Webcasts, Tools, Add-ons, etc..." on first page of our SSIS forum. Please check the relevant post for you... Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/

I'm looping thru file directory to insert the the data from the files into a table.

I'm looping thru file directory to insert the the data from the files into a table. Then you are probably using a Foreach Loop: In the Data Flow of that Foreach Loop you can use the Lookup Transformation to check whether a certain record already exists in the destination table: If you want to check whether the file has already been processed then you can use the Execute SQL Task. For example: to insert the filename into a table/to check whether the filename is already in the table. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

SSISJoost showed the way, I've just explored details solution for UPSERT with lookup here: http://rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.htmlhttp://www.rad.pasfu.com