Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
on7june
Starting Member
41 Posts |
 Posted - 2008-07-31 : 05:42:18
|
| Is there a way to disable packages at runtime?Sarvan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-31 : 05:43:38
|
| runtime? you mean while executing or when called from job? |
 |
|
|
on7june
Starting Member
41 Posts |
Posted - 2008-07-31 : 06:24:27
|
| To understand better i shall explain my task. I have a parent package which in turn calls the child packages (Both parent and child packages are stored in the same folder). To perform this i am planning to use ForEachLoop container which browse the folder to find files with extension .dtsx and i have execute package task in it which will execute the current package the foreachloop container fetches. (Till now i have not succeeded using that) I have an execute sql task as the first component in all the child packages. When the query in that executed and the result is 1 then that package should be disabled else it can proceed with the execution. If u find any other possible to way to perform this task please share.Sarvan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-01 : 13:28:13
|
quote:
Originally posted by on7june
To understand better i shall explain my task. I have a parent package which in turn calls the child packages (Both parent and child packages are stored in the same folder). To perform this i am planning to use ForEachLoop container which browse the folder to find files with extension .dtsx and i have execute package task in it which will execute the current package the foreachloop container fetches. (Till now i have not succeeded using that) I have an execute sql task as the first component in all the child packages. When the query in that executed and the result is 1 then that package should be disabled else it can proceed with the execution. If u find any other possible to way to perform this task please share.Sarvan
first of all you can just give the list of packages inside for each loop using list enumerator and then use execute package task inside loop provided you know the packages earlier i.e they are static. The second part of your problem can be implemented with the help of precedence constraints. you can specify an expresion their and do conditional execution absed on reult of expression. just store result of stored procedure in a variable created in pacakge and use the variable in expression to eveluate to 1. then it will make sure other tasks are executed only when its 1. |
|
|
|
on7june
Starting Member
41 Posts |
Posted - 2008-08-04 : 01:13:36
|
| Thanks a lot will have a try on this today.Sarvan |
|
|
|
on7june
Starting Member
41 Posts |
Posted - 2008-08-11 : 08:29:16
|
| I have a problem in my ForEachLoop container only first package is executed and not the others. This is how my task works.I have a sql table where it stores only the name of the package but not the path in one field (Package) and another field which stores its type (Group).In SSIS i have an Execute SQL task. Its result set is "Full result set". The source variable is "varsql". In Result set the Result Name is "0" the variable name is "Varloop". This is the query i am using "SELECT 'C:\Test\' + PACKAGE AS PACKAGE, [GROUP] FROM Tbl_Name" so the result set of this query (path of each package with its type) is stored in the variable varsql. Let us assume it returns 5 rows each represents individual packages which is to be executed.Next is the Foreach Loop Container. The enumerator type is ADO Enumerator. The ADO object source variable is Varloop Enumeration mode is "Rows in the first table". Variable mappings is For "VarPackage" the index is 0 and for "varGroup" the index is 1.Within the Loop container i have an "Execute SQL Task". The connection type is ADO.Net and the SQL statement "SELECT IsNull(MIN(CONVERT(INT,RUN_IND)) ,0) as RUN_IND FROM Tbl_Nam WHERE [GROUP]=@VarGroup" thisreturns either 0 or 1. It results 0 If any of the value for RUN_IND is 0 for the current type which is stored in @vargroup or It results 1 when all the values for RUN_IND is 1. In the parameter mapping the variable name is varGroup, Direction Input, Data Type String, Parameter Name @VarGroup and Parameter Size -1. In Result set the Result Name is 0 and Variable Name is VarPackageDisable.After this i have an Execute Package task. The connection manager is created for this with Existing file (First package to be executed) this task has an expression "(DT_BOOL) @[User::Varpackagedisable] == False ? False : True" If the result is false package will be enabled else it will be disabled.For the conenction manager used for execute package task i have an expression where the connection string is given thru the variable "VarPackage" (This holds the full path of the packages)When i see the execution of my package after entering in to the loop for the first time the execute sql task works and the package name is stored in the variable "VarPackage" and the execute package executes that particular package perfectly. After this again the execute package task continues its execution but the Execute package task doesnt work for the rest and after 4 times of execution (since we have 5 packages) of execute sql task the control transfers out of the loop and rest of my task executes. At which part i have made a mistake?Sarvan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-11 : 08:34:53
|
| can you put a breakpoint on preexecute event of foreachloop and check if you're getting correct values for all variables during subsequent loop executions? |
|
|
|
|
|
|
Disable packages