Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing noisi commas in CSV file (SSIS)


Removing noisi commas in CSV file (SSIS)

Author
Message
damien.gulluni
damien.gulluni
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
I am quite new on using SSIS dtsx on SQL server 2005. I have CSV files that have been cleansed removing extra commas (in text fields) opening the CSV file in excel and using a replace command. The problem is that I cannot do the same thing in SSIS as the CSV file cannot be opened with the Excel Connection Manager.

Moreover, I also cannot use a transformation option as the error occurs directly in the Flat File Source.

How Can I handle this problem?

Thanks in advance for your help, Damien
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
Use a Flat File Connection Manager.

http://msdn.microsoft.com/en-us/library/ms140266.aspx

http://technet.microsoft.com/en-us/library/ms141668.aspx

http://technet.microsoft.com/en-us/library/ms139941.aspx

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
damien.gulluni
damien.gulluni
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
Hi Raunak,

First of All, thanks for your reply. I already tried with a Flat File Connection; but the error occurred directly on the Flat File Source.

The solution I am working one now is to use a script task to open the file in excel and use a macro to remove the noisy commas. But using SSIS 2005 I can't find Microsoft.Office.Interop.Excel.dll to use Interop within VB.net ...

Regards,
Damien
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
Can you post the error message here? How are you configuring the Flat File Connection Manager and why does the component throws error? Can you post a sample data with first 10 rows?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
damien.gulluni (10/7/2013)
Hi Raunak,

First of All, thanks for your reply. I already tried with a Flat File Connection; but the error occurred directly on the Flat File Source.

The solution I am working one now is to use a script task to open the file in excel and use a macro to remove the noisy commas. But using SSIS 2005 I can't find Microsoft.Office.Interop.Excel.dll to use Interop within VB.net ...

Regards,
Damien


Is there a text qualifier defined?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
damien.gulluni
damien.gulluni
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
Yes sure.
You will find here all the component I use:

- Flat File Connection Manager (format:delimited; text qualifier: "; header row delimiter: {CR}{LF};
- Flat File Source;
- Data Conversion Transformation (used to transform all the string to Unicode String)
- Ole DB Destination Editor (used to insert the data in database)

The error following error appears directly in the Flat File Source.
Error: 0xC0202055 at DFT_LoadingExcelSheets, Flat File Source [1183]: The column delimiter for column "Billing End Date" was not found.
Error: 0xC0202092 at DFT_LoadingExcelSheets, Flat File Source [1183]: An error occurred while processing file "\\cfsdata01\vmnc\RAID\Data Upload\Sources\Eboat\file.csv" on data row 1441.
Error: 0xC0047038 at DFT_LoadingExcelSheets: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Connection" (1183) 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.
Error: 0xC0047021 at DFT_LoadingExcelSheets: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at DFT_LoadingExcelSheets: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at DFT_LoadingExcelSheets: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at DFT_LoadingExcelSheets, DTS.Pipeline: Post Execute phase is beginning.



The row content is:
"toto","tata","tata""toto"", OJSC","tata","tata","CB ""tata"", tat","tt","tata","tata","59595","0","tata","0","","tata","tata","1","1200","tata","1200","tata","20.50","N","","2010/05/26 00:00:00",""
I changed the text values with toto tata due to some confidential information. But the problem is that some noisy comma are part of text.

Hope this helps,
Regards,
Damien
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
It seems to me your text qualifiers are the problem, not the commas.

Anyway, all of these issues can easily be avoided by choosing a decent delimiter.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
damien.gulluni
damien.gulluni
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
I fully agree when you have the control of the file produced .. The file comes from Business Object world and I have only choice between tab, semicolon, and comma which are all delimiter that can be used in the file I receive.

But for me the main problem is that the excel engine can handle that case while SSIS cannot.

Regards,
Damien
Raunak Jhawar
Raunak Jhawar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1944
As a good practice, you should use the Preview command while configuing the connection manager and tasks? What is the issue with Billing Date Column? It says missing column delimiter?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
damien.gulluni
damien.gulluni
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
Yes preview is cool, but I can only see the first 100 rows when the problem appears latter on the file.

Regards,
Damien
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search