Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using SSIS package not executing tasks in proper depandant sequence.

Posted on 2011-03-15
14
858 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

828 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