January 8, 2013 at 9:18 am
We are using a SSIS package to insert a series of CSV files (saved in excel) into a database table.
The problem is, the SSIS package inserts " quotes before every entry into every field imported.
There are no quotes in the CSV files at all, yet they appear in the database.
The SSIS package uses a Multiple Flat File connection, Row delimiter is {CR}{LF}.
Is there a quick and easy way in SSIS package or in the database to stop the quotes from being inserted?
thanks
January 9, 2013 at 1:03 pm
Which transformation and destination components are you using?
January 10, 2013 at 1:48 am
You will need to set a text qualifier on the source csv file connection manager.
Excel will auto remove them when looking at the csv file as it knows " is a text qualifier, I bet if you open the file in notepad they will show, or even using the preview screen on the connection manager.
January 10, 2013 at 3:40 am
jerry-621596 (1/9/2013)
Which transformation and destination components are you using?
In my data flow, I have a Flat File Source which connect to a
OLE DB Destination
anthony.green (1/10/2013)
You will need to set a text qualifier on the source csv file connection manager.... I bet if you open the file in notepad they will show.
Yes, you are right, I can now see the quotes in notepad.
As for the Text Qualifier... In the Multiple Flat Files Connection Manager Editor, under the General tab, the Text qualifier box was set to <none> I have tried putting in a quotes " and double quotes "" to no avail - the import job fails (if thats what you mean?).
And inserting quotes in the text qualifier also warns me that 'the flat file parser does not support embedding text qualifiers in data...'
January 10, 2013 at 3:44 am
If it speach marks " (double quote), the text qualifer should just be a speach mark, you dont want to wrap it up into a string so '"' (single, double, single)
If that doesnt work are you able to share the file so I can take a look.
January 10, 2013 at 3:57 am
I have tried just " on its own, and that didn't work - import fails.
example of the content (when the database is set to limit data entries to 9 characters):
"AB123AB"
"AB12 AB"
an example of the import (when the database is set to limit data entries to 8 characters):
"AB123AB
"AB12 AB
above examples are when there is no text qualifier.
With " (1 set of double quotes) it fails.
Also tried the above with the database character limit to 7 , 8 and 9 - still either shows quotes or import fails.
here are 2 example files (as a series of these csv files are being imported)
http://www.sypensions.org.uk/webcomponents/ab.csv
http://www.sypensions.org.uk/webcomponents/b.csv
- it is just the first column which is being imported.
thanks.
January 10, 2013 at 4:07 am
mrichardson 57577 (1/10/2013)
I have tried just " on its own, and that didn't work - import fails.example of the content (when the database is set to limit data entries to 9 characters):
"AB123AB"
"AB12 AB"
an example of the import (when the database is set to limit data entries to 8 characters):
"AB123AB
"AB12 AB
above examples are when there is no text qualifier.
With " (1 set of double quotes) it fails.
Also tried the above with the database character limit to 7 , 8 and 9 - still either shows quotes or import fails.
here are 2 example files (as a series of these csv files are being imported)
http://www.sypensions.org.uk/webcomponents/ab.csv
http://www.sypensions.org.uk/webcomponents/b.csv
- it is just the first column which is being imported.
thanks.
Ah post code data, what a lot of fun I have had in the past getting this kind of information.
Looking from the data though there shouldnt be an issue in getting that imported.
Can you attach your SSIS package solution as a zip file, or atleast the CREATE TABLE command for the table your inserting the data into.
January 10, 2013 at 11:16 am
If you would post a sample of the csv file, and a create table script, I will see if I can figure it out. Sounds like a setting issue in one of the components.
January 11, 2013 at 1:22 am
Hopefully resolved this via PM, took a look at the OP's SSIS package and was what I looked at setup incorrectly.
Detailed to use a ForEach loop to pick up each file individualy and import, as the flat file connection manager was looking at *.csv instead of ab.csv etc.
January 11, 2013 at 1:25 am
Thanks for reporting back what caused it. Helped me with something else entirely.
January 11, 2013 at 10:06 am
thanks for the help with the SSIS package.
I'm a newbie at SSIS and not sure what to enter in the variable mappings box?
Name...
Namespace...
Value Type...
Value...
also,
i'm not sure if its best to edit your solution or try and replicate what you have done on my own solution.
I have tried to drag a control flow on to the stage - is it best to drag it on to the control flow or data flow area?
January 14, 2013 at 1:25 am
I would strongly recomment reading through the stairways section on SSIS.
But every task is control flow.
Only task that is data flow enabled is a DFT.
I would strongly recommend trying to re-create it that way you can understand what is happening and you have a better chance at supporting the package rather than saying it works but I dont know how.
January 22, 2013 at 4:37 am
I've had a look at the stairways section on SSIS, and re-built the package from scratch.
I can now import some of the postcode data into my SQL table successfully without the " speech marks!
However, the loop imports the same single file about 60 times and doesn't import any of the other files.
It imports the file I specify in the Flat File Connection Manager (e.g. ab.csv).
In my loop Collection properties I have specified my folder where there are about 120 csv files and in the Files box I have entered *.csv
I have created a variable name with index of 0 (but not using this variable name anywhere else in any expression).
January 22, 2013 at 4:48 am
Have you set the connection manager of the flat file source to use an expression on the connection string of the variable?
That will ensure that the filename on the connection manager changes with each file it finds.
January 22, 2013 at 4:55 am
In Connection Manager >> Properties >> Expressions >>
I click on the ellipses and then on the next screen on the Property column drop the list down I have chosen ConnectionString.
I can see my variable and select it, but when I click on OK I get this error message:
Expression cannot be evaluated
There was no expression to compute. An Attempt was made to compute or get the string of an empty expression.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply