Solved

Using SSIS package not executing tasks in proper depandant sequence.

Posted on 2011-03-15
14
810 Views
Last Modified: 2012-05-11
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.

I have no idea what else to do.   Thanks for any help you can provide.

-ExpertUser
0
Comment
Question by:ExpertUserId
  • 8
  • 6
14 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35142543
Have you checked to make sure that the agent being used to run the job has sufficient permissions to be able to move the files?
0
 

Author Comment

by:ExpertUserId
ID: 35143098
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. )
0
 

Author Comment

by:ExpertUserId
ID: 35143130
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.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 125 total points
ID: 35148630
Can you provide a screen shot of the SSIS package's diagram?

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.)
0
 

Author Comment

by:ExpertUserId
ID: 35149075
Yes. I have attached a screen shot of the package diagram.  Correct. The files do not show up in the target folder but remain in the source folder.

Also here is the progress tab at the end afer all four files have been processed the SQL task then shows:
>Task Execute SQL Task
Start(4)
Validation has started(4)
Validation is completed(4)
Progress: Executing query "sp_MoveEndoscanFile ?". - 100 percent complete
Progress: Executing query "sp_MoveEndoscanFile ?". - 100 percent complete
Progress: Executing query "sp_MoveEndoscanFile ?". - 100 percent complete
Progress: Executing query "sp_MoveEndoscanFile ?". - 100 percent complete
Stop(4)
Finished, 9:20:02 AM, Elapsed time: 00:00:05.273
ForEachLoopContainer.jpg
Package-Control-Flow-with-Proper.jpg
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35149303
Thanks for the additional information as well.

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).
0
 

Author Comment

by:ExpertUserId
ID: 35150244
Thanks for the back ground.  That would make a lot more sense regarding the proper sequence being followed just not reported as such.  

Yes. The Sproc works great when executed from Management Studio.  I have added error trapping and it does not make any difference.  

I am wondering if it is not getting the file name in the input variable.  Do you know if the actual file name should show in the progress results?  Or is the "?" all that would show? (see post above)
0
Better Security Awareness With Threat Intelligence

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.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35155943
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. ;-)
0
 

Author Comment

by:ExpertUserId
ID: 35157774
SSIS-package-Screen-shots.docx I have attached a word doc with all of the screen shots. SSIS-Package-Screen-shots.docx.

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.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35158171
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.
0
 

Author Comment

by:ExpertUserId
ID: 35159617
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.
0
 

Author Comment

by:ExpertUserId
ID: 35161342
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

-expert user
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35161405
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.
0
 

Author Closing Comment

by:ExpertUserId
ID: 35167106
Solution was to use a File System Task. I am greatful for the help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now