Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I use the container name in an expression for a control flow task


Can I use the container name in an expression for a control flow task

Author
Message
aaron.reese
aaron.reese
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 900
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9599 Visits: 14375
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:TonguearentContainerGUID]. 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
aaron.reese
aaron.reese
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 900
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search