December 17, 2013 at 7:39 am
Hi All
I'm struggling to find a solution to an issue I'm having, despite hours of searching forums, TechNet and suchlike...
I have created a package in BIDS. The package does the following:
1. A data flow task:
a. Uses an OLE DB Source object to connect to a SQL Server instance running on the local server, and retrieve a View.
b. Uses a Data Conversion object to convert the DT_STR types to DT_WSTR, in order to prevent the errors regarding converting Unicode to non-Unicode formats.
c. Uses an Excel Destination object, linked to an Excel Connection Manager to map the columns provided by the Data conversion object to the correct columns in an Excel file and output the data to this Excel file. The Excel file name is hard coded into the Connection Manager, and is a .xls (Excel 97 - 2003) file. The Excel file is empty, apart from the column headings
2. Send an email on success, attaching this Excel file
3. Use the File System Task to make a copy of a blank (apart from the headings) version of the Excel file and overwrite the sent Excel file, so that it's ready for next time.
Within BIDS, I've set the Debugging option Run64BitRuntime to False.
When I hit F5 to test the procedure in BIDS, all works successfully, the file is created, the email sent, and the file over-written with the blank file for next time.
I then click on File --> Save copy of Package As... and save the package to the local server, using Windows Authentication. I supply the Package Path, and for the Protection Level, I select "Rely on server storage and roles for access control"
Within SSMS if I connect to SSIS and expand the Stored Packages tree, I can see the package I've just stored. If I right-click on this package and select "Run Package" and click "Execute", again, it runs successfully.
I then create a new Job, using SQL Server Agent. The job has one step, of the type "SSIS Package." I have set the Use 32 Bit Runtime option to True. For the Package Source, I have tried SQL Server, File System and SSIS Package Store, in all cases locating the correct package. For the "Log on to the server" settings, I have tried using Windows authentication, and specifying my own username and password. (I am set up as a sysadmin on the server, and db_owner on the database from which I'm retrieving data).
Whatever I try, I get the same result - the full text is posted below.
I have also tried by using DTEXEC from a commandline, but this also fails with the same result.
Please - can anyone save my sanity and point me in the right direction?
Many thanks!
Andrew
Here's the full error log for the error I'm getting:
[font="Courier New"]
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:59:00
Error: 2013-12-17 13:59:02.05
Code: 0xC0202009
Source: Data Flow Task Excel Destination [106]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
End Error
Error: 2013-12-17 13:59:02.05
Code: 0xC0047022
Source: Data Flow Task SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (106) failed with error code 0xC0202009 while processing input "Excel Destination Input" (117). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 13:59:00
Finished: 13:59:02
Elapsed: 1.17 seconds[/font]
December 17, 2013 at 8:02 am
Have you set "Use 32 bit runtime" inside the agent job? Edit: Never mind, I see you did mention this.
http://1.bp.blogspot.com/-WHsiqaOI8gA/T8_GuvdMMZI/AAAAAAAAAPE/SoqxcClaSvI/s1600/ssis.jpg
December 17, 2013 at 9:01 am
Was the package built using drive letters for your login? Does the account running the SQL Server Agent have permissions to the files and folders involved?
December 17, 2013 at 9:06 am
Does it work if you export as a csv? If so, then its probably some excel devilry. If not, then perhaps permissions.
December 18, 2013 at 1:24 pm
It sounds like security. But Bids is an odd beast. Try running it on the server itself
December 19, 2013 at 3:09 am
It does sound like a permission error. Check that the account running SQL Agent has access to the folders where the Excel template file is stored (and where the new copy is to be created). Also, if you are using drive letters, I would recommend using UNC file paths instead.
December 19, 2013 at 8:11 am
tmatthis (12/19/2013)
It does sound like a permission error. Check that the account running SQL Agent has access to the folders where the Excel template file is stored (and where the new copy is to be created). Also, if you are using drive letters, I would recommend using UNC file paths instead.
That would be what I would check too.
BIDS and SSMS were running under your security.
I doubt that SQL Agent is running under your credentials.
December 19, 2013 at 8:49 am
Afternoon all
Many thanks to those of you who've replied - I really appreciate you taking the time.
You were, of course, all right - it was a security issue. I've given the SQL Server Agent account permissions on the folder where the Excel files were being stored, and hey presto...
So, I have one further question, if that's okay.
First thing this morning, having read some of the replies and got the gist that it might be a security issue, I decided to see if I could get the job to run as me, rather than the SQL Server Agent (just as a test). I discovered that in the "Run DTS Package" step of my job, there's the Run As field. I found, of course, that I couldn't say "Run as me", or indeed specify any other user's name. But, I was able to select SSIS Package Proxy... and that this then worked.
So, could anyone explain what this SSIS Package Proxy account should be used for, and why giving the SQL Server Agent account permissions on the folder (the solution I've adopted following advice here) is a better solution than choosing to run the job as the SSIS Package Proxy?
Many thanks again for your help
Andrew
December 19, 2013 at 9:10 am
Several reasons come to mind
You might leave the company, SQL Agent probably won't. Depending on how they handle deactivation of AD accounts, it may not become apparent right away. But things will eventually break down.
You probably have far more access to many things that SQL Agent would never need.
Do you change your password? Would you remember to change the proxy?
Has your account ever been locked out?
Say you changed postions within the company. Your permissions would likely change, and this could cause the job to fail.
I would tend to use a proxy and a priviledged ID only rarely.
I might be inclined to have a file access account created to use with SQL Agent. This could have some value, especially if you had different SQL Agent accounts on different machines, but they all had similar file access needs.
December 19, 2013 at 9:14 am
Hi again
Sorry - I may not have explained myself clearly.
I absolutely understand why I shouldn't have it run under my own account (and why that's not even an option) - I was only planning to try this as a test to see if it resolved the issue, and moved me a step closer to the permanent solution.
No - my question was why should I give SQL Server Agent permissions on the folder, rather than using "SSIS Package Proxy" which was the other option which worked. What is this "SSIS Package Proxy" account, and what should it be used for?
Thanks a lot
Andrew
December 19, 2013 at 9:16 am
I've only used a proxy when a vendor app wanted thier domain account (as opposed to my Agent's domain account) to run an SSIS package from an Agent job. It's their app, but its my sql server, so the Agent account is one that is privileged appropriatly for my database backup method. So in order for the Agent to run their SSIS package, either the Agent account gets the necessary privileges to run the package or a proxy is created allowing their account to run SSIS from the Agent.
December 19, 2013 at 1:24 pm
You use Package Proxy when you want to run it under a different account to the agent. Its useful if you have loads of different boxes and you don't want to chuck the SQL Agent account everywhere.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
