I assume you mean that you want some data in SQL Server to be updated. SSIS can certainly do the required data manipulation but you need to solve the problem of automatically executing your SSIS package whena change occurs to the source. SSIS executes reactively, not proactively.
Regards
-Jamie
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
