Very weird transformation problem..

[gyfu]

I have a very weird data transformation problem..

I have been using a DTS to transform a text file into a table. The Text file usually has about 700K records. It seems to be doing fine everyday, except today when it transforms only 345K records.

However, if I do repeat this process manually a few times, out of 5 tries, I will manage to transform a 100% at least once.
Anyone has any idea, what is going on? I want to try and check the text file but it is too huge for any application to open it.

Please advise. Thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[AlexCuse]

What are your transformations doing, and how is the file delimited?

Can you create a table with all varchar fields (ie the 'Create' option when you choose your destination) and then do a straight column to column transformation successfully?

MOre info would help very much.

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[SBTBILL]

Have seen similar issues when the table being imported contains an apostrophy. For instance O'Shay will cause a problem.

Bill Couture

 

[gyfu]

Ok.. I apologize for the lack of information.
There are no special cases here. just as you mention it is a straight column to column transformation.

Funny thing is that when i do another transformation using the import wizard and creates a table automatically, it works fine, but on my dts, it doesn't anymore. I am pretty sure nothing has change since it last work.

I am just so confuse.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[micha123]

Is the file local or is it on the NET?
If it is not local - try to copy it locally and retry.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[AlexCuse]

Does your DTS generate an error log?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[micha123]

I would also try to recreate all transformations from scratch and see if it helps.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[gyfu]

When i do it from scratch and just the transformation, it works. The file is local on the server and on my pc.

I just tried to log it, but when i did run, they was no error as the dts claims that the transformation is a success.

I also did try to use a different version of the DTS, my back up, and that too didn't work.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[AlexCuse]

Try bringing it into a new table with new transformation as you say was successful. Then compare this table with table from the 'failed' load, and see if you can identify a problem with the rows that did not get in.

I kind of suspect a delimiter issue (ie a vbCr rather than CrLf). Has the source of the file changed (is it coming from a different server or application?)

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gyfu]

Oh sorry, I forgot to mention that this is a fixed length file. I strongly believe it is the file, but I have to find where the problem lies so that I can prevent it in the future.

and yes Alex, the source file changes everyday. it is a daily download file.

I am debugging in what you are suggesting so far.

thanks for the opinion guys..


-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[AlexCuse]

I have had tab delimited files coming off a mainframe that because of line delimiter issues end up wrapping to the max width allowed in a text file. This crashes my DTS because it's not allowed to fail a single row.

As far as source file, I am asking has the source of the file changed?

Does the data that does make it into the table look complete?

Good luck,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gyfu]

Issue Resolve. yeah Alex, I think my issue was very similar to yours. After carefully re doing the transformation, I realize that the file used to have 61 columns, but now it has only 60 columns. Since I did not remapped it, I believe SQL screws it up..

Anyways, all is well now.. Thanks you guys for your efforts.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[micha123]

...And usually by re-doing all the transformations as I suggested - you would discovered that quicker... ;-)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.