Possibly save 1 hour of your time: For some reason when the SSIS package was deployed in a different environment and when it ran, it gives this error.
The stored procedure is not automatically created.
Solution: In my case, the issue was actually related to the SQL Server Agent job not setup probably to tie to the environment variables. For some reason that causes this issue.
After the job steps have the environment variables linked, then job is running fine and the stored procedure was automatically created.