0

It's very simple package, running fine when executed via visual studio. From the report I get:

enter image description here

  • Visual Studio 2019 Community
  • SQL2019CTP3.1

In the connection manager I am using windows authentication. The package has the following structure:

  • OLE DB source - simple T-SQL statement
  • OLE DB destination - create table and insert the result of the statement

Can anyone tell what can cause this error? I find some topics telling this is a security issue, but not sure what kind of access I need to grant and to whom.

As I said, when I hit the execute button in the context of Visual studio the package and execute, a table is created in the destination database and records are inserted.

enter image description here

enter image description here

Improve this question
6
  • You mentioned the package runs fine from VS but not how it is run when the errors occur. Add that detail to your question. The security context will be different (e.g. when run under SQL Server Agent service account) and that account will of course need permissions on the resources used (database, file system).
    – Dan Guzman
    Jul 27, 2019 at 12:55
  • I've never found that windows authentication works properly with SSIS, especially if it has to reach to another server to do it's work. Try setting it up with SQL Authentication. But as Dan says, it smells like an authentication issue. Especially if it doesn't pass the validation step (which tests connections to resources as best it can)
    – Jonathan Fite
    Jul 27, 2019 at 13:00
  • @DanGuzman I am working on virtual machine - both the visual studio and the SQL server are there.The NT SERVICE\SQLSERVERAGENT is sysadmin already. I have added full access to this account to the folder where the visual studio project is saved. Nothing change.
    – gotqn
    Jul 27, 2019 at 14:37
  • @gotqn, note that the create table in an OLE DB destination is only a design-time function and doesn't create a table during execution. Not saying that's the issue, just mentioning for awareness. Although security is a common issue when it works from VS but not a job, that doesn't seem to be the issue here. See if you have more details about the underlying cause of the error by viewing all messages instead of just errors.
    – Dan Guzman
    Jul 27, 2019 at 17:03
  • @DanGuzman It's a kind of strange - the caller in the report is the administrator, so it should not be security issue. Could you tell if the posted details are what you referred as viewing all messages?
    – gotqn
    Jul 28, 2019 at 7:31

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.