Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pulling count of records from Teradata


Pulling count of records from Teradata

Author
Message
Prassad Dabbada V R
Prassad Dabbada V R
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 172
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3229
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
Prassad Dabbada V R
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 172
Datatype is Int

I am assigning value of below query to the variable

select count(1) from Mytable
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3229
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
Prassad Dabbada V R
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 172
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
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3229
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
Prassad Dabbada V R
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 172
Yes correct
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3229
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
Prassad Dabbada V R
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 172
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
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search