3

I have a CSV file which has salesforce data and is generated using python API call and i am trying to load the file in MS SQL DB using SSIS.

I need a C# script to rectify the issue because the data is not consistent (no of columns and order of columns can change anytime). Below mentioned are more details:

The file is not fully text qualified . There are few records which are text qualified and those records contains quotes/comma in them .

i tried to follow below blogs :-

But this logic works only when there are no quotes or comma in the text otherwise the program fails due to data disposition.

i checked few more articles on techbrothersit but i did not get any idea on how to deal with text qualified data. Could anyone provide a possible solution for this.

Update 1

I struggled a lot with this issue and thought of switching to excel but i am facing some issues with excel as well . i am importing data from excel source to oledb destination but it truncates the data to 255 characters whereas i need whole data. As a solution i do not want to try the registry edit as i will be deploying my program to production later and this impacts the performance. Won't be able to keep more length data in first row (data is coming thru API). So now the issue is that i want the data without truncation (using excel i was able to overcome the issues that were there in CSV) .

Any help with be highly appreciated.

Thank You in advance.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Amanpreet Kaur
  • 31
  • 5
  • if u are getting a `comma` in a cell data I don't think u will find a solution as it was comma separated values but last time when I get this problem we replace the comma with special char `<:>` something like this and write as csv and when ur inserting data replace this <:> again to comma – Avinash Reddy Apr 16 '19 at 11:21
  • 1
    Possible duplicate of [how to check column structure in ssis?](https://stackoverflow.com/questions/53096602/how-to-check-column-structure-in-ssis) – Hadi Apr 16 '19 at 17:46
  • If your data contains any commas I would call it junk data & require a different format. Another delimiter like a Pipe | or a complex delimiter like a GUID might be ugly but gets the job done. – Zakk Diaz Apr 16 '19 at 17:48
  • Please check the link mentioned in the comments i think it will solve the issue – Yahfoufi Apr 26 '19 at 11:13
  • 1
    Please post an actual sample of the issues you are facing. Are comma-containing data values always text qualified? If the number of columns and order of columns can change anytime, don't bother with SSIS, write something in C#. You're going to have to write a completely custom loading program to do this for you. – Nick.Mc Apr 27 '19 at 00:48

1 Answers1

1

SSIS is not geared toward this kind of problem solving - it is better suited to load static file structures repeatedly. To that point, the Connection Manager to a file is static - it never changes. Adding insult to injury, you have a delimited file that has no text qualifiers, so if a text value happens to match the column delimiter, then data is going to spill into other columns. This will effectively blow up your data import.

It is recommended that you swim upstream to work with the API generator to create standardized output files. Are you able to do this?

J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • Thank you . We tried hard coding the columns in Python API call which gets data from Salesforce and exported the data to CSV and now i'm able to load the csv using ssis. – Amanpreet Kaur Sep 19 '19 at 10:44