Proxy Account
I am having the same problem as this user... http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/784839fe-3f42-494b-b936-52779f984a7e/ http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/922d3254-bed6-450b-931e-1f2c25796082/#b2c05b2e-cbc1-45ce-af17-5f92cf039191 The only problem is that i HAVE set up a proxy account to run the job that has access to the sharepoint site and selected that user to run the package.... I must be missing something obvious. I understand that the user that runs the job needs to have access to the SP site. any ideas ?
October 11th, 2012 12:15pm

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions. This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account. http://support.microsoft.com/?kbid=918760 Soooo here is the other kicker... After reading this i decided to just create the package on the server, but for some reason when i log on i am using a temp profile.... (after not being able to access the network location i went to the cmd prompt) When i log on to the server I am using a temp profile for some reason instead of my profile
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2012 12:22pm

Looks like account impersonation is happening, I suggest you figure out what is going on by involving your SharePoint adminArthur My Blog
October 11th, 2012 12:32pm

Naw, for some reason my AD profile is screwed up from the beginning, been happening for a while. its not the sharepoint server that i am logging into its the sql server...
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2012 12:39pm

anyway.. why cant i create a package on one machine (local) and then transfer to another (server) when using proxy acct as mentioned in the KB above " Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account."
October 11th, 2012 12:59pm

So here is the problem I have created a proxy account to run package but I moved package (after development) to a different server with encrypt sensitive with user key. If i cant log on to the server and develop the package how do I get this to work?
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2012 4:11pm

So here is the problem I have created a proxy account to run package but I moved package (after development) to a different server with encrypt sensitive with user key. If i cant log on to the server and develop the package how do I get this to work? When you save SSIS package please select Rely on server storage for encryption.
October 11th, 2012 5:21pm

negative Code: 0xC001401E Source: EPackage Connection manager "Flat File Connection Manager" Description: The file name "\\portal.ABC.com@SSL\DavWWWRoot\XYZ\Shared Documents\Data.csv" specified in the connection was not valid. Connection "Flat File Connection Manager" failed validation. SSIS cant make the connection to the site bc for whatever reason the person running the package does not have acces to the site. Even though under the Job Setup I have set the Run As property to the proxy account with the perms to access the site. This is so frusterating
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2012 5:44pm

"Flat File Connection Manager" validation message has nothing to do with encryption. It is permission issue. Please make sure that proxy account has access to network share "\\portal.ABC.com@SSL\DavWWWRoot\XYZ\Shared Documents\Data.csv"
October 11th, 2012 6:55pm

ElectronicPackage Connection manager "Flat File Connection Manager" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data1.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:18.82 Code: 0xC001401D Source: ElectronicPackage Description: Connection "Flat File Connection Manager" failed validation. End Error Error: 2012-10-12 08:53:18.86 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager 1" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data3.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:18.86 Code: 0xC001401D Source: ElectronicPackage Description: Connection "Flat File Connection Manager 1" failed validation. End Error Error: 2012-10-12 08:53:19.01 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager 2" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data4.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:19.01 Code: 0xC001401D Source: ElectronicPackage Description: Connection "Flat File Connection Manager 2" failed validation. End Error Error: 2012-10-12 08:53:19.06 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager 3" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data2.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:19.06 Code: 0xC001401D Source: ElectronicPackage Description: Connection "Flat File Connection Manager 3" failed validation. End Error Error: 2012-10-12 08:53:21.12 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data1.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:21.17 Code: 0xC0202070 Source: ElectronicPackage Connection manager "Flat File Connection Manager" Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error Error: 2012-10-12 08:53:21.40 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager 1" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data3.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:21.44 Code: 0xC0202070 Source: ElectronicPackage Connection manager "Flat File Connection Manager 1" Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error Error: 2012-10-12 08:53:21.56 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection Manager 2" Description: The file name "\\portal.company.com@SSL\DavWWWRoot\Documents\Data4.txt" specified in the connection was not valid. End Error Error: 2012-10-12 08:53:21.72 Code: 0xC0202070 Source: ElectronicPackage Connection manager "Flat File Connection Manager 2" Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error Error: 2012-10-12 08:53:21.79 Code: 0xC001401E Source: ElectronicPackage Connection manager "Flat File Connection M... The package execution fa... The step failed.
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2012 10:18am

"\\portal.company.com@SSL\DavWWWRoot\Documents\Data1.txt" is not a valid path to a file Arthur My Blog
October 12th, 2012 10:40am

So why does the package run if i execute it from SSIS or in BIDS, the only time i get an error is when running the package as a job... I thought the only reason it is not a valid path is b/c it cant access the file path
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2012 10:50am

If it is then the account executing the package via the Agent cannot see/access it and to resolve you need a proxy to run this SSIS job step as outlined here Running a SSIS Package from SQL Server Agent Using a Proxy AccountArthur My Blog
October 12th, 2012 11:01am

Yup did that, i think the problem is that when i enter the File name in the Flat File Connection manager it is https://portal.company.com/whatever/whatever/Data.txt -I can get to this address from the server and the flat file connection manager changes the path to \\portal.company.com@SSL\DavWWWRoot\whatever\whatever\Data.txt -- I cant get to this address from the server
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2012 11:20am

The IIS root folder is a special one and the access to it is quite restrictive. If the file is hosted within IIS (web page) it may even be locked, you can though download it using Web APIs e.g. using a script.Arthur My Blog
October 12th, 2012 11:22am

Ah crap, i have never done API's. I am needing to write to the root folder. Basically i am pulling data out of the sql servers and placing the data into files on the SharePoint site, and then a 3rd party prog will come scoop them off the SharePoint site
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2012 11:32am

mmm http://mossdefinitely.blogspot.com/2010/08/enabling-webdav-in-sharepoint-windows.html Looks like this is a very common problem with sharepoint
October 12th, 2012 12:35pm

yes, to communicate with SharePoint from SSIS you need special connectors typically. But after all, if you only need a data file hosted within harePoint then the design should provision an offline access to it. I am thinking the SP admin needs to create a process that deposits this file to a local drive outside SP somewhere periodically that you can consume with SSIS. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2012 2:56pm

Huh...... I have implemented the solution above and i can now navigate to the UNC path from explorer. When I Run the SSIS job as the proxy account that has access to the SP site and use the UNC path in the Flat File COnnection manager here is the error Unable to start execution of step 1 (reason: Error authenticating proxy XYZ\UserName, system error: Logon failure: unknown user name or bad password.). The step failed. and when i run the job as the proxy acct but use the mapped drive Z:\ i get the error Message Executed as user: XYZ\User All rights reserved. Started: 1:26:51 PM Error: 2012-10-17 13:26:51.99 Code: 0xC020200E Source: Data Flow Task Flat File Destination [76] Description: Cannot open the datafile "Z:\Data.csv". End Error Error: 2012-10-17 13:26:51.99 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (76) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:26:51 PM Finished: 1:26:52 PM Elapsed: 0.219 seconds. The package execution failed. The step failed. Has anyone on this freaking earth written to sharepoint from SSIS i would really appreciate some feedback, on why this seems so difficult!
October 17th, 2012 4:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics