Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I use the container name in an expression for a control flow task Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 4:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.
Post #1395583
Posted Thursday, December 13, 2012 12:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1396012
Posted Thursday, December 13, 2012 1:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.

Post #1396049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse