Preprocessing Files SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Preprocessing Files SSIS

Preprocessing Files SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Jonathan Marshall

Posted Wednesday, February 06, 2013 8:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 11:07 AM
Points: 93, Visits: 279

Hello everyone,
I'm trying to process files that have NUL characters w/a black background.
Looks like a block. The client wants them to be just kicked out as a bad row.
Some of them I can get kicked out. Other files completely fail the process with various columns being overflowing the disk I/O buffer. For tesing purposes I set the datatypes within SSIS to an Output width of 5000 along with the database being varchar 5000.
Does anyone have any suggestions?
Researching the issues and workarounds I see that some preprocessing can be done.
Has anyone ran into this type of error?
Would not copy and paste and I do not see where I can import the image outside of a hyperlink.

Let me know your thoughts on this....

Post #1416543

Jonathan Marshall

Posted Wednesday, February 06, 2013 9:16 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 11:07 AM
Points: 93, Visits: 279

So it looks like the file has \0 when I search within Notepad++ when changing the Search Mode to Extended
Continuing to search

Post #1416584

Jonathan Marshall

Posted Wednesday, February 06, 2013 9:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 11:07 AM
Points: 93, Visits: 279

Basically I have to see about removing the NULL character from the files in some type of preprocess...

Post #1416600

opc.three

Posted Thursday, February 07, 2013 7:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 6,730, Visits: 11,781

I have had to deal with that in the past. They were flat files coming from an AS/400. My requirements said to replace NUL with a space (ASCII 32). I dealt with it in C# by reading the file byte-by-byte and doing the replacement as I wrote a new file. When done I deleted the original and carried on with processing the now-fixed-up file.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1417079

Jonathan Marshall

Posted Thursday, February 07, 2013 9:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 11:07 AM
Points: 93, Visits: 279

Indeed.
I was able to use a C# script to read line by line and remove NUL value characters from the file.
Now I'm able to load the files into database and push error(s) into an error logging table with the process continuing on.

Post #1417153

« Prev Topic | Next Topic »

Permissions