0

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

10thTiger
  • 95
  • 2
  • 10
  • I don't believe I understand the question. You may expose no columns, one column or all columns to a script component depending on the needs. You can update existing columns in place or add new ones to the buffers. – billinkc Aug 11 '15 at 01:21
  • You have some library that you wish to use to provide a new value for one of your columns as you migrate the rest of data intact to a new destination. If that's the case, then 95% of your ETL is just EL. That's not unusual so I'm not sure where the concern is. I will point out that depending on how you're consuming your business object layer, if it's direct reference to an assembly, you'll need to ensure it's strongly signed and part of your deployment process for SSIS packages. If it's a SOA, then you're good to go. – billinkc Aug 11 '15 at 01:24
  • I guess you need to decide how much performance impact you can absorb before this process is no longer practical. If it gets unusable in future will you have the luxury of rewriting it (for example not using a script task at all). Most importantly is there any way you can pre-cache all the file paths or is the nature of the source data too random to do that? It comes to maintainability (central business logic) or performance - you usually can't have both. – Nick.Mc Aug 11 '15 at 01:36

1 Answers1

1

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.

10thTiger
  • 95
  • 2
  • 10