CODEWITHSUNDEEP
csvssisssis-2012
Sudip Thapa
Sudip Thapa

Reputation: 120

Find out Total Number of Rows affected by SQL Command Variable in Data Flow Task

I have SQL Command From Variable (In General it is a Select Statement) as a Source in DataFlow Task. Destination is .csv File.

Problem: Even though no rows is affected by SQL command Variable .csv file is generating without records. I don't want to generate the file if the select statement (from SQL command variable) populate no records.

Please advise me.

Upvotes: 0

Views: 163

Answers (1)

ɐlǝx
ɐlǝx

Reputation: 1394

Simple procedure: you could count the rows with a query before export, using Execute SQL Task, if the number of rows is greater than 0 then proceed with the export;
The following is a possible solution:

  • use a query like SELEC COUNT(*) AS MYCOUNT FROM...
  • use a package variable (myVariable to associate with MYCOUNT), to contain the number of rows
  • set Result Set = Single Row in SQL Task Editor
  • map the variable in tab Result Set in SQL Task Editor (MYCOUNT - myVariable)
  • use two arrows from Execute SQL Task in each arrow choose Evaluation operation: Expression, Expression: myVariable > 0 (first arrow) and myVariable == 0 (second arrow), choose Logical OR, in this way you have a bifurcation!
  • connect the export to the arrow with myVariable > 0
  • connect the other arrow to another possible task, for example it can warn you that there are no rows via email

For counting rows can also use the task: Row Count (present in the latest SSIS versions); the Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable.

I hope it help

Upvotes: 1

Related Questions