September 18, 2008 at 2:41 pm
I developed a package that imports some flat file data into sql server tables on my local workstations instance of SQL Server 2005. I used windows credentials when creating the connection manager in my package for my local database I was writing to. My source flat files were already on another server well call Frank (also with SQL Server 2005 installed). I finished developing the functionality of my package and everything worked fine. Now I need to repoint the destination point for the data to an identical database in the SQL Server instance on Frank, only Frank doesn’t want to play nice. I created an application account user login on Frank called ssis_user so that in my connection manager I could opt for SQL Server Authentication instead of Windows Authentication. On Frank I added ssis_user to my database and gave it every role I could. For user mapping I mapped it to my database and again made sure it had appropriate roles assigned. Every time I open the connection manager, type in the password and test the connection, it works great. When I actual run the package though, I get the following…
[SQL Server Destination [25798]] 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(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
I tried creating a new connection manager object and repointing all of the Data Flow Destinations to it, instead of just repointing the old Connection Manager Object but that didn’t seem to make any difference at all. When I open the connection manager object and retype the ssis_user password in and click on the test connection button I get a "Test Connection Succeeded" message every time. One odd item but I dont know if this is default behavior or related, every time I open the connection manager the password field for the SQL Server Authentication is blank instead of showing a "*********" even though I checked the "Save my password" check box.
I am not as well versed in the intricacys of the SQL Server security model so I realize I am a bit handicapped from that standpoint. Is there some sort of check list of things that need to be setup for using an application account login on SQL Server in order to use the SQL Server Authentication method for connecting your SSIS package? Any suggestions would be appreciated. 🙂
September 18, 2008 at 3:25 pm
Whats really baffling about this is I have a Execute SQL Task on the front end of this package that flushes the tables prior to populating them and it works using the same connection manager as all of the subsequent 8 control flow objects but the control flow objects all fail for the same reason. :crazy:
September 18, 2008 at 6:32 pm
I finally had some one point out what the problem was on another board. I was using a SQL Server Destination which will only work for the server the package is run on. I could run it locally as long as the database was local as well. I couldn't run it locally via Visual Studio 2005 while it pointed to a remote server as a destination. I could deploy that same unaltered package and run it from a command line on the remote server and it would work fine. Sounds like if you have a package that needs to write to a SQL Server instance that is not local, you need to use the OLE DB Destination object instead. Well that killed most of my day, but hey, at least I have a solution now! :w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply