Hi Guys,
I
have a flat file which is loaded into the database on a daily basis.
The file contains rows of strings which I load into a table,
specifically to a column of length 8000.
The string has a length of 690, but the format is like 'xxxxxx xx xx..'
and so on, where 'xxxx' represents data. So there are spaces, etc present in the middle.
Previously
I used SQL 2000 DTS to load the files in, and it was just a Column
Transformation with the Col001 from the text file loading straight to
my table column. After the load, if I select len(col) it gives me 750
for all rows.
Once I started to migrate this to SSIS, I
allocated the Control Flow Task and specified the flat file source and
the oledb destination, and gave the output column a type of String and
output column width of 8000. But when I run the data flow task it
copies only 181 or 231 characters out of the 750 required.
I feel it stops where it finds the SPACES and skips the rest.
I
specified row delimiters or CR, and LF. I checked the file under
UltraEdit and there were no special characters in the file that would
cause the problem.
Any suggestions how I can get it to load the full data
Thanks
SSIS Data Flow task fails to load all text from flat file
Hot Topic
- how to loop through several excel sheets in a file in integration services
- Who Run The Job!
- XML sample code for metadata
- I can no longer connect to my SQL Server Express 2005 from Manager Studio Express
- application failover error
- Package templates
- Help using the formulas in report builder
- MAX, subquery, TOP or something else??
- Problem performing a join on a function in a SQL query
- LocalReport - ClickOnce(Request for the permission of type)
SQL Server
- Hierarchal data reports
- What's In A SQL Transaction Log File?
- microsoft visual studio cannot shut down because a modal dialog is active
- Permit user to select columns for a report: why not use Dynamic SQL?
- Need help with pivot or is this even possible using pivot?
- Report Model Expression
- Cannot configure SQL Server Express 2005 for remote connections
- Folders for instances
- two dimensional array using form designer
- Tool for MySQL to MS SQL Server 2005
SSIS Data Flow task fails to load all text from flat file
swingme
-In the Flat File Source, in the preview, I seem to be able to view most of the data of the text file, i.e. much more for each row than is actually stored in the table
-I put a Data View in the transformation, and in the dataview it has just the 181, or 231 bytes of data.
So, I guess in the transformation the data is being truncated for some reason I checked again under Ultra Edit and cannot see any funny characters that could cause the row to jump to next.
It seems to do this at areas where there are plenty of spaces.
71M
For the Flat file source, in the error output -> Error -> redirect row, truncation ->redirect row
I redirected them to a table i created to store the error column, code, etc.
But when I run the transformation, there is nothing in the error table. It succeeds.
FilthyZombie
-There is only one column which I load into the table
-The column in the table schema has length 1000
-Each row of the text file (which forms the one column) is 750 bytes long
-Meaningful data finishes after 690 bytes, after which there is a filler of spaces till it gets to 750
The delimiter to get to the next row is [CR] or [LF].
So, the string in the first two rows could be, as an example:
'20051188399848hdyjknwierhhq iouowiejfiowef 9747-27427 '
'20051188399848hdy iouowiejfiowef 9747-342350727 '
So I would want all that to be loaded into the table in the one column.
Unfortunately when I load it in SQL 2005, via SSIS it loads first 181 or 231 bytes and not the whole 750.
When I run the DTS via the Maintenance -> Legacy in 2005, it is successful, but not via SSIS.
Does that help slightly Would you like me to post a sample of the actual file and the package that I am trying to use
mikebk
That shows an output of 50 bytes, though... Don't use the advanced options for now... Just use the columns option. Delete the flat file connection, and recreate it using flat file or ragged right.
One other thing, it shouldn't be CR *OR* LF as the row delimiter... It should be one or the other for the whole file, or both together on each line. So either your file has CR as the row delimiter across all rows, or LF, but not both, unless it's a CRLF on each row. I'm not saying that's the issue here, but I just wanted to be clear.
Your DTS task will be more forgiving than SSIS.
Dave Foderick
Pati123
Saurabh, you mentioned a transformation now. Can you outline the flow of your DTS package I know I can take a fixed width text file (of 1000 + bytes) and import directly into an SQL table without a problem...
Just make the package really simple, 1 Flat File Source to 1 OLE DB Destination. You can always hook the transformation up later after you make sure the flat file is being read correctly...
Sounds like you might need to start over...
Create a new SSIS package (or delete the Flat File Task and Flat File Connection Manager that you already have).
Drag Flat File Task onto Data Flow tab (it should prompt you for the File Connection Manager info)
When File Connection Manager Editor opens, choose ragged right (under format) and choose the location of your file.
Choose Columns (left side of Editor). You should see a preview of a few lines of your data. Scroll to the right and click on column 750 (this should place a vertical line at column 750).
Now choose Advanced (left side of Editor), there should be a Column 0 and Column 1. Click on Column 0 and the OutputColumnWidth should indicate 750.
Connect the Flat File Task to your OLE DB Destination and double-check the mappings. It should work. If not, there is something else that is not working...
Jassim Rahma
In the OLEDB Destination, there is an error output link. Should I use that
Peppermint
I'm a little confused...
You mention rows of strings. Is each string 690 bytes long or is each row of the input file 690 bytes long Are you loading multiple columns from each row of the input file, or just one column from the entire row You mention loading the string(s) into a column of length 8000, if so, how are you getting the len(col) = 750 from the 690 byte string
Can you post how wide (how many bytes) your input file is, what the delimiter of the 690 byte string is and the structure of the table you are loading
Malacki
Try recreating the flat file connection using fixed width.
The other thing you need to do is redirect to see if there are any error rows getting kicked out.
OPH
I checked the file under Ultra Edit and there are no escape characters that I can see.
The same file does load under SQL 2000 DTS, so it technically should work here too.
I have uploaded this screen shot so you can have a look:
Dunno if it works.
Fusion54
Again it gives the same result.
I am analysing the file again in Ultra Edit and noticed that at some places, there are spaces used as '00' in HEX and in some places are used as '20'.
Is '20' in HEX not a valid character The truncation seems to happen here.
JIM.H.
Everytime it loads the same length of text in the table. Never the full required text.
Debabrata.debroy
Hex 00 is NULL. Hex 20 is a space. 20 is valid, 00 is probably not, but then you know the data best. We cannot speak for your data.
dzimmy
When you double-click on your Flat File Connection Manager (not the Flat File Source Task) and choose Advanced, it should be showing two columns Column0 and Column1 (one for data, one for cr/lf). If you then choose Columns (from Flat File Manager) there should be one line running vertically at column 750.
Only other thing I can think of is that there are escape character in your file. If it is always loading only a certain number of bytes, you might want to look at the data in the columns surrounding where it stops to see if there are any anomolies there...