Using SSIS package not executing tasks in proper depandant sequence.

Using SSIS and in my ForEach Loop Container I have a two objects: 1st a Data flow task which is link to my SQL Task. The dataflow task is reading the data from 4 csv files from a network drive an writing those row into a SQL table. The problem is the four files are being processes in the for each loop completely before the stored proc is executed in the SQL Task even though their both in the Control Flow. I need my SQL task which is a stored proc to run after each file has been processed. I want the loop to complete each tasks in sequence before cycling throw to the next file. I have set up the data flow and the SQL task with the dependancies. I have set up the value property on the precedence Constaint to = "Success". Also, the execution results tab (display) is saying that it is executing 4 times successfully and independantly, howerver, it is actually not running at all. (the files on the network are not being moved). When I execute the stored proc in Managment Studio it runs perfectly.

Good Q. I don't have a job running this yet. I am still at the stage where I am executing the package myself. Permissions were not all there before and I would get an access error when running the package, so I put proper permissions on the network folder and then ran the stored Proc myself and ran the SSIS package and the proc ran successfully from Management studio and also the package ran without errors. (this all after granting myself and user 'SQLServer' the permissions. )

Also, in the results tab of the package after it runs it shows the data flow task running through the four files before trying to run or validate the SQL task. So it's sequence isn't correct. It is not trying to run the SQL task after/for each file, but only after the data flow is completed all four files.

If you have the Data Flow Task set as a Success Precedant Constraint to the System File Task, and both of those are in the same For Each Loop Conatiner, I am having a hard time figuring out how the execution order you are indicating can be happening.

Are the files not appearing in the target folder?

Do you have full permissions on the folders in question? (You need to be able to modify as well as read the source folder.)

The quirky thing I have noticed about the Progress Log for SSIS packages is that it is not a strictly chronological log. Instead, it groups all actions by the component that performs them and then logs them chronologically. (It's kind of wierd but, I suppose, it makes sense to someone at MS. ;-) Anyway, the way I would interpret the cluster of 100% completions is that the data has been imported and then the SP has been called . . . period.

Does the file move occur in your SP? If so, you should probably modify the SP so that it checks for an error and, if there is one, raises an exception. Alternatively, you can use a File System Task to perform the move, rahter than using the SP. Using the File System Task will move all of the action into the SSIS package and you can trap the error there.

In general, I suspect that the SP is not successfully executing whatever sript you have for moving the files and you are missing the error (possibly because your SP ends with RETURN instead of RETURN @@ERROR).

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

I believe that the filename should show up instead of the ?; however, a bit more information would help.

Please understand that the following questions are intended to use you as my fingers in checking what I would check if I had the SSIS package on my system and do not reflect any discounting of your abilities. ;-)

Can you provide a screen shot of the Variables (expanded to allow all of the details to be read)?

Can you provide a screen shot of the Properties of the For Each Loop Container?

Can you provide a screen shot of the Edit form of the For Each Loop Container?

CAn you provide a screen shot of the Properties of the Execute SQL Task?

CAn you provide a screen shot of the Edit form of the Execute SQL Task?

I kind of think you may be correct in thinking that the SP is SQL Task is just not getting the filename. I have generally found it better to use a File System Task to do things like copying or moving files and, in fact, I tend to take the conservative route and do a copy and then a delete of the file. That let's me disable the delete and make sure that the copy is happening before actually nuking the files. ;-)

My next hour will be spent looking at the process flow in detail. I think the entire process (reading the data from 4 csv files inserting the records into SQL and coping the csv files and saving them to the archive folder and deleting them from the source folder ) should be taking more than a split second. Or course. It is not actually doing the move of the files. But, I don't think it waits to pass the varible file name to the SQL task.

I want to add another task that provides another precedent contraint on the data move task before it proceeds on with the iteration. Maybe I can put a watch on the varible @FileName.

I'd start by changing the FileName variable so that its scope is the For Each Loop Container.

In the BIDS editor, single left click on the Execute SQL Task and then go to the menu and, under Debug click on the Toggle Breakpoint option. That should put a red dot in the upper right corener of the Execute SQL Task. Now run the pkg in the BIDS editor.

When the Breakpoint is hit, you can right click on a blank area of the image and then select Add Watch. That will let you select the User::FileName variable and add it to the Watch List window. At this point, though, that variable should have something in it . . . i.e. the Filename that is about to be handled by the Execute SQL Task.

Diver, thanks for the continued assistance. I am new to SSIS so this is all helpful. I have followed your instructions and the value of the variable is the entire path (with the file at the end.). I don't know how to set the FileName variable to just the value of the file name and not the entire path.

Diver, I decide to go back to the package that was using the file system task. I got that working after purchasing a book "Hand-on Microsoft SQL Server 2008 Integration Services". I know this has been like working with a real newbie, thanks for all your help! :-D

To be honest, I much prefer using the File System Task . . . reference my response #35148630. ;-)

I'm just glad you got your pkg working and doing what you need.

One more point, though, you should probaly cultivate the habit of giving the various components more meaningful names. I sort of abbreviate the default name (e.g. I would probably name the File System Task something like FST Move File to New Location).

I would also suggest getting "Microsoft SQL Server 2008 Integration Services: Problem - Design - Solution" when you get a chance. Also, look at the PragmaticWorks web site (just type thename and hit <ctrl><Enter> in a browser's address bar ;-) for their BI-XPress and BI-Documentor tools . . . there is a free download of each sort of like SS Express.

The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability.
Flipping Coins in Excel: Enter =RAND() into cell A2:
Recalculate the random variable…

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…