I am trying to move data from one database to another and need to do some massaging of the data in a script component.
The thing is that, though I can declare all the fields in the component, it seems rather counter to the nature of SSIS to do that.
So I have a table that has roughly 20 fields. One of the fields is a file directory path that is created using our business layer in C#. I didn't write it and its actually not my side of the development game as I am more database.
Since we are moving the data to another database the file path is changing for each record.
Got it to the point that the singular value coming into the script component does indeed produce a valid file path for the record, but all the other information is not flowing through the script component.
Now I could declare the rest of the columns of the record in the Script component, but it seems like that goes against the nature of the program. Data flow after all.
So the order of steps that I am looking for are.. 1. Bring in a row of data 2. Massage only that one column value using the script component as it allows us to use the business layer 3. Update only that one column in the row 4. Insert it into the target database.
Most of this is easy to do but the script component is limiting the flow of the data.
Any way around this, or do I just have to suck it up and flow the entire table through the script component?
Thanks
Sorry if that was unclear. I found the solution though.
The key to getting all of your data through a script component is to not declare any outputs columns. If you declare any, then you have to declare all of them. If you don't then the component implicitly moves all the data through it.
Sorry for the confusion, it wasn't very clear trying to get that component going.