SSIS Package Stuck at "Created Execution" Status

Question

I recently deployed a update to one of my SSIS projects, and ever since the project has failed it's scheduled runs.

The SSIS package appears to be stuck at the "Created Execution" status. There are no messages in the reports to explain this issue.

I've attempted to redeploy the project, but the results remain the same. I ultimately had to revert to an earlier version of the deployment in order to bring the system back online, but I am now in a position where I can not update the project.

I'm at a loss right now as to how to resolve this issue, and this project is a rather vital component to our business.

Any guidance would be appreciated.

Bivalve

Were you able to debug/run the package in Visual Studio? – Eboat

Yes it ran fine via Visual Studio. The deployed version seems to be the problem – Bivalve

What version of VS are you running? And what is your SQL Server version? – Eboat

Visual Studio 2010 locally for Development and SQL Server 2012 on our Production server – Bivalve

I have found the issue. After digging into the job history, I found a useful message regarding my environment variables. As it were, since the last deployment, a change had been made to the SMTP connection settings, and the connection string was parameterized at the project level. The environment variable was setup, but were still unmapped on this particular project. I redeployed the project and assigned the variable. The project now works normally. – Bivalve

Answer 1

In the interest of having an actual answer on this question and having just run into this very frustrating issue myself, the problem lies in package/environment parameters. When a package is run on a schedule, if required package parameters are not supplied, SSIS just... sits there. No errors are reported, but the package never actually starts.

Providing values for the required parameters will resolve this issue.

Darnall

Had a similar problem where my environment was not selected in a job while the paramters did exist. For some reason SSMS allowed me to save the job without a environment selected. So even when you have the paramters set but no environment selected in the job this will occur – Deeannadeeanne

All my package parameters for the entry package are configured, but this is still happening. Do you, offhand, know of any other reason? – Dignitary

Answer 2

I found that this can also happen if a new version of a package no longer has, previously configured, parameters. The only solution I found is to re-create the job-step after updating the Environment Variables' configuration. Somehow the Job seems to 'remember' the no longer existing parameters, and will not actually start the package because it thinks that some parameter-configurations are missing.

Haematogenous

Answer 3

For someone that had this issue with a development team triggering 1000+ bad SSIS Executions through the stored procedures we had set up on the Database Administrator side causing a memory overload... Can't do much for not following documentation.

To set parameters in your package if they remain unset. see reference

EXEC [SSISDB].[catalog].[set_execution_parameter_value] id, object_type, parameter_name, parameter_value

If you wish to start it and get it to run after setting up the parameters.

EXEC [SSISDB].[catalog].[start_execution] id

If and only if status of the package is left in the status = 1 state. see reference

There is also if the package is already running to stop it

EXEC [catalog].[stop_operation] id

And if you need to do it bulk for what I had (setting parameters up properly) create a cursor and do what you need to do based on the status and issue in the packages if there are valid ones that need to run and invalid ones that need to end their existence.

DECLARE id BIGINT

DECLARE < cursor > CURSOR FAST_FORWARD FOR
  SELECT * FROM [SSISDB].[catalog].[executions] WHERE [status] = 1

OPEN < cursor >
 FETCH NEXT FROM < cursor > INTO id

 WHILE @@fetch_status=0
 BEGIN
  ...
  Code for changes to SSIS packages
  ...
 END

CLOSE < cursor >
DEALLOCATE < cursor >

Dowable

Answer 4

If you don't execute the package before deployment, possible to take this error. You should first execute the package then deploy. After that you can start the SQL Server Agent job.

Wellordered

Answer 5

I have got the same error while running the SSIS job. I have just ran that job manually once using "Right click on the Job>> Start job at Step". After doing this the job ran successfully. The job worked fine from the next job schedule

Papeete