The Temple of Fu

code, systems and games

Archive for the ‘SSIS’ Category

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.

with 2 comments

Man one of these days Microsoft will make these error messages easier to understand. I am one of the ones that came from DTS land where after a few years things became comfortable; but in an effort to make things easier for us Microsoft gave us SQL Server Integration Services (SSIS) to overcome some of the DTS package shortcomings. Well they made my life a living hell in trying to learn this, well at least this particular task /rant.

The latest in my struggles is trying to deduce from the garbage that are SSIS messages what exactly I need to do to fix my error. The most recent 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 table

Confused yet? Me neither, If I were to do this with a DTS package it would have taken an hour tops but currently 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 within BIDS designer!
/rant #2

OK so the actual fix now 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.

Written by lordfu

November 5, 2010 at 4:33 pm

Posted in Development, MSSQL, SSIS