I was trying to export data from a txt/csv file to sql server table.
After setting up the package and executed it .It worked if the sql server destination is on the same server were i ran the package from.
But when i tried to export the data to a different server i get the error:-
[SQL Server Destination [2625]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(error not found). Make sure you are accessing a local server via Windows security.".
After doing some search i found this great article.
Which stated that SQL Server Destination that cannot access a remote box because
BULK INSERT will not be able to access the memory space of a remote machine.
So the way to go is to use OLEDB Destination.
One more thing though is you could setill get error after switching to OLE DB Desitnation and you might need to re map your columns to get it to work :( especially if it was set as SQL server Destination.
Tip: Make sure you use SQL SERVER Authentication when using the remote option
Hope that saves your time.