question

Gehima2016 avatar image
Gehima2016 asked

Sql agent Job

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.
sqljobagentfailure
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Gehima2016 - now you've removed the error messages, there's no chance of us helping you, or of any responses below of being much use to anyone else... (My edit was just a re-tag, in case anyone is wondering...)
0 Likes 0 ·
tzvikl avatar image
tzvikl answered
Looks like your trying to connect to a CSV file - are you sure the file is present on the server?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Waqar_lionheart avatar image
Waqar_lionheart answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.