Column Row Delimeter Problem

I'm trying to import a comma delimited text file into a SQL table-

The row delimiters I am assuming are {CR}{LF}

When I try to open up my file in Excel, the data file parses perfectly.

When I try to port it over in SSIS, I get an error:

"The column delimeter for column <my column> was not found."

"An error occurred while processing the file <my file> on data row 2076"

I've tried looking at that data row, and i am having a hard time finding anything wrong with the row.

Anybody know of any good ways to debug that?

n/m- I figured out my problem- the data was buggy- it had dual double-quotations- yet the double-quotations are what signified text qualifiers- and SSIS was not correctly picking up the text qualifiers correctly.

How do you get SSIS to understand quotes if the text qualifier is a quote?

Collations and simple import from a csv-file

Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?

I appreciate all help.

Regards

Thomas Ivarsson

You can control the data types and lengths that SSIS uses for flat files by going to the Advanced area in the flat file connection manager. It will show you the columns defined, their data types, and their lengths, and you can alter the values to match what you want.

Parameter support in the data reader source: I don't believe that it does, but you can create the SQL that you want by building it in a variable, then assigning the variable to the Data Reader Source.SQL Command property using an expression.

|||

Thank's for the answere jwelch. I have actually tried the advanced properties and have set the flat file connection data types according to the target table data types.

Perhaps I have another error but this configuration will work?

I will have a look at your advice regarding the data reader source.

Regards

Thomas Ivarsson