Assigning a record count to a variable for comparison purposes

Question

Assigning a record count to a variable for comparison purposes

DamianC

SSCrazy Eights

Points: 8023
More actions
October 31, 2016 at 7:58 am

#331464

Hello
I have a package that I use to load data
To load and compare counts, I'm using a foreach loop
This basically works through each of my csv files and loads the data into tables
In this instance, I've chosen a for loop as I also want to compare csv row counts against the row counts in their corresponding table
I can easily use Row Count to obtain the count from the csv file and allocate the result to a variable (sourceCount)
I'm struggling with the SQL table row count part
I have created a variable called destinationCount made this of type Int32 with a default value of 0 and a scope of the package
The for loop has a variable used for the filename
I then use a c# script to obtain the table name (filename/location less the path and csv extension using GetFileNameWithoutExtension)
I have a variable called tableName that I set to this value
All of this works so far
So, now I create a SQL task with:
ParameterMapping tab: User::tableName with a Parameter Name of 0 for the table
Result Set tab: Result Name = 0 and Variable Name = User::recordCountDestination for the record count
General tab: ResultSet = Single Row; SQL Statement = select count(*) from ?
I'm getting the following error:
[Execute SQL Task] Error: Executing the query "select count(*) as recordCountDestination from ?" failed with the following error:
"An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anybody seen and resolved this before?
DBTYPE_I4 seems correct to me as I'm passing a count to Int32
Thanks
Damian
- Damian

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

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

Thom A SSC Guru Points: 99134 More actions · Answer 1

If I'm reading your SQL right, you're declaring a Parameter/Variable as a Table name "FROM ?". This won't work. A parameter needs to be a parameter.

If you need to use Dynamic table, you'll need to use Dynamic SQL. Something like:

DECLARE @sql VARCHAR(4000);

SET @sql = 'SELECT COUNT(*) AS recordCountDestination FROM ?';

EXEC(@SQL);

Personally I don't like that solution though, but I'm not a massive fan of "D-SQL".

Edit:

Another option, which personally I think would be better, is to have a SQL variable within SSIS, and execute that. That variable can create the SQL you need, and which you can easily execute in your SQL task.

For example:

"SELECT COUNT(*) AS recordCountDestination FROM " + @[User::TableName] + ";"

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

Or pass the table name to a stored proc, which returns the count as an OUTPUT parameter.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

There's no need for dynamic code. You just need to query a system view with a parameter.

SELECT SUM (row_count)

FROM sys.dm_db_partition_stats

WHERE object_id=OBJECT_ID(?)

AND index_id IN( 0, 1);

This will be faster and easier to parametrize.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

Thanks for the responses

Just about to try the SP with OUTPUT method

Both parameter based suggestions seem to continue to return the same error

hmm, looks like output doesn't work either

Bit frustrating as this seems pretty simple

All I want to generate a variable that holds a record count of a dynamically derived table so I can compare

Any further thoughts or pointers?

Thanks

- Damian

DamianC SSCrazy Eights Points: 8023 More actions · Answer 5

Finally working

Thom A, misunderstood your example part

Set the expression of the variable to

"SELECT COUNT(*) AS recordCountDestination FROM " + @[User::tableName] + ";"

Also set the property EvaluateAsExpression to True

Remove any Parameter Mapping

Left as resultSet = Single Row and under Result Set, set the count variable (recordCountDestination) Result Name to 0

So now, the result of the count is stored in recordCountDestination

Thanks all

- Damian

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

DamianC (11/1/2016)
Finally working
Thom A, misunderstood your example part
Set the expression of the variable to
"SELECT COUNT(*) AS recordCountDestination FROM " + @[User::tableName] + ";"
Also set the property EvaluateAsExpression to True
Remove any Parameter Mapping
Left as resultSet = Single Row and under Result Set, set the count variable (recordCountDestination) Result Name to 0
So now, the result of the count is stored in recordCountDestination
Thanks all

Well done on getting this working.

But note that the method described by Luis performs far better than SELECT *.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DamianC SSCrazy Eights Points: 8023 More actions · Answer 7

Fair point regarding the count method, Phil; and thanks for the code, Luis

Switched to the more efficient method and works

Thanks

- Damian