August 7, 2008 at 11:35 am
Whats the best way to import flat files into a table while dynamically replacing or removing unwanted characters in some of the columns that are being imported. Ideally I would like to do a bulk insert task but it looks like I need to massage some of the data before trying to insert it into a table. I have text and numeric columns of data but some of the numeric data ends up being "****" instead of "6345" in the flat file. This makes the bulk insert fail everytime since its a datatype mismatch. I thought I might be able to do this with the derived column object but haven't had any luck yet. My next stab was going got be trying the Script Component object. Is there any other tools that would lend themselves to this type of dynamic transformation that I might be overlooking? 🙂
August 7, 2008 at 12:16 pm
If you've already tried format files with the bulk insert then using the Script Component will work too.
An example of what the process would look like:
[Flat File Source] ---> [Script Component] -(data viewer)-> [Row Count or Destination]
Set the Script Component as a Transformation, then change the output within the script.
For example, to change every occurence of "a" to "b" in Column1:
Row.Column1 = Replace(Row.Column1,"a", "b")
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply