CODEWITHSUNDEEP
ssis
Akshay
Akshay

Reputation: 559

Query works in SSMS but not in SSIS

need help guys. I am trying to use the following query to get the data fom a table. It works in SSMS , but in SSIS throws an error.

SELECT  CONVERT(VARCHAR(10), GETDATE(), 120) AS col1
UNION ALL
SELECT  CAST (CustomerID AS VARCHAR)
FROM    tCustomer
UNION ALL
SELECT  CAST(COUNT(*) AS VARCHAR) AS col1
FROM    tCustomer

Throwing the error

[OLE DB Source [1]] Error: There was an error with output column "col1" (35) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.".

What I am trying to achieve is 

Header line yyyy-mm-dd
 --e.g. 2013-07-01
Data records
Trailer record
---e.g. 13480

Where am I going wrong ?

Upvotes: 0

Views: 522

Answers (1)

makciook
makciook

Reputation: 1555

The error you see happens due to data incompatibility.

In first CONVERT(VARCHAR(10) you specify the length to 10

MSDN says, that default varchar size is 30, so doing a CAST(x AS VARCHAR) equals CAST(x AS VARCHAR(30)). So here we have length of 30

And finally you have a column, which length is 20, and here we have a "potential loss of data"

Upvotes: 1

Related Questions