Best Practice - How to Filter Data After Execute SQL Task

Question

 Newbie question:

In my Package I have an Execute SQL task running a stored procedure that populates a table. If the task cannot find a person's date of birth, it sets that column with NULL.

For the records with no DOB, I do not want to continue processing. Instead, I need to put them in a separate table for reporting.

I could to the work inside the stored procedure, but I would like to make it a visible step in the package design.

Ideally, what is the best practice to pull certain records from a table and put them in table A and put the rest in table B?

There is an OnPostExecute event I can use to set up a Data Flow Task to set up the query and move the fields.

Or, in the main Control Flow I can have another Execute SQL task after the first one that does that work. 

Does anyone have suggestions?

Thanks.

Answer 1

you can use precedence constraint to implement IF condition and direct flow to other tasks, like this example:

http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.html

but in your case I think using OLE DB Command inside the data flow task with combination of Conditional Split works much better.

you want to run the stored procedure for every record in source table, and if there was DOB then put them in table A, otherwise insert those records into table B.

so I think best way to do that is to create a data flow task,

create a source for your source table or file

add an OLE DB Command and write your sql statement there, format of passing parameter is look like execute sql task. for fetching output parameter out of stored procedure with OLE DB Command follow instruction of my blog post here:

http://rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.html

after OLE DB command add a conditional split transform, and write conditions there to find out which records have DOB and which don't , with expressions and then connect two different output of conditional split to two ole db destination for tableA and tableB.

let me know where you need more details

---

http://www.rad.pasfu.com

Answer 2

Hello,

Follow the links below. It might help you on this.

http://microsoft-ssis.blogspot.com/2010/12/performance-best-practices.html

http://www.citagus.com/citagus/blog/sql-server-integration-services-best-practices-part-i/

Answer 3

  Thank you, Reza.  The links show great ideas.

Answer 4

 Thank you, Mario, for the link to the best practices pages. I did not see them on my own.