June 5, 2012 at 3:03 am
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.
June 5, 2012 at 4:19 am
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
June 5, 2012 at 5:01 am
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
June 5, 2012 at 5:06 am
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
June 5, 2012 at 6:17 am
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?.
June 5, 2012 at 6:31 am
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
June 5, 2012 at 6:49 am
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?
June 5, 2012 at 7:11 am
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
June 5, 2012 at 10:17 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply