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.
source to share