SSIS – File mapping object ‘Global\DTSQLIMPORT’ could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.
Man one of these days MS will make this easy. I am one of the ones that came from DTS land where after a few years things were comfortable. So in an effort to make things easier MS gave us SSIS to overcome DTS shortcomings. Well they made my life a living hell in trying to learn this /rant.
The latest in my struggles is trying to deduce from the garbage that is SSIS messages what I need to do. The latest being:
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(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
What was I trying to do I will represent in this complicated flowchart.
Excel document –> SQL Server
Confused yet? Me neither, this in DTS land would have taken an hour tops! I am going on 4 hours! Not only am I battling Excel and it’s ‘here let me make this easier for you and guess what types or lengths your columns can hold’ productivity enhancements. I am also needing to deduce these bs error messages that are given at freaking runtime from SSIS designer!
/rant #2
Ok so the actual fix now is, is instead of using a SQL Destination (even though you are inserting into SQL wtf) use an OLE DB Destination. The reason being to use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.
Hope that helps.
