I am trying to make a data flow task in SSIS where I insert data from an OLE DB Source to an OLE DB Destination (i.e. two different SQL Server instances on different VMs). I was able to get the OLE DB Source connection to work. The OLE DB Source is a SQL Server instance on a personal VM that I use for staging and data conversion.
However, when I try to establish the OLE DB Destination I keep getting an error because the destination SQL Server is on a different VM/data server. No matter which provider, server name, or credentials I use I cannot get it to work.
Can anyone please point me the in the right direction?
I usually use "Microsoft OLE DB Provider for SQL Server" for the source so I thought I could do the same with the destination, but I'm not sure which authentication I should be using?
Here is the error I get when I try, for example, the sa account:
