How do I resolve a 0x80040e14 and 0xC0202071 in SSIS?

Question

I am extremely new to SSIS and may need allot of things explained.

What I am trying or attempting to accomplish is this:

1. Connect to an XML site using SSIS in VS-2013
2. Delete the existing data in the table
3. Dump the parsed XML data to the table

I am getting the following execute errors in SSIS:

• [SQL Server Destination [61]] 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 Server Native Client 11.0" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 5(Access is denied.). Make sure you are accessing a local server via Windows security.".
• [SQL Server Destination [61]] Error: Unable to bulk copy data. You may need to run this package as an administrator.
• [SSIS.Pipeline] Error: SQL Server Destination failed the pre-execute phase and returned error code 0xC0202071.

I do not even know what information to add to be helpful. I have a data flow which consists of a XML web source and a destination that points to a SQL table on the system.

My Questions are:

1. How do I resolve the errors mentioned above?
2. What am I missing?

Answer 1

You will need to set your connection string as an expression and either hard code or parametrize your user and password.

The password is encrypted based on information from the machine that it is encrypted on. Once you move that encrypted property to the server it will not know how to decrypt it and therefore does not have a valid password.

I had to do this with a project I was working on with AZURE. I believe there is a setting to remove the encryption from the password but that is no different then hard coding the user and pass in the connection string expression.