row count value check

Question

Hello,

I have built a SSIS package that loops thru a directory and outputs record count for each file in a delimited file. I have used the "for each loop" enumerator with aggregated row count to output file name and record count. It works well.

Now, I want to be able to check the value of record count. If any of the files is empty, record count is zero, I want to be able to raise error and fail the package. I will then use an error handler to send email with custom error description. If  none are emtpy, I would want it to proceed with loading.

I have tried using "conditional split" after aggregated row count but might not be using correctly or it might not be the correct transformation to use.

any suggestions?

Thanks,
Jen

Answer 1

No, the conditional split isn't the right component to use.

To fail your package when the row count is zero, you should use a Script Task.  Unless you have some other reason for using an Aggregate component for determining the row count, you should use the Row Count component instead - because we need to assign the row count value to an SSIS variable, and that's what the Row Count component is made for.  After your Data Flow Task, place a Script Task, and pass in the SSIS variable with the row count as a ReadOnly variable.  Inside the Script, examine the contents of the variable - if they're zero, then return a "fail" code instead of the "success" code that's been generated for you in the script.

Let me know how that goes.

---

Answer 2

No, the conditional split isn't the right component to use.

To fail your package when the row count is zero, you should use a Script Task.  Unless you have some other reason for using an Aggregate component for determining the row count, you should use the Row Count component instead - because we need to assign the row count value to an SSIS variable, and that's what the Row Count component is made for.  After your Data Flow Task, place a Script Task, and pass in the SSIS variable with the row count as a ReadOnly variable.  Inside the Script, examine the contents of the variable - if they're zero, then return a "fail" code instead of the "success" code that's been generated for you in the script.

Let me know how that goes.

---

Hi Jen,
Todd is correct, you can follow his solution. except that you can check this thread also for the more clarification:

" http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/45cb9825-a6b2-47a7-a5dc-a694f55783d9 "

Thanks-

---

Let us TRY this

Answer 3

I have a "row count" transformation - I observed it returned "0" for each row. I had to to a "count all" aggregate on the top of that to get record count.

I don't need "row count" for each row - I need it for entire file.

Thanks,
Jen

Answer 4

You must be expecting something different.

The Row Count transformation takes all of your rows in - but it does not modify them at all.  It will not add a row count to your flow, or aggregate your rows.  It simply counts the rows and places the result into a variable.  The Aggregate "consumes" the rows and produces (in your case) only one row with the row count.  Both do similar things - but the Aggregate component doesn't do the extra step of moving that result into a variable, which is what we need to do.  The Row Count transformation does - and doesn't affect your data flow at all.  The Row Count can be used as the "last" component in a flow - much like a destination component.

---

Answer 5

so many options out there - a sure way of confusing a libran like me :(

anyway, script task seem to be working. I also stripped out the row count and aggregate transformations. I am reading each filepath and outputting total rows in a file. works fine.

My issue is around "compile binary code" now. For the code to work in SSIS designer, I had to set "delay validation" property to true. "Precompile to binary" is set to true. I switched the "delay validation" to false when code provided desired output.

Now I schedule the job on the server - I get error "the task is configured to precompile the script but binary code not found. visit IE...". I open the package in SSIS designer and see the same error.

confused ... it's more than a trait for sure ! :))

Thanks,
Jen

Answer 6

The "delay validation" property essentially tells SSIS that it shouldn't worry about the fact that your OLE DB Source component doesn't have a meaningful SQL Statement (because your script hasn't executed yet).

The given error isn't very instructive, it's an "indirect" explanation of what's wrong.  SSIS is telling you that it can't find the compiled version of your script in the package... because it hasn't been compiled... because there's a syntax error in your script.  You need to open the script up and look for error notations in the script.

---

Answer 7

I dont see any errors in the script. I checked each line by line. I even commented everything out leaving only  "Dim" statements in the script but no luck.

Thanks,
Jen

Answer 8

Alright then.

Set that property (precompile) to false, and put a breakpoint on the first line of your script.  Run the package.  When it stops in your script, use the "Step Over" command (in the Debug menu) to process through your script line by line to verify it works.

Having the script precompiled is not required - you can leave it uncompiled.  Read the "Precompiled Script" section here: http://msdn.microsoft.com/en-us/library/ms137640(SQL.90).aspx

---

Answer 9

A different approach could be taken.
After setting the row count to a variable you could use precedence constrain.
use expression to redirect the process.
Send mail if rowcount = 0 else continue normal process.

---

Hope this helps !! - Sudeep

Answer 10

as it turns out I can't run any scripts since I am missing a hotfix. if the script is not compiled with binary code created, it cant run as a job in 64-bit environment. I am in the process of getting hot fix indicated here - hope that will help.

http://support.microsoft.com/kb/932557

Answer 11

hmm..stuck again

I downloaded the hotfix for 32-bit (x86) from above site and while trying to install it - I am getting error:

This update requires service pack 0. The service pack for product instance SQL Tools is 1. Download the update for service pack 1.

what does it mean? I have Windows XP SP2 IE6 SQL Server 2005 installed on my machine. really confused about "service pack 0" statement here.

 

any help is appreciated.

 

thanks,

Jen

Answer 12

You may want to look at KB 932556 instead.  The KB you posted refers to an early build - possibly a CTP or other beta.

---