ETL Guidance

Hi,

We've decided to use SSIS for an ETL type project. We are going to convert some files to our special flat file format. Our special flat file format will not change, but we will need to convert about 40 different style flat files into this format. We will receive any of these files at random intervals. We will likely have to add more file formats to the 40 at some point, too.

I'd like to use a flexible design, but I just started with SSIS and its going to be hard to test all the possible combinations, so I hope I can find some guidance in this forum.

What would be a good approach to allow for the most reuse of work to support these various file formats?

Jamie Thomson's excellent SSIS blog has an "SSIS Best Practices" list that might be a good place to start: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx

|||At worst you'll need to make a converison from each style of incoming file to your formatted style. more details would be helpful|||Thanks, I read Jamie's blog everyday and I recommend it to everyone, too.sql

ERROR: Using Flat File Connection Manager Editor with delimited format

Hello everyone:

I am new with SSIS and I have a problem that I don’t know solving it.

I have a simple package with a delimited flat file source which is loaded into a table in a SQL Server database.

Below is the import format:

- Row delimiter: carriage return and line feed {CR/LF}

- Column delimiter: Comma {,}

- Text qualifier: “

In the source file, the data looks like this:

“string1”, 34, “”, , ,” string2 “text1” string2” , ““,

This package with DTS works, but now with SSIS does not, when I see the “previous rows” in the Flat File Connection Manager Editor, the last column has incorrect information and when I create a new file without spaces and without twice “” in the same string, it works. I don’t know what I am missing with SSIS.

Regards,

Fanny Tejera

Well, technically double quotes inside a field should be escaped with another double quote.

"string1 ", 34, "", , , "string2 ""text1"" continued string2", " ",|||

Yes, but I do not have the control of the information that contains these source files. Can I fix this problem with the Flat File Connection Manager Editor? Am I missing something with SSIS?

|||

See below post where Donald Farmer answered a similar question

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=546729&SiteID=1

SSIS does not support embedded text qualifiers in this version. However, you can look at these sample components that I mentioned elsewhere recently ... http://www.microsoft.com/downloads/details.aspx?familyid=b51463e9-2907-4b82-a353-e15016486e1d http://www.microsoft.com/downloads/details.aspx?FamilyID=9c624eab-6893-4734-b5dd-f80d0d487aa1 http://www.microsoft.com/downloads/details.aspx?familyid=91e24d1d-acf0-45b1-a9a5-9ea1c7bd0463 Donald Donald Farmer, Microsoft -- www.pamboli.com -- www.beyeblogs.com/donaldfarmer - Ranjeeta

|||

Thank you so much!

Fanny Tejera

|||

Hi Fanny,

Even i too have the same problem with delimiter.

I tried it by using the same links.

but i am getting the error right at the source file during validation. can you please explain me how to use the Undouble component.

Thanks in advance,

Anand kumar

|||

Hi

even i too have the same problem. I tried it with the same links you have provided. But i am getting the same error at flat source file.

i have downloaded, installed and build the components.

when i am using the undouble or undouble component it could not remove the ", i am getting the error at flat file source during validation itself. can you please reply me as soon as possible.

can you please tell me how and where to use the undouble component.

Thanks in advance,

Anand

|||

I installed the undouble but getting the error that i can't use it with DT_NTEXT?

How to solve this or is there a other solution. I must place it in DT_NTEXT because the field is longer then 8000.

In access i can import it in a memo field without any problems.