February 17, 2016 at 9:14 am
This feels like it should be easy but I'm struggling.
I'm importing a table from access. This needs to be appended to existing data in the destination DB and a DateStamp added. The user needs to be able to specify the value of the date stamp (it won't just be GetDate()) which will then be used for all records in the current import. Essentially I'm creating a dated history.
My plan was to give the user a parameters table in the destination DB with a field called ImportDate. The think is, I can't find an elegant way of "joining" it onto the import data. If I was doing this purely in a SQL query I would simply cross join to it but I can't do this as a SQL Query because the source and destination do not have visibility of each other. I can't use merges or look ups because there's no key to join on.
About the only way I can see to do this is to add a derived column with some arbitrary key value to both datasets and then use a merge join but that feels awfully clunky. Is this the right thing to do or am I missing the obvious somewhere?
February 17, 2016 at 9:27 am
I'm all for parameter tables. So that's a piece of my potential solution.
Read the parameter into a variable and then used a Derived Column transformation to get the value into your data flow. Once it's a column you can put it into the destination.
February 17, 2016 at 9:43 am
JustMarie (2/17/2016)
I'm all for parameter tables. So that's a piece of my potential solution.Read the parameter into a variable and then used a Derived Column transformation to get the value into your data flow. Once it's a column you can put it into the destination.
+1 – that's how I would do it too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 18, 2016 at 2:03 am
Thank you. That was exactly the obvious answer I was missing.:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply