There a SSIS which is set up to run as a job in SQL agent. The jobs executes successfully in visual studio but fails when run as SQL agent job with the follow error messages. "Message That connection manager is needed by "Flat File Source.Connections[FlatFileConnection]" in the connection manager collection of "Flat File Source". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID. End Error Error: 2017-06-07 07:33:15.35 Code: 0xC0047017 Source: Copy data from CSV to Table SSIS.Pipeline Description: Flat File Source failed validation and returned error code 0xC004800B. End Error Error: 2017-06-07 07:33:15.35 Code: 0xC004700C Source: Copy data from CSV to Table SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-06-07 07:33:15.35 Code: 0xC0024107 Source: Copy data from CSV to Table Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:33:14 AM Finished: 7:33:15 AM Elapsed: 0.625 seconds. The package execution failed. The step failed. " I will appreciate if someone can help with this problem.
couple of things we recently found out about ssis packages. You cant use \\server\... paths (UNC paths) therefore all your paths have to be disk relevant. Secondly you have to make your package "do not store sensitive information" as i think the default is to encrypt it for the user + machine who is creating it. Lastly, if you are creating on a local machine and deploying it to a server then you ll have to create a config file where you would have to write your password (hard coded and visible for all to see :)). Look up running ssis packages under sql server authentication
I'll also add that there's going to be a difference in privs. Just because you can run it in VS, doesn't mean the server login has the same privs to wherever the file is actually stored. The file does NOT need to be on the server but the server login does need to be able to see the directory or UNC and have the correct privs to do whatever job you're trying to do.