daniel-johnsson (3/8/2013)
Hi Phil,Yes that is exactly what I want to accomplish. Basically that I have Audit_ID with identity (1,1) that works as a PK in my Audit table and
my respective dimension tables contain Audit_ID columns that should work as foreign keys to the Audit_ID PK in my Audit table.
And just what you mentioned, I want to retrieve the Audit_ID from my Audit table and pass it on through the data flow to give that value
to my various dimension tables.
Also I should mention that I'm auditing every row that's passing through but the only value that seperates these is the Audit_ID column so I currently end up with
128 rows which is identical (except for the Audit_ID). Can I use a Row Sampling component to filter it down to just 1 row or will this become a problem?
Again, let me know if it's not clear what I currently have and what I want to accomplish. I'd be glad to provide the information if I can. Still rather fresh to both
posting in SSC and working with SSIS.
I think that you could achieve this as follows:
1) Create a package-scoped integer variable in SSIS - 'AuditId', perhaps.
2) Insert an ExecuteSQL task before your data flow - this will insert a row into the audit table and return the new AuditId into your AuditId variable.
So the SQL would be something like
insert dbo.Audit (col1, col2)
select ...
select scope_identity()
3) Now you can add a derived column to any subsequent data flow and set its value to whatever is in the variable AuditId.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.