Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pulling count of records from Teradata Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 3:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
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
Post #1397659
Posted Tuesday, December 18, 2012 4:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 895, Visits: 2,442
What Data type is the SSIS variable TeraDatacount, and how many rows are you pulling through.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1397689
Posted Tuesday, December 18, 2012 4:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
Datatype is Int

I am assigning value of below query to the variable

select count(1) from Mytable
Post #1397692
Posted Tuesday, December 18, 2012 4:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 895, Visits: 2,442
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
Post #1397697
Posted Tuesday, December 18, 2012 6:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
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
Post #1397740
Posted Tuesday, December 18, 2012 6:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 895, Visits: 2,442
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
Post #1397750
Posted Tuesday, December 18, 2012 6:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
Yes correct
Post #1397761
Posted Tuesday, December 18, 2012 6:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 895, Visits: 2,442
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
Post #1397773
Posted Tuesday, December 18, 2012 7:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
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.
Post #1397800
Posted Tuesday, December 18, 2012 7:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 895, Visits: 2,442
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
Post #1397802
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse