December 12, 2012 at 4:23 am
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.
December 13, 2012 at 12:24 am
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
December 13, 2012 at 1:33 am
Thanks opc.
Each file is different but the load process is the same: reset the validto date, then load the new data and default the valid to date to the end of the century.
I have 21 'copies' of the sequence container and have had to rename the SQL task and Bull Inert Task within each one. The SQL Task is a dynamic script derived from the Task Name which is "tablename_date" with a (REPLACE(@[System::TaskName],'_date','') in the expression. The Bulk Insert task derives it's flatFileConnection from the Task Name. I was hoping to do it from the container name but it is no great hardship.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply