Pulling count of records from Teradata

Question

Post reply

Pulling count of records from Teradata

Prassad Dabbada V R

Ten Centuries

Points: 1251
More actions
December 18, 2012 at 3:38 am

#286400

Hi,
I am pulling count of records from a Teradata table.
I have used Execute SQL Task in SSIS to get the count, based on the count I want to proceed further.
while I am execuing the package I am getting below error
[Execute SQL Task] Error: An error occurred while assigning a value to variable "TeradataCount": "Value does not fall within the expected range.".
Note: I am using SSyou help is much appreciated
IS 2008 and ODBC Drivers for Teradata
Thanks
Prasad

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

What Data type is the SSIS variable TeraDatacount, and how many rows are you pulling through.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 2

Datatype is Int

I am assigning value of below query to the variable

select count(1) from Mytable

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

what scale is the int in SSIS, there are 5 maintypes INT16, INT32 and INT64 plus the Unsighed versions (UINT32 and UNIT64)

An Int16 can only hold numbers upto +/- 32767, an INT32 can hold numbers upto +/-2^15.

I would look to use the Int32 datatype in SSIS for this kind of thing unless you can guarantee that the row count will be less than 32000 rows of data.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 4

I have changed the datatype of the variable to Int64, still it is giving same error message

The actual count is 38044708, ans Int data type can handle this count.

declare @i int

set @i = 38044708

print @i

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

Sorry I thought the variable you were using was in the SSIS package not an SQL variable.

I take it the SQL Task you have just does

SELECT COUNT(1) From myTable

After that you have the SQL task set to use the result set and pass the value back to an SSIS variable

is that correct?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 6

Prassad Dabbada V R

Ten Centuries

Points: 1251

December 18, 2012 at 6:38 am

#1569540

Yes correct

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

I've just tested this and it shuold work with an INT32 datatype.

How is your tast set up? is it set to use Single or Full row count?

The Variable is set up in the Result set part of the SSIS SQL task, with the Result name set to 0 and Variable name pointing at the correct variable.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 8

This is working fine when the source connection is SQL Server or Oracle.

When I change the datasource to Teradata it is throwing this error.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 9

I'm going to have to bow out as I have limited knowledge of Teradata.

It sounds very much like an ODBC/Teradata driver error, have you tried the TeraData forums to see if anyone has seen this issue before with SSIS.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

PS : just had one thought of inspiration

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1a91b594-23b0-47f5-940b-4ee067f82fe2

Its using a different driver, but could still be a valid solution.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 11

Thanks Jason,

This could be good solution, I will work on this.

Thanks for your solution.

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 12

Prassad Dabbada V R

Ten Centuries

Points: 1251

December 19, 2012 at 1:52 am

#1569928

That worked perfectly Thanks Jason