4

I am using SQL Server 2016 and SSIS. I have a package in which I am calling a sql server job.

My job have just one step as PowerShell command for transfering all files from one directory to another directory between two servers.

When I run my job from SQL server, it works well.

When I run my package from VisualStudio, every things is ok, but after deploying my project to sql server, when I want to run this package from SQL Server, I have this error :

Execute SQL Server Agent Job Task: Error: Failed to lock variable "RunId" for read access with error 0xC0010001 "The variable cannot be found. This occures when an attempt is made to retrive a variable from the Variable collection on a container during execution of package, and the variable is not there. The variable name may have changed or the variable is not being created.".

enter image description here

I deleted the Execute sql server agent job task from my package and I redeployed my project and now I do't have this error. It is clear that error is about Execute sql server agent job task.

I have more than 2000 files in source directory and it takes several minutes to transfer all files to destination directory.

Edit1 : I have several Task and component in my package and the last ones is Execute sql server agent job task and i don't have Execute T-SQL Statement Task.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
  • Are you sure that you don't have any `Execute T-SQL Task` in there? – Hackerman Jul 12 '17 at 16:02
  • does this help :https://stackoverflow.com/questions/3107766/failed-to-lock-variable-ssis-error – TheGameiswar Jul 12 '17 at 16:29
  • I don't use the "Other Tasks"/DBA tasks much as they're ... limited in use for me. You can click the View TSQL button and you'll see the task is generating `EXEC msdb.dbo.sp_start_job @job_id=N'DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF' so I'm not sure where the RunId is getting referenced from. Since your package is so trivial, could you try just creating a new SSIS package with the single task and connection manager and see whether the problem persists? – billinkc Jul 12 '17 at 16:35
  • @TheGameiswar: I have read this article. It is not my case. I have not deleted the variable or I don't have two component which are using the same variable. And i dont have this error when i run my package from VisualStudio. – Ardalan Shahgholi Jul 12 '17 at 16:52
  • @Hackerman: I have several Execute SQL Task in my package The last task in my package is Execute sql server agent job task – Ardalan Shahgholi Jul 12 '17 at 16:57
  • @billinkc I have created a new package and I have put just one `Execute sql server agent job task`. I have the same problem and the same error message. – Ardalan Shahgholi Jul 12 '17 at 17:32
  • 1
    Ok Ardalan....now if you look at the properties of the `Execute SQL Server Agent Job Task` you'll see that one of the properties is called `RunId` with an value like `{00000000-0000-0000-0000-000000000000}`; now right click on your package and choose `View Code`...and Ctrl+F and look for `RunId`...for some reason(bug maybe) that value is not setted in the XML...what you can do and since that task relies on the `sp_start_job` procedure, you can just add an execute sql task that call the `sp_start_job` procedure instead of the agent job task... – Hackerman Jul 12 '17 at 18:00

0 Answers0