Hello, I am attempting to help out my DBA deal with a rather difficult problem. Currently we have a process to export data into a csv file through an SSIS package. Typically the destination would simply be \\server\folder however, we would like to transfer the csv file to a server in the dmz that is not on the domain. The SSIS package that wants to do the export wants to use the domain account from the domain server where the package is executed from, however when it attempts to connect to \\server\folder it will fail because that account has no access on the local dmz machine. Is there a way to pass local credentials to a server for making this type of transfer work? So for example, the package will run from SqlServer.domain.com and it will want to copy it to a server in the dmz at 192.168.1.2 in a folder marked transfers. When forming the SSIS package the destination for the csv file would be \\192.168.1.2\transfers. Any help would be appreciated!Jonathan Finkbiner

A couple of ideas - Create a mirrored account, same user name and password on both sides. I forget if this will work from domain to non-domain, but the risk is the account used is the SQL Server Agent service account (I assume that is the security context in use for a scheduled package) will have to be mirrored in the DMZ which could be considered a security risk. Use the "net use" command line to setup the UNC share. You can call this with cedentials. You do not have to specify a drive letter when using net use, in fact for this scenario I recomened you don't. I would also ensure you remove the share after use each time. I would do this in two stages, 1 Create your file locally, 2 Map the share, copy the file, remove the share. The later could be done in a batch file. Avoid WIndows security issue entirely and use ftp or similar to transfer the file. PowerShell has the New-PSDrive cmdlet which allows you to specify a username, and then password. You would need to do the file copy in the same PowerShell session as well, but no hardship I feel.http://www.sqlis.com | http://www.konesans.com

Awesome. Thanks for the feed back. I thought of the mirrored account idea as well. I wasn't sure if that would work. Let me give that shot and I'll get back to you.Jonathan Finkbiner

Ok, so it appears like the SSIS package fails upon executing with the following error message (no code tags?): Error 0xc001000e: SSIS_Package_Test: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. You were spot on with the UNC paths working with a mirrored account, however when the DBA logged on to the mirrored user account locally on the domain resource and tried to construct the SSIS package the error came up on the execution step. By the way, I left some vital info out. We are currently using SQL Server 2005 Enterprise Edition with the latest service pack applied. If you need the exact revision level, I will need to ask our dba. In summary, the mirrored account can get from inside the firewall to the outside resource, without being prompted for credentials, however the SSIS package is still failing on the Executing step according to the UI. The error message above is one of several of the same messages listed when clicking the "messages" hyperlink from the error record of the GUI when executing the package. Any ideas?Jonathan Finkbiner

Change your package to use a local path and make sure it works locally first. That error doesn't make sense in isolation. Look in the Output window (Ctrl+Alt+O) as it is easier navigate, as well as copy and paste stuff from there.http://www.sqlis.com | http://www.konesans.com