July 11, 2012 at 7:50 am
Hi
I am loading data from a flat file to a table. The problem is the data from one source column (string [dt_str]) shows up as either zero's or one's in the destination column (string [dt_str]). Am I missing something or do I need to convert that data in that column?
July 11, 2012 at 8:19 am
What is the data in the source? I assume not 0's and 1's.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 8:38 am
Hey Jack
The data in the source is numeric although there are some null's and zeros too.
EB_RECENT_HIGH_CREDIT
80600
10100
100
4000
July 11, 2012 at 10:32 am
Does your Flat File Connector have that column properly set as string?
Can you show us an example of File to Table conversion? (I can't see 4000 only showing up as 0s or 1s).
How wide is the column in question? (How much of those numbers is being read by the Flat File Connector?)
July 11, 2012 at 3:10 pm
Does your Flat File Connector have that column properly set as string?
--Yes it is setup correctly as string.
Can you show us an example of File to Table conversion? (I can't see 4000 only showing up as 0s or 1s).
--select eb_recent_high_credit from gdsland where EB_RECENT_HIGH_CREDIT >= 0
eb_recent_high_credit
0
0
0
0
0
0
0
0
0
0
0
0
How wide is the column in question? (How much of those numbers is being read by the Flat File Connector?)
---In the flat file source editor I see the length of the source and destination columns is 1 which may be the problem
July 13, 2012 at 5:19 am
jdbrown239 (7/11/2012)
Can you show us an example of File to Table conversion? (I can't see 4000 only showing up as 0s or 1s).--select eb_recent_high_credit from gdsland where EB_RECENT_HIGH_CREDIT >= 0
eb_recent_high_credit
0
0
0
0
0
0
0
0
0
0
0
0
Note quite what I meant. I need to see the original values compared to what is being "translated" to 0s and 1s.
FileValue TableValue
XXXXX 0
Where XXXX is the value from the file.
How wide is the column in question? (How much of those numbers is being read by the Flat File Connector?)
---In the flat file source editor I see the length of the source and destination columns is 1 which may be the problem
I think that's exactly the problem. Your column lengths are all wrong and the package is grabbing the last digit of the number. You should be able to go into Columns on the file connector and see how things are laid out. There will be a vertical line that separates all the columns. Then fix everything in the Advanced tab.
July 18, 2012 at 10:47 am
Hi
Sorry for the delay in response. I was able to correct the issue by changing the data type of the flat file conversion to (DT-I4). Thanks for all the help 🙂
July 18, 2012 at 11:28 am
Glad you got it working. SSIS can be frustrating at the best of times.
July 18, 2012 at 1:12 pm
I am finding that to be true, thanks 🙂
July 19, 2012 at 4:30 am
Theres a option called Suggest Types under file connection which will scan the records of your file and suggest a datatype , that is really useful , y ou could use that.
July 19, 2012 at 5:00 am
Will it scan all the records or just the first 10-50?
July 19, 2012 at 1:39 pm
I will remember that next time. I used a conversion chart at this link http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
July 19, 2012 at 11:20 pm
Brandie , you can specify the no of rows you want it to scan.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply