SSIS execution fails when trying to connect to a remote server

Need help building the SSIS package I am creating. So here's what I've done so far.

I have a DTSX that has a Script component in the data stream that references an assembly where I have all my logic to connect to a remote server using an entity structure and fetch some records from a table.

All my modulation and integration tests on this assembly are working 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 am looking for. Same result if I try to manually execute the package.

I have already made sure that the servers can communicate. I created a console application using this assembly and it works fine, I made ping requests to the remote server and everything is fine, I connected to this server using SQL Management Studio, I even added the remote server as a linked server on Server A and was able to complete the request.

The last attempt I made was to create a SQL Agent Job that launches my test console application and the result is fine too. I did this to make sure the user calling the console is the same one calling DTSX.

I am really getting ideas, so if anyone can help I would be very grateful.

+3


source to share


1 answer


Finally, I discovered what was wrong. I was missing some configurations that needed to be added to IServerExec instead of the dtshost configuration file. Unfortunately, the error message was not clear enough.



+1


source







All Articles