SSIS Issue

Question

Post reply

SSIS Issue

sqlrocks79

Old Hand

Points: 354
More actions
November 3, 2010 at 12:18 am

#228710

Hi
I have a SSIS package and as part of that package I have a For each loop container. And I have a Source folder with say 4 files F1,F2,F3,F4.
Now the role of for each loop container is to move these files from Server\Fileshare$\SourceFolder to Server\Fileshare$\Destination Folder.
The package runs fine when run from the BIDS but it fails when scheduled through Agent Job.
The Package Creator and the Job runner are the same acount. When run through the Agent Job it says that
Could not find file 'C:\WINDOWS\system32\F1.csv' But the actual file Path is 'Server\Fileshare$\SourceFolder\F1.csv'.
I don't know why the agent is looking for the file in the location ''C:\WINDOWS\system32'.
Error message:
Executed as user: UserA. ...10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:00:00 PM Error: 2010-11-02 17:00:08.07 Code: 0xC002F304 Source: File System Task Description: An error occurred with the following error message: "Could not find file 'C:\WINDOWS\system32\F1.csv'.". End Error
Could anyone help please?
Thanks in Advance
SQLRocks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gtschech Ten Centuries Points: 1351 More actions · Answer 1

Hi,

i'm afraid your information is insuficcient for a real good answer.

I would suggest to activate logging on the copy task so you can see which path actually is taken.

From what you say it seems you'r not setting the variables/parameters with full path name.

It could also be that you're using environment variables, which are specific to the user running the package.

These reasons seem to be most likely; but others may become visible when you turn on SSIS logging intensively.

Guenter

tmitchelar Hall of Fame Points: 3391 More actions · Answer 2

Are you using package configurations or an expression for your connection manager to the file? Sounds like your package is reading the hardcoded path in BIDS, but then reverting to a different path when you are running it outside of BIDS.

sqlrocks79 Old Hand Points: 354 More actions · Answer 3

Thank you very much for your Help.

Actualy I am using a Varibale say V1 for the Foreachloop container and then using the Expresssion

V1 + .csv for the flat file connection manager.

When run from BIDS the package is executing fine. But when executed from Job it is giving this error.

Is this related to SQL Server Agent Job in any way ?

Thanks

SQLRocks

sqlrocks79 Old Hand Points: 354 More actions · Answer 4

Forgot to mention that I am using a File System Task within the For Each Loop container Task and then using that File System Task to move the files.

Thanks

SQLRocks

tmitchelar Hall of Fame Points: 3391 More actions · Answer 5

Thanks for the additional info, but this is still difficult to diagnose without more information. My advice would be to create a test package and get rid of everything and then slowly build it testing in both BIDS and SQL Server agent as you go to try and isolate the issue.

My guess is that there is a permissions issue that you are overlooking - try to dig a little deeper before eliminating this as the problem. Double check to see what your SQL Server Agent account is running as and then double check the permissions on the server/folders you are trying to access.

If you can eliminate a permissions issue with certainty, then another guess would be the value that your variable is getting set to or SSIS isn't reading the server file path structure correctly as the culprit. Again, in your test package, create a simple test first as a hardcoded value, then as a variable, then as a loop in both BIDS and the Job Agent to isolate the problem.

Good luck and let us know what you find.

sqlrocks79 Old Hand Points: 354 More actions · Answer 6

Hi Tmitchelar,

Thank you very much for your help.

Unfortunately we don't have Test or Dev environments to test this Pkg. This is directly on the Prod box.

Actually I created a Agent proxy for the user account that has created the SSIS Package to run the SSIS package through the Job. But he is only a user but not the local admin on the box. But he has all the privileges to run the SSIS package through BIDS. So we requested Local admin privileges for the account and would like to see if that fixes the issue.

The next option would be to check if the SSIS is correctly identifying the variable value.

I will keep you posted on the results.

Thanks

SQLRocks

gtschech Ten Centuries Points: 1351 More actions · Answer 7

You should make sure that the foreach loop variable is an absolute path.

When you run SSIS from BIDS, the current directory is most likely the dorectory where your .sln file resides, whereas when running the package from outside it is the current directory of the user (when you execute cmd.exe, it is c:\windows\system32)...

Guenter

sqlrocks79 Old Hand Points: 354 More actions · Answer 8

Hi Tmitchelar,Guenter

Thanks a lot for all your help and support. We found that the actual issue was the For each loop container 's path that's accessing the files, we fixed it and boom everything worked fine -- Both the Package and the Job.

Thank you once again,

--

SQLRocks