Hi Jeff,

Hope you had a great X'mas Eve!

I have to agree with you, this whole code is like a every kid's dream ;-)!

It worked perfectly, even when I don't get whole understanding right now, but I will learn when practicing. I didn't even know about this "CROSS APPLY " and " Item = REPLACE" function. Thank you for teach me step by step like a kid, because for me most part of the code still very hard to understand.

Now, I would like to ask you how could I execute this SP in dozens of files within the same directory (c:temp in this case). Is there a way to call all the files within this directory regardless the file name?

I found the SP under Programmability, but not the table for the "GetFileType01", so I think we have to do an INSERT INTO SomeTable, am I right?

And finally, for some other files some fields will be bigger than 8K, specially one field that brings http adresses longer than 500 or 600 characters and I am having troubles even using the T-SQL or a SQL Server Import Export Tools, it comes with the error message:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "cDocumentName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "cDocumentName" (58)" failed because truncation occurred, and the truncation row disposition on "output column "cDocumentName" (58)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\MyDocs\SimpressDB\Equitrac\Equitrac.csv" on data row 2.

(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Equitrac_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

When I use a T-SQL transaction to do it, I found out the this field gets splited and part of it goes to the next fields, even using VARCHAR(MAX) or TEXT configuration in this field. I don't have the "header problem" with this files, but now I faced this problem with a very big/long information to insert in this other table field.

Always thank you, Jeff!

Andre