May 1, 2011 at 9:46 pm
Hello All,
i have to extact the data from flat file and load the data into the sql server 2005
the data is like
Type from Thru Size Description
char 1 3 3 Claim_Prefix
Zoned 4 9 6.0 Claim_Number
zoned 10 12 3.0 Claimant number
when i used flat file source and derived column.
i got error like [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
can you Please tell me what is the right way to extract from flat file and load into the sql server.
how i use data coversion transpomation or script task?
Please help me in this scenario.\
" Note: zoned is excel data type it is a similar to decimal"
Thank you
May 3, 2011 at 9:30 am
I think you are on the right track. The most direct way to extract data from a flat file into SQL Server 2005 using SSIS is to add a data flow task to the control flow. Then open the data flow task and add the flat file source and SQL Server destination. Configuring the data sources may be where the issue lies. Question: the data you provided appears to be a delimited file. Do you know if it is tab-delimited or space delimited (not common, but this is what it looks like the way it is pasted in)?
Here are a couple things to look at in the meantime:
As you configure the flat file connection manager in the editor there are four categories down the left side.
Under General - Make sure first row has Column Name in the First Data Row is checked (assuming your first row is headers)
Under Columns - This will give you a rough view of the columns and data (if you don't have the column checked in the step before you will see Column 0, like your error, if it is checked you will see your headers). Another question, now is all your data in one column or is it in multiple columns? If it is all in one column your delimiter is not set up correctly. Select the Reset Columns button and this will allow you to change the column delimiter (which will likely be tab or space) and click Refresh. Now you should hopefully see your data in rows and columns that make sense.
Under Advanced - You can now select each column and choose the data type and precision (these should ideally match your SQL Server table)
Under Preview - take another look and make sure everything looks correct
Next connect your flat file source connection to the sql server destination connection with the green arrow.
Configure your SQL Server destination. It is usually best to create the SQL Server table yourself. Otherwise, if SSIS creates the table it will be guessing at the data types and precision. Select the proper table and then click on Mappings and verify all the fields are connected from the source to destination.
This will hopefully help you figure out where the error resides. If not please follow up with additional errors or information.
May 5, 2011 at 7:08 am
The chances are that the destination column data type is overflowing.
Check the destination column data type with the column data type in the pipeline. There could be a mismatch somewhere. Use the appropriate transformation to match the destination data type or change the destination data type to match the source.
You may also try to change the data type under advanced tab in Flat File Connection Manager Editor.
May 19, 2011 at 12:14 pm
Thanks. i got it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply