Issue while reading .DAT file in SSIS 2005

Question

Issue while reading .DAT file in SSIS 2005

hamsa_apj

SSC Eights!

Points: 825
More actions
June 5, 2012 at 3:03 am

#274994

Hi,
Iam trying to read .dat file in SSIS (SQL server 2005) and map the values to database table columns. Inconsistently, Data inside the .dat file are not read in the proper format.
Example:
Original data in .DAT file:
123:34:45:67 XX 00001234 20120406
Data received during Improper file read:
123:34:45:67 XX 00001234 20120406
Actual characters in the.dat file is 1024.
Please advise.Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

quoted and coded to make it more obvious.

so what is the issue? the whitespace is changing from the source? is this a fixed width file, or could you consider it a space or tab delimited isntead?

hamsa_apj (6/5/2012)
Hi,
Iam trying to read .dat file in SSIS (SQL server 2005) and map the values to database table columns. Inconsistently, Data inside the .dat file are not read in the proper format.
Example:
Original data in .DAT file:
123:34:45:67 XX 00001234 20120406
Data received during Improper file read:
123:34:45:67 XX 00001234 20120406
Actual characters in the.dat file is 1024.
Please advise.Thanks in advance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hamsa_apj SSC Eights! Points: 825 More actions · Answer 2

Thanks for your response.

Its is fixed length file and am splitting the data using subString(), to map the data from .dat file to database table columns. Since position of the data is changed, wrong data gets inserted into database columns.

But am facing this issue inconsistently, so not am able to identify the root cause of the issue.

Please do needful

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

hamsa_apj (6/5/2012)
Thanks for your response.
Its is fixed length file and am splitting the data using subString(), to map the data from .dat file to database table columns. Since position of the data is changed, wrong data gets inserted into database columns.
But am facing this issue inconsistently, so not am able to identify the root cause of the issue.
Please do needful

If it's a fixed-width file and the positions are changing, this is the responsibility of the creator(s) of the source file, not yours. Get them to fix it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hamsa_apj SSC Eights! Points: 825 More actions · Answer 4

I would like confirm one more clarification. Actually in my scenario, multiple .dat files are read in fraction of seconds.Is there any possibility of timing issues, which would cause the datalost while reading the .dat file in SSIS?.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

Presumably you keep the files? If so, you can check for yourself, surely?

Or are you saying that the files themselves are fine and that the errors are occuring elsewhere in the chain?

You need to be more specific in explaining what the errors are exactly (post error messages) and where they occur.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hamsa_apj SSC Eights! Points: 825 More actions · Answer 6

Scenario:

multiple .dat files are placed in the shared location. In my SSIS package, looped the files in the directory and read the files with the data file source and hold the whole file content (actually am having single line with 1024 columns) in a String(DATA_STR). Then with use of Split-column, DATA_STR is split using subString() and the mapped to Database columns.

Content in the file themselves is correct. But when i read the contents in SSIS and mapped to Database columns, values are different from the actual values in the original dat file.

There is no complicated calculations in my SSIS packages(jus read the file from location - Split columns - map to Database columns)

Please advise. Is there any possibility of timing issues / performance issues, etc?

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

May I ask why you are not using a fixed-width flat file source connection in SSIS - that will do all of the splitting for you?

To answer your question - I would say 'unlikely' to both suggestions, based on the facts as you have presented them.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hamsa_apj SSC Eights! Points: 825 More actions · Answer 8

Hi Phil,

Thanks for your quick response.

Similar Data issue am facing in another SSIS package which has Data File Connection with default Header row delimiter (CR-LF) with column delimiter (LF). In this SSIS package also .dat file is the input and whole data in the file content are read in DATA_STR variable and using split columns substring() is used for DATA_STR and values are mapped to Database columns.

If i re-run the SSIS package for the same .dat file, file contents are read correctly and columns are correctly mapped.

Dont have any clue to resolve this issue. Please advise