I have 3 Servers Server A is the desitination server, SS 2008 R2 64 bit Server B is the SSIS Package Server SS 2005 32 bit Server C is Oracle - version and other info unknown. I am trying to load information from Server C to Server A Server B has Linked servers to Server C I am having difficulty with usernames, logons drivers etc... I have tried to used server logons for all of the sources and destinations but it continues to fail when trying to load a 2008 server with a 2005 package from a 2005 server.
I am not a SSIS expert but having linked servers on Server B isnt necessary. SSIS packages hold all their own connection information. I'd expect to see a SSIS package with; - an Oracle database connection with settings to connect to Server C - a SQL Server database connection with settings to connect to Server A Then a data flow that uses the database connections to carry out the ETL process. IMO - Server B doesnt need any linked servers