06-13-2013 05:28 AM
A few months ago I set up a straightforward process that took two permanent tables (e.g. registered as metadata and stored in library) and used a SQL join transformation. I then right clicked on the table icon and selected register to create another permanent table with the result of the join. I would now like to come back and modify the process to put another step in after the join and before loading to premanent dataset. however now when I delete the link and try add in the step (splitter transformation) I am told that "splitter requires a physical table or work table" Is there a way without rewriting the join transformation that I can modify the existing.
06-13-2013 06:31 AM
I'm not sitting in front of DIS right now, but if I recall right:
As a best practice, it's maintain work tables (views) with the transformations, and use a Table Loader transformation for the target table. It makes the flows easier to adjust ;-)
06-13-2013 07:45 AM
I can get the work table back following your instructions, thanks. My inconvenience now is that all the formual I had in he expression columns have gone. I assume this is because they were mapped to the permanent metadata object. but before I replace al 600 of them can you think of a way for me to salvage them using DI studio?
06-14-2013 09:00 AM
Well, if you expressions are without linebreaks, and you are sure that you have the same sort order of the columns, you could just copy and paste the expressions. A bit risky, but could probably save some work...
06-14-2013 09:04 AM
Unfortunately there are alot of case statements that when I copy else where go into multiple lines. Mentally preparing myself to do it now. Seems like a good task for a friday afternoon. Thanks for the help though I certainly will always use the table loader going forward.
06-14-2013 09:09 AM
Just take a deep breath...
For future considerations, doing ETL and DW is far more easy if the data is transposed, leaving fewer columns to maintain in metadata.
Have nice evening!