• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

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.

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

-ExpertUser
0
ExpertUserId
Asked:
ExpertUserId
  • 8
  • 6
1 Solution
 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
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
 
ExpertUserIdAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
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
 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
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
 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
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
 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
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
 
ExpertUserIdAuthor Commented:
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
 
8080_DiverCommented:
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
 
ExpertUserIdAuthor Commented:
Solution was to use a File System Task. I am greatful for the help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now