CODEWITHSUNDEEP
c#sql-serverssis
h0tfus3
h0tfus3

Reputation: 592

SSIS execution fails when attempting to connect on remote server

Need some help with a SSIS package I'm creating. So here's what I've done up until now.

I have a DTSX that has a Script Component in a Data Flow that references an assembly where I have all my logic to connect to a remote server using entity framework and retrieve some records from a table.

All my unit and integration tests on that assembly work fine. Unfortunately after deploying the package to Server A and running it with a SQL Agent Job, it gives an error when executing the script component indicating that it cannot find the SQL instance I'm looking for. Same result if I try to execute manually the package.

I've already made sure that the servers can communicate. I've created a console app using that assembly and it works fine, I've made ping requests to the remote server and it's ok, I've connected to that server using SQL Management Studio, I've even added the remote server as a linked server on server A and was able to execute a query.

Last attempt I made was creating a SQL Agent Job that run my test console app, and the result is OK too. I did this to make sure that the user that invokes the console is the same that invokes the DTSX.

I'm really getting out of ideas so if anyone can help I would be very grateful.

Upvotes: 2

Views: 386

Answers (1)

h0tfus3
h0tfus3

Reputation: 592

Finally I found what was wrong. I was missing some configurations that needed to be addes to IServerExec instead of dtshost config file. Unfortunately the error message wasn't clear enough.

Upvotes: 1

Related Questions