I have source coming from TSv format(CSv).In one column I have multiple values, which has to go to multiple columns in the target table. all the values in a single column are separated by commas. Do I have to write script for this or do I have to use 2 dataflow tasks. can anybody give any suggestions?
You can achieve this easily with single data flow task: First You select the Tab Delimiter. This will Divide your source into the four columns. Then in the Connection Manager Editor you switch to Advanced where you can define different delimiter for each Columns. As in Example Described. If you have 5 columns (Column0 - Column4). The Column0 - Column3 will have the Tab as delimiter. For the Column4 you change the delimiter to comma. In addition you add additional Columns (Column5 - Column7). The Column5 - Column6 will have also comma as delimiter and the last Column7 will have RCLF as Delimiter. **But Now, if you have a Header row in the CSV, the Header have different structure and you cannot use it.** You have to set **Header rows to skip** to 1 and also uncheck the "Column names in the first data row".