I have a SSIS package which takes the many file locations from a SQL table inside a for each loop and checks the existance of each file inside a script task. If file exists, an user defined variable User::IsFileExists is set to 1. this variable is set as 0 (zero) by default. Depending on this variable value (1/0) the flow is sent to Data Flow Task or Notify Operator respectively. (I have used Constraint (Success) and Expression for precedence flow.) This package works fine when I run it manually from dev studio. But when I create a SQL Server Agent Job for the package and run it, the variable User::IsFileExists is always 0(zero). I dont get why the variable values are not changed. Due to this even if file is present, the flow always goes to the Notify operator part. This variable is created in the package scope. and is added to readwrite variable list in script task. Please help!!! Thanks.

Normally the SQL Server Agent Job does not have an effect on internal SSIS variables. I think the problem could be caused by permission issues. The SQL Server Agent account possibly doesn't have access to all the folders where the files are located, so you will always get a 0 value (file does not exist, since he can't find it). Do you use a try/catch block in your script task? If not, add it to the code and write a DTS.Log statement in the catch block. Then enable logging on the script task. That way you can check if there are issues at runtime.MCTS, MCITP - Please mark posts as answered where appropriate.

Koen is right try doing this , isted of multiple folders make it so that you only have 2 folders , check the permissions and etc.... insted of setting the variable through the setting in a SQL Job, pass the variable in a XML file or another test will be open the package hard code the variable to start from 1 and then see what happens PLEASE NOTE THAT: what i have mentioned are only for test scenariosSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Are you fetching the files from correct folder? Please cross check it.. (try to write all the variable values in some file and check that) As Koen mention that Job doesn't play with internal variables.. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Does the account of SQL Server Agent or the Proxxy (if you have any) has the permissions to access the folder?

Does the account of SQL Server Agent or the Proxxy (if you have any) has the permissions to access the folder? Yes the proxy used to run the job have sufficient permissions.

Are you fetching the files from correct folder? Please cross check it.. (try to write all the variable values in some file and check that) As Koen mention that Job doesn't play with internal variables.. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/ Yes. they are referrring t the correct folders. Because the package runs properly when is manually executed on the dev studio.

Do you use any package configurations? Did you turn on logging on the script task, as I've mentioned in my first post?MCTS, MCITP - Please mark posts as answered where appropriate.

I believe this is the file folder read/write permision issue as your sql server agent account are not able to access the folder your files reside. What you can do is: 1. Create a credential under Security from an SQL login 2. Give the read/write permision on the parent folder of your working folder to the log in 3. Create a proxy under SQL server agent Proxies from the credential you created 4. Tick the Active to the SQL Server Integration SErvices Packes 5. Edityour step to run the SSIS packages to run as the proxy you created Hopefully, this works

I have done what ever Steven Wang has told in the previous post. Now I get a new error something like below when I run the job - Source: Notify Operator Description: Failed to lock variable "RunId" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". End Error Error: 2011-04-12 12:38:40.02 Code: 0xC0014054 I get this error for the 'Check if file exists' scrit task and all the Notify operator tasks. But not for Data Flow Tasks. The package works fine when executed from dev environment. Fails only when executed through a job.

Do you use any package configurations? Did you turn on logging on the script task, as I've mentioned in my first post? MCTS, MCITP - Please mark posts as answered where appropriate. Hi Koen, Yes package uses xml configurations. I have never done logging before. Can you pls suggest good explanatory link? Thanks.

Logging in the script task: http://msdn.microsoft.com/en-us/library/ms136131.aspx Regarding the variable issue: does the variable still RunID exist in your package? How do you use it in your package?MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Koen, Thanks for the link. I will go through it. No. I dont use any variable called 'RunID'. I have no idea where it came from.

No. I dont use any variable called 'RunID'. I have no idea where it came from. Well, apparently there is some reference in your package to the variable RunID, hence the error. You can open the XML file of the package and find it using text search...MCTS, MCITP - Please mark posts as answered where appropriate.

No. I dont use any variable called 'RunID'. I have no idea where it came from. Well, apparently there is some reference in your package to the variable RunID, hence the error. You can open the XML file of the package and find it using text search... MCTS, MCITP - Please mark posts as answered where appropriate. I have searched already for this name thinking the same. But nothing was found with that name :( I am completely struck.

Logging in the script task: http://msdn.microsoft.com/en-us/library/ms136131.aspx Regarding the variable issue: does the variable still RunID exist in your package? How do you use it in your package? MCTS, MCITP - Please mark posts as answered where appropriate. Where are the log entries stored? How to read the logs?

Where are the log entries stored? How to read the logs? It depends. If you right-click on the control flow, you can select Logging in the context menu. There you must provide a connection for the log provider. This is either to a SQL Server database or to a flat file. If you choose the database, the log results will be written to a table (the name depends on the version of SSIS that you are using). In the Logging menu, select the events (in the details tab) that you want to log and the tasks for which you want to log. Select all the tasks and for events, choose OnError and OnTaskFailed. Select the script task in the logging menu, and choose the event ScriptTaskLogEntry. In the script task itself, you can log an error message in the catch block by using the following code: Try ' Your code Catch ex As Exception 'An error has occured. Log the error and fail the task. Dts.Log(ex.Message.ToString, 999, Nothing) Dts.TaskResult = ScriptResults.Failure End Try MCTS, MCITP - Please mark posts as answered where appropriate.