none
EXCEL destination -failed validation and returned error code 0xC020801C

    Question

  • W2K3 with SQL 2005 SP3

    everything I have googled talks about the error occurring in a 64 it environment; I am running in 32bit

    Simple package:  execute SQL stored proc --> convert 2 columns to unicode --> excel destination-->email excel file (Uses dtsconfig files)

    Runs perfectly from VS; import package and can execute from IS; create job in SQL Agent (either as a IS or CMDexec command) and I get these errors:

    SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "OD26711" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    component "Excel Destination" (76) failed validation and returned error code 0xC020801C.

    Thanks!

    Rose

    Friday, May 27, 2011 2:48 PM

Answers

  • I: is a local drive on the server.

    I have tried executing it as a CMDExec and as a SSIS; neither worked

    For at least the 3rd time today - i deleted the entire job in SQL Agent and recreated.   IT NOW WORKS ! %$^%%#$% only executing it as SSIS with windows authentication.

    Not using the proxy; using the sql agent service account.

    No permissions on any accounts/folders were made.  Not sure what caused it to start working.  Don't remember if I had tried the SSIS job type after I switched from the mapped drive to a local one or not.

    Thanks for all your help.

    Friday, May 27, 2011 6:33 PM

All replies

  • Must be a security issue, did you use a Domain based account for proxy to run this package?

    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 3:05 PM
  • SQL Agent Service is ran under a AD domain account.  That account is part of the administrators group.

    I have numerous SSIS jobs that run successfully on this server.  However, this is the first job I have created that uses an EXCEL destination

    Friday, May 27, 2011 3:13 PM
  • The account may not have permissions to the location on the file system causing it to fail. So you'll want to make sure that Domain account has write access to the folder you are saving the file at.

    Friday, May 27, 2011 3:17 PM
  • I thought that too since I was trying to write to a mapped network drive.  But I change the location of the files to reside on a local drive and I get the same error {sigh} 
    Friday, May 27, 2011 3:28 PM
  • Did you explicitly give that account permission locally. If its a domain account it may not have permissions unless you've explicitly granted them. I've ran into it before - it's worth double checking.

     

    Also does the file exist already, some of the file connection manager types don't create files - only connect to existing I think that is true of the excel one (but I'm not positive)

     

    We typically use the CozyRoc Excel+ component for excel, I've had much better luck with it (and it even works on both 64bit and 32bit).

    Friday, May 27, 2011 3:33 PM
  • Forgot to mention - when I changed the location of the file to the local drive....the error did change to ..

    The process could not be created for step 1 of job 0x0C8102907AAD274A895C3682E2B21DD4 (reason: The system cannot find the file specified).  The step failed.

    Yes the Excel file does exist.

    Yes the domain account ( as a part of windows admin group) has full permissions to the directory and actual file.  Permissions are NOT explicit to the domain account itself.

    Friday, May 27, 2011 3:38 PM
  • So based on that the file Share definately sounds like a permissions issue.

    On the file system - when you change to running it locally are you accidently overwriting the filename with a configuration causing it to not match? Or do you have the file open in excel currently which may be locking it?

    Friday, May 27, 2011 3:40 PM
  • Project is Closed and Excel is not running.  I have verified that the excel file path in the config is correct for the local drive

     

     <Configuration ConfiguredType="Property"
           Path="\Package.Connections[OD26711].Properties[ExcelFilePath]"
           ValueType="String">
      <ConfiguredValue>I:\trp\Excel\OD26711 TRP and Box Usage.xls</ConfiguredValue>
     </Configuration>
     <Configuration ConfiguredType="Property"
           Path="\Package\Send Mail Task.Properties[CCLine]"
           ValueType="String">
      <ConfiguredValue></ConfiguredValue>
     </Configuration>
     <Configuration ConfiguredType="Property"
           Path="\Package\Send Mail Task.Properties[FileAttachments]"
           ValueType="String">
      <ConfiguredValue>I:\trp\Excel\OD26711 TRP and Box Usage.xls</ConfiguredValue>
     </Configuration>

    Friday, May 27, 2011 3:56 PM
  • Then it could be this: the access to the %TEMP% directory. Please make sure this account has full access to the temp directory.

    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 3:58 PM
  • Also I see you configured the same path twice, if these are in the control flow do you have them running sychronously?
    Friday, May 27, 2011 3:59 PM
  • sorry shouldn't of included the 2nd entry ...that is actually for the email task (attachment)

    Verified that the admin group has full control to the %temp% directory

    Friday, May 27, 2011 4:10 PM
  • Is the Excel driver alive in your deployed to environment?

    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 5:17 PM
  • I am still on my development server (where package was created) which has Microsoft Office installed.
    Friday, May 27, 2011 5:24 PM
  • It definately sounds like a permissions or locking issue. If you are running it in BIDS does your account have access to the path?

     

    Does it work in BIDS?

    Friday, May 27, 2011 5:27 PM
  • I can execute it successfully under visual studio; and from SSIS Package Store.

    The only place that it won't execute is SQL Agent. 

    Yes I agree with you - but I can not think of anywhere else to look.  And it sems to be specifically with the EXCEL file

    Friday, May 27, 2011 5:38 PM
  • This account you are running the package using the Agent must be the issue.

    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 5:48 PM
  • Sorry, forgot to add the link: http://bidn.com/blogs/DonnyJohns/ssas/1705/sql-server-agent-proxy-accounts

    Follow the above article on how to properly set the Agent job to run the package, you are very close to make it working.


    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 5:49 PM
  • Even though i am running SQL Agent with a domain account that belongs to the server's windows admin group ....

    I created credentials linked to the server\administrator account -this is the account which the package was developed.

    Created a proxy with those credentials

    Within the job step - "run as" proxy

    I still receive the same error .........

    The process could not be created for step 1 of job 0xB334E02CF375604F86FBF8D45B56B514 (reason: The system cannot find the file specified).  The step failed.

    Friday, May 27, 2011 6:06 PM
  • The I drive might be the problem then.

    Are you on a clustered SQL Server instance?

    Try relocating the Excel file to another drive, v:?


    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 27, 2011 6:16 PM
  • Can we try taking the space out of the filename as well, its unlikely that will make a difference but its worth a shot. If I has a less busy day today I'd hop on a webex with you to see if your missing anything.

     

    Maybe some screenshots of the configuraton, and the folder may help too.

     

    Also is the I:\ drive a physical drive, or a mapped network drive?

     

    Friday, May 27, 2011 6:28 PM
  • I: is a local drive on the server.

    I have tried executing it as a CMDExec and as a SSIS; neither worked

    For at least the 3rd time today - i deleted the entire job in SQL Agent and recreated.   IT NOW WORKS ! %$^%%#$% only executing it as SSIS with windows authentication.

    Not using the proxy; using the sql agent service account.

    No permissions on any accounts/folders were made.  Not sure what caused it to start working.  Don't remember if I had tried the SSIS job type after I switched from the mapped drive to a local one or not.

    Thanks for all your help.

    Friday, May 27, 2011 6:33 PM