aaron.reese (12/12/2012)
Hi guys,I have an SSIS package that currently bulk inserts 21 files using SQL Tasks that all call a stored proc. The sp takes 5 variagles (db, schema, table, sourcefilePath, formatfilePath) and generates dynamic SQL which is then executed. I am trying to replace these with 21 Bulk Insert Tasks.
Before each Bulk Insert there is a requirement to update some of the records in the existing data. The update is the same for each table (UPDATE <tablename>, SET ValidToDate = now() WHERE ValidToDate = '20991231')
I would like to wrap the two processes in sequence container and name the container with the tablename to be updated and then refer to the sequence container name in an expression. The idea is to make future development easier; if a new table is required then the current sequence container can be copy-pasted and the container name changed and hey-presto! the new file is added to the SSIS.
Is what I am trying possible? I can't see the container name exposed as a variable in the expression builder.
Do all 21 files all have the same schema? Will all future files have the same schema? How do you know the values of sourceFilePath and formatFilePath? I am wondering if 21 Sequence Containers is overkill and that it could be done dynamically using only 1. The answers will determine which solution I may propose.
In the meantime, to answer your question about attaining the container name, no, not without some custom coding to store then iterate over all the Package's objects using the system variable @[System::ParentContainerGUID]. I am still puzzled as to why Microsoft provided the parent containers GUID and not the Name. Not to mention that getting the name of a container from its GUID is not trivial, at least it wasn't when I looked into it about a year ago.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato