June 9, 2008 at 6:23 pm
I have a OLD DB Source using a query to extract data. Within the query, I am using specific convert statements to get the correct datatype that I wanted. See query:
SELECT
CASE
WHEN LEN(EmpId) = 1 THEN '0000' + CONVERT(CHAR(5), EmpId)
WHEN LEN(EmpId) = 2 THEN '000' + CONVERT(CHAR(5), EmpId)
WHEN LEN(EmpId) = 3 THEN '00' + CONVERT(CHAR(5), EmpId)
WHEN LEN(EmpId) = 4 THEN '0' + CONVERT(CHAR(5), EmpId)
ELSE CONVERT(CHAR(5), EmpId)
END AS EmpId,
CONVERT(DECIMAL(12,6), FTE) AS FTE,
CONVERT(CHAR(6), YearMonth) AS YearMonth
FROM Table1
ORDER BY EmpId
In old DTS, it would generate for the most the correct datatype on the transformations. In SSIS, I notice no matter what I do, it took the underlying columns datatype with some exceptions it seems. For this query, EmpId becomes DT_STR(9), FTE becomes DT_R8, and YearMonth becomes DT_R8.
This has caused a bunch of issues.
1. When the destination was to a text file.
Instead of xxxxx, it was xxxxxXXXX for the EmpId
2. When I destination was Sql Server Destionation, it complained about converting from DT_R8 to DT_STR.
3. I'm not even checking yet to see if DT_R8 makes me lose any precision/scale on the FTE column.
I'm looking at adding some steps in the middle to do additional data manipulations, but hopefully somebody has better answers.
Thanks in advance!
June 10, 2008 at 12:56 am
hey,
Why dont you first write a query, "select distinct len(emp_id) " to check at the various lengths that you have?
Possibly the length is being reflected as the maximum length.
Also, in your text file connection manager, you can specify the length that you want to transfer
~PD
June 10, 2008 at 4:39 am
pduplessis (6/10/2008)
hey,Why dont you first write a query, "select distinct len(emp_id) " to check at the various lengths that you have?
Possibly the length is being reflected as the maximum length.
Also, in your text file connection manager, you can specify the length that you want to transfer
~PD
I'm not sure what you mean. I'm specifically using convert to specify the length. I actually wanted it to go to a SQL Server Destination which was given me the error. I was using the Text File connection as a test to review the output.
June 10, 2008 at 5:31 am
If the info is not sensitive, please post an example of the source, and the desired destination
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply