Hi All I have created a parent SSIS package using BIDS on Sql Server 2005 sp3 . The package has a for each loop container that loops on an object variable. Within the Foreach loop is a execute package task that calls my child package. The child package currently has 2 script tasks and an execut SQL task. The on the first loop everything runs correctly. But for every loop afterward the script tasks are not executed only the SQL task is executed and then it finishes and moves onto the next loop. I found some information related to a different problem about setting the property "OptimizeScriptExecution" on the script task to False. However this has had no effect. Why does this happen? Has anyone encountered it before? Many Thanks TimTim

what is being done in ur scrips? are you enabling and disabling controls on the run? If you are doing that use precedence constraint instead of diabling and enabling tasks

I am using the script to update variables in the parent using the following code: Dts.Variables("Max_Occurrence").Value = Dts.Variables("Child_Max_Occurrence").Value Tim

how do you say that the script has not executed? if that would be the case the child package execution would be incomplete.

Both script tasks are green (successful) as soon as the child package is called again. The SQL task goes Yellow (Processing) immediatly basically skipping the script task. When it is finished with the sql task and the sql task is successful it exits the child package and moves to the next loop skipping the final script task. The Child package is structured as follows 1) Script task copying parent variable data to child 2) Sql Tasks assigning result to child variable 3) script task copying result from sql task in child variable to parent variable. Eventually it will do much more but I need to get the hteory to work first. Thanks Tim

Could you provide us with an image of how all is set please? PS: I assume you do not use precedence constraints?Arthur My Blog

The tasks are all linked in a flow so that would be precedence constraints wouldn't it?. This is my first time on MSDN, how do I provide the image? Also to set your expectations I have been using SSIS for around 3 - 4 months so still relatively new, so don't assume my knowledge, I could be easily missing something that I know nothing about and everybody else does. Thanks Tim

The tasks are all linked in a flow so that would be precedence constraints wouldn't it?. This is my first time on MSDN, how do I provide the image? Also to set your expectations I have been using SSIS for around 3 - 4 months so still relatively new, so don't assume my knowledge, I could be easily missing something that I know nothing about and everybody else does. Tim, Re #1: I basically needed to know if there is a flow or there is a flow + precedence constraints, so it seems you only used the flow; Re #2: There is a FAQ section where there are posting tips with links to free image hosting/sharing services, you can use SkyDrive. The design you came up seems a bit odd to me (this does not imply you are wrong), but looping to execute a package may be not needed.Arthur My Blog

Ok I am trying to be able to get a screenshot shared but our webfilter is blocking the skydrive. I have requested access so hopefully, if it gets approved, I'll be able to show something later today. RE #1: Yes there is a flow. I have not specifically set any precedence constraints Let me also provide a bit of background as to what I am doing and why. This is difficult to explain so I am sure there will be a number of questions laer. I have a pretty large ssis package that is used to process records to generate fact rows to be used in a cube. Alot of actions need to take place to get the data in the correct format and structure. The other complication is that the table is a "ledger" however each row in the "ledger" merely holds an updated view of sales orders each time the sales order is changed rather than a truely ledger entry showing the adjustments to the original record. E.g. if order 11 had a cost adjustment due to more items being added on from £100 to £120 it would show as follows: Order - Cost 11 - £100 11 - £120 instead of giving the adjustment so when aggregated would reach the correct result: order - cost 11 - £100 11 - £20 So these ledger entries need to be generated. To do this I look up to an aggregated view of the fact table and determine what the adjustments are and then create a new record in the fact table with only the adjutsment, so the fact table aggregates to the correct result. Another complication I have is that there can be multiple updates per data load performed from the source system. I need to process each update to each order line seperately otherwise the ledger entries could go out of sync. To do this I have a script that determines how many times an order line appears in the data load and provides these with an occurrence id ordered by the update date and time. I then loop around the occurrences across all orders e.g. Order - line - value - occurrence 111 - 1 - £100 - 1 333 - 2 - £50 - 1 111 - 1 - £102 - 2 555 - 3 - £20 - 1 333 - 2 - £60 - 2 111 - 1 - £103 - 3 So what I would do is have an sql task that now determine the max occurrence number and then do a for loop on each integer from 1 to the max. Using a variable I would select the records with each occurrence number in turn. e.g. all the 1's first then the next loop process the 2's and so on. What this allows me to do is seperate updates of specific lines but still process as much data as possible at once. There are many lookups and other transformations that then occur to get the data in the correct format for loading into the fact table. Recently I have hit an additional issue. When trying to load the fact table initially, from our staging table (which contains 58m rows), I was receiving locks, it seems to do with resources on the SQL server. Now our DBA is looking into this problem, but he too is new to SQL server. I don't think the locks are specifically being caused by my ssis package being too resource hungry, more there seems to be some sort of a restriciton on the sql server. We have multiple processors and 20 GB of RAM and when we hit problems on the SQL server the operating system is still showing plenty of resources available. While he figures that out, I thought it a good idea to try alternate approaches that may help to reduce the impact this ssis package has on the SQL server resources available. Hence me looking into this approach. I don't know if it will help, but I always think it is good to try different things at it aids in learning. My theory, which very possibly could be wrong, is that by spliting my current single ssis package to multiple ssis packages and calling them is that it will use less resources at any one time as it will hopefully release them sooner. Currently the ssis package i use now seems to hold onto a large amount of memory. Although this was slightly improved with the installation of sp3. Even if this isn't the right approach for this specific issue, I still need to be able to overcome this problem I am having as I do need to pass variables between my packages (which I know is possible through other means, but I have seen posts advising that a child cannot update a parents variables, which I need to do) and it also concerns me that a child package placed in a loop will not action the script tasks after the first loop. In this test package that I have created to try out this idea I am doing a for each loop as mentioned earlier and the execute package task sits within this loop. The reason for this is that I have split the data in the staging table down to sudo "data packages" by supplying multiple package ids against the rows in the staging table and processing each package id at any one time. Each "data package" contains around 1 - 2 million rows. I thought this again would aid in helping the initial load, but in any case moving forward this is the approach we are using for delta loads so I need to loop across all unprocessed "data packages" one at a time. Then we come to the steps as described above where the script tasks will run on the first loop but on the second loop they are just ignored. I hope this helps in some way in understanding the issue. I know it is alot of information. I hope at somepoint to be able to provide the screen shots to give some visual context. Thanks so far for the responses. As usual the IT community are a friendly and helpful bunch! Tim

Here are the screen shots: http://cid-e9293dc843b067c6.skydrive.live.com/redir.aspx?page=play&resid=E9293DC843B067C6!117 Hopefully they show in order. If not check the names of the images or order by name. Hope this helps in understanding what is going on. ThanksTim

That does help, I wish I could see what is inside the scripts, but I think I got a hunch: you need to understand that in SSIS the variables get evaluated each time the execution goes through them (sorry for the lack of better terms), so I suspect somewhere this spoils the whole pot. In short, please verify your logic (how you work with the variables) keeping in mind the above.Arthur My Blog

I think I understand what you mean. The variables do get changed by the script tasks and hold their values. The script tasks just don't get run again after the first loop. The script tasks don't depend on the variables or their values, they simply pass the value from the parent variable to the child and vice versa. Here is the contents on the script tasks script 1: ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' ' Add your code here ' Dts.Variables("Child_Package_ID").Value = Dts.Variables("Package_ID").Value Dts.TaskResult = Dts.Results.Success End Sub End Class script 2: ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' ' Add your code here ' Dts.Variables("Max_Occurrence").Value = Dts.Variables("Child_Max_Occurrence").Value Dts.TaskResult = Dts.Results.Success End Sub End Class Tim

OK, at this stage start decomposing you package, I also suggest to add logging to each component. This way will narrow down to what is the culprit. A good place to start is probably to drop the SQL Task, but leave the Scripts, then remove one other script.Arthur My Blog

Ok Thanks I'll give that a go and let you know what I find.Tim

Sorry I haven't replied for a while on this. I had been pulled onto an urgent project so could check this out. I am doing some testing and thought I would post the results as I went along. I have removed the SQL task. The child package again ran fine first time, but second time it doesn't even enter the child package with only the 2 script tasks. The execute Package task just immediatly goes green. I'm going to try a few more things out. Tim

Ok I have figured out it is not that the task isn't running on every loop after the first, it is the command that is not running inside the script. I added 1 script in the child package and added an addition Hello world message box. The Hello world message box comes up on every loop, however the over line of code fails to update the variable. So at least I have indicated at a lower level where the problem is actualy occuring.Tim

Ok I have finally figured out tht it is working. It seemed like it wasn't because the debugger doesn't stop at the break points after the first loop. I had a watch on the variable but nothing seemed to be changing. It's still wierd that the breakpoint stop working after the first loop but at least I have confirmed that it is working correctly. I did this by setting message boxes and outputing the contents of each variable after my code changes it. each time the variables changed correctly. I suppose I just have to implement different tactics of debugging for this sort of thing. Thanks all for your replies!Tim