Import Column Transformation

I have a SQL Server table that has a "Text" type column. RTF files are stored in this column. On a monthly basis, new or updated RTF files are dropped into a shared folder. I need to upload these into the SQL Server table. Reviewing some of the entries in this forum, I know I probably should be using the Import Column Transformation, but for the last week I can't get it to work successfully.

Another column in the table contains the unique file name(varchar). At runtime the user inputs the fully qualified path to the RTF files which I store in an SSIS variable. Within the package I use a Derived Column Transformation to concatenate these two elements and then pass them as input to the Import Column. I'm expecting the RTF file to come out as output.

How does the Import Column match up the file with the proper table row? Do I need to provide the looping mechanism or does the Import Column handle that?

I've been searching for a working example with no luck. Can someone provide or point to a good sample?

Thanks.

It sounds like you are close to having it working.

The Import Column transform doesn't handle looping itself... it will process multiple rows, though. Each row can specify a different file name. That file will be read and the contents placed in the output column for that row. So, if you have all the file names at once in a table, you can process them in one shot with the Import Column.

However, you may need to be using a foreach loop container in the control flow to get your file names one at a time. In that case, the data flow with your derived column and import column transforms would need to be within the loop as well.

Let me know if this helps.

Thanks
Mark

|||Thanks Mark, that got me further along. I got passed some of the errors I was gettting. I think I'm getting closer after realizing that I need to manually set the "FileDataColumnID". Still having problems with the looping though. Have you done or run across any working examples? I think that would really help. Tom