Table check task

Question

Table check task

Cralis

SSC Veteran

Points: 230
More actions
April 29, 2009 at 7:52 pm

#137212

I have a query that returns a COUNT(*), and it basically goes and
checks if there is anything in one staging table, that references
another - having invalid references. So if the count comes back > 0,
then there is a problem, and the migration must stop.
How would I do this? Do I use a SQL Task - and if so, how do I fail it
based on the result?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Create a variable, say RowCount (int32).

Add a Row Count transformation immediately after your source component and map the row count result to the RowCount variable you just created.

Then use precedence constraints on your Control Flow to direct the flow, based on the value in RowCount.

Cralis SSC Veteran Points: 230 More actions · Answer 2

Thanks Phil.

My row count will always be 1, if using a SELECT Count(*), so I assume I should change that to a SELECT *, and then use the steps you mention?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

OK, I changed my mind - let's do it your way after all, as you do not need the staging table data for other reasons.

Create the variable as I mentioned before.

Create and configure your Execute SQL task:

Select Count(*) [Count] from table

Change result set to 'Single Row'.

Click on the Result Set tab and Add. Set Result Name to Count and Variable Name to User::Rowcount (use the drop-down).

When the Execute SQL task runs, it will map the results of the query (specifically the Count column) to the variable Rowcount.

Now use precedence to control the logical flow, as previously mentioned, based on whether RowCount > 0 ...

Cralis SSC Veteran Points: 230 More actions · Answer 4

Thanks Phil.

This sounds like it's going to work. I'm new, so please bare with me.

I have created the Execute SQL Task, put all the good stuff in, and put the result into my 'MappingCheckRowCount' variable.

I then create a green 'SUCCESS' arrow to the next step. I (for the first time in my SSIS career...) changed a property, 'EvalOp' on the arrow to 'Expression'. The line turned from green to blue. Is this good? I then edited it, and changed the Evaluation Operation to 'Expression'. In the 'Expression' box, I put: @MappingCheckRowCount == 0

Clicked Test and no errors.

I now have my Task, with a blue arrow, pointing to the next task.

I am unsure how to handle the event of having @MappingCheckRowCount > 0. In other words, something is wrong with the data.. I want to redirect to a Send Mail task, and then stop the process. Do I just create another Blue Arrow, and change the formula?

Cralis SSC Veteran Points: 230 More actions · Answer 5

To add to that, I have tried to run my task, and get an error...

Error: 0x0 at Check Vehicle Mappings: No result rowset is associated with the execution of this query.

Error: 0xC002F309 at Check Vehicle Mappings, Execute SQL Task: An error occurred while assigning a value to variable "MappingCheckRowCount": "Exception from HRESULT: 0xC0015005".

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

Pretty much - you're on the right path! Start running it with some test data and seeing what happens ... the best way to learn.

I would use Expression and Constraint, by the way - you want to traverse down the processing path only if the variable test is passed and there were no other errors in the task.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

Cralis (4/29/2009)
To add to that, I have tried to run my task, and get an error...
Error: 0x0 at Check Vehicle Mappings: No result rowset is associated with the execution of this query.
Error: 0xC002F309 at Check Vehicle Mappings, Execute SQL Task: An error occurred while assigning a value to variable "MappingCheckRowCount": "Exception from HRESULT: 0xC0015005".

Weird. I tried a few ways of cocking up my package (as it were) and was unable to reproduce this error, so not sure what is going on.