July 16, 2018 at 11:19 am
I am running into an issue and I can not figure it out.
I have an SSIS package I am working on where I am taking a csv file we get from a third party. (File looks like this "FirstName", "LastName", etc). The header row is the first row in the file, and the 2nd row and beyond is all data. Every item including column names has a double quote around it.
I have connection manager to the flat file that is set as comma delimited, and text qualifier being " . In the preview for the file feed, all off my column names, and all the data look good with no quotes anywhere in the preview.
For some reason when I do a bulk insert task using this connection manager file, i will go back and look at the data imported, and all of the data will have double quotes still.
I am not sure what I am missing here.
Any thoughts?
July 16, 2018 at 11:31 am
Interesting... this web page has that as the solution. You might want to check to verify that the change to use " as the text qualifier was actually saved...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 11:56 am
i found that earlier. I am using a Bulk insert task to import the csv file into the table. I just dont under stand why the preview in the connection manager looks fine with no quotes, but when I actually execute the task and go look at the data inserted into the table, it is including the double quotes.
July 16, 2018 at 1:50 pm
gothaimviii - Monday, July 16, 2018 11:56 AMi found that earlier. I am using a Bulk insert task to import the csv file into the table. I just dont under stand why the preview in the connection manager looks fine with no quotes, but when I actually execute the task and go look at the data inserted into the table, it is including the double quotes.
And come to think of it, I'm pretty sure I saw another post somewhere in this site that had the same problem, and I'm thinking that was one solution, and I can't remember the other.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 3:49 pm
Interesting. Every time I see something like this, the more happy I am that I don't use SSIS ;-). A BCP FORMAT FILE and the use of BULK INSERT would eliminate such problems in fairly short order.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 1:18 pm
Jeff Moden - Monday, July 16, 2018 3:49 PMInteresting. Every time I see something like this, the more happy I am that I don't use SSIS ;-). A BCP FORMAT FILE and the use of BULK INSERT would eliminate such problems in fairly short order.
Why is this interesting? So you don't know SSIS or you just dislike it? So you suggest BCP Format File or Bulk Insert but don't offer any basics or advise? This user is probably stressing out and frustrated but you find this "Interesting"...
July 17, 2018 at 1:26 pm
Chris Hurlbut - Tuesday, July 17, 2018 1:18 PMJeff Moden - Monday, July 16, 2018 3:49 PMInteresting. Every time I see something like this, the more happy I am that I don't use SSIS ;-). A BCP FORMAT FILE and the use of BULK INSERT would eliminate such problems in fairly short order.Why is this interesting? So you don't know SSIS or you just dislike it? So you suggest BCP Format File or Bulk Insert but don't offer any basics or advise? This user is probably stressing out and frustrated but you find this "Interesting"...
The reason I find it "interesting" is because SSIS is supposed to make things easy for users that may not know other methods. It supposedly can handle text qualifiers but that apparently doesn't work for the OP. It's also interesting that people would even bother with SSIS for such a simple import of such a nicely laid out file. And, it's interesting that no one has been able to help the OP, including you. 😉 Instead, you're trying to crown me because I find all that "interesting" even though I mentioned a possible alternative that the OP hasn't said he'd be willing to try never mind even asking what it is that I'm talking about. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 1:55 pm
well in theory the SSIS import of the file and using the " as a text qualifier should eliminate the issue, and it does when previewing the data etc, but for some strange reason (still looking into it), the data gets imported with the double quote.
What is the BCP file etc you mention?
July 17, 2018 at 2:19 pm
gothaimviii - Tuesday, July 17, 2018 1:55 PMwell in theory the SSIS import of the file and using the " as a text qualifier should eliminate the issue, and it does when previewing the data etc, but for some strange reason (still looking into it), the data gets imported with the double quote.What is the BCP file etc you mention?
Okay, Jeff, here is your opening.
July 17, 2018 at 3:20 pm
gothaimviii - Tuesday, July 17, 2018 1:55 PMwell in theory the SSIS import of the file and using the " as a text qualifier should eliminate the issue, and it does when previewing the data etc, but for some strange reason (still looking into it), the data gets imported with the double quote.What is the BCP file etc you mention?
One of the denizens of this forum has a wonderful quote in his signature line that states something like "In theory, practice and theory are the same. In practice, they are not". 😀
Neither BCP nor BULK INSERT are designed to handle CSV files and MS states that in BOL. And, yet, for a well formatted file as you have mentioned, both are remarkable in what they can do especially when you get what is known as a "BCP FORMAT FILE" involved, which is the hardcoded method for mapping the file to the table as you might in SSIS. Even without that (someone might not allow you to store a BCP FORMAT FILE although such a restriction would be a matter of preference rather than a practicality), some rather simple things can be done without it.
Rather than provide you with a generic example of what to do and then let you possibly struggle to "get it right", if you could provide me with the "Record Layout" for the file and the "CREATE TABLE" statement for the target table, I'd be happy to provide you with some tested code along with an explanation of what it all is and how it works (and, although it's not a click'n'drag GUI, it's simple). If that's not a possibility, then check out the following links.
http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/non-xml-format-files-sql-server?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017
I've not included links for either BCP (I suspect that you don't want to do any of this using the command prompt or the command shell) or the XML style of BCP FORMAT FILES because I've found them to be unnecessary complex and require you to do double-identification that's unnecessary in the non-xml format styles.
Also, if you CAN use a BCP Format File, then use the tried and true but undocumented backslash double-quote character combination to use the double quotes as a part of the field terminators. You have to do a special trick with the first quote of each line to get the system to ignore it by counting it as a field in the file that has no target column in the table.
All of this is incredibly easy to do when you know how and usually difficult for most folks that haven't done it before, which is why my offer of tested code help was originally made and still stands. I just need the information that I requested to give 'er a start.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 6:55 am
Hi All.
SSIS looks for text qualifier as the character next to the column delimiter.
If there is anything extra between the two, such as a space, it won't recognize the text qualifier character.
To work around this, you can use the advanced tab to set the column delimiter to include the extra space. That eliminates the gap and SSIS will then properly 'see' the text qualifier.
Hope this helps.
July 18, 2018 at 8:01 am
sestell1 - Wednesday, July 18, 2018 6:55 AMHi All.SSIS looks for text qualifier as the character next to the column delimiter.
If there is anything extra between the two, such as a space, it won't recognize the text qualifier character.To work around this, you can use the advanced tab to set the column delimiter to include the extra space. That eliminates the gap and SSIS will then properly 'see' the text qualifier.
Hope this helps.
Thanks for the input, but I dont think this is the issue as there is no space. The file imports fine into SQL as a new table from SQL, and shows up fine in the previews within SSIS, but the actual data when imported through SSIS and the bulk insert seem to leave the double quotes. 🙂
July 18, 2018 at 8:32 am
gothaimviii - Wednesday, July 18, 2018 8:01 AM... The file imports fine into SQL as a new table from SQL, ...
I'm afraid I don't understand what you are saying here. Could you clarify?
Is there a case where the data did import as expected?
July 18, 2018 at 8:40 am
gothaimviii - Wednesday, July 18, 2018 8:01 AMsestell1 - Wednesday, July 18, 2018 6:55 AMHi All.SSIS looks for text qualifier as the character next to the column delimiter.
If there is anything extra between the two, such as a space, it won't recognize the text qualifier character.To work around this, you can use the advanced tab to set the column delimiter to include the extra space. That eliminates the gap and SSIS will then properly 'see' the text qualifier.
Hope this helps.
Thanks for the input, but I dont think this is the issue as there is no space. The file imports fine into SQL as a new table from SQL, and shows up fine in the previews within SSIS, but the actual data when imported through SSIS and the bulk insert seem to leave the double quotes. 🙂
Like I said, if you were to post the record layout for the file and the CREATE Table statement for the target of the import, we might be able to fix it in short order.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 4:13 pm
See this http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/25/sql-server-2008-sp1-cu-6-includes-small-changes-to-dtsx-files.aspx
I distantly recall running into this. I think if you view the properties of the object rather than opening up in the GUI you can edit this properly.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply