June 17, 2009 at 7:24 am
I have a four step SSIS package that performs the following steps:
1 - extracts data from MySQL and creates a csv file
2 - extracts data from MySQL and creates a second csv file for historical purposes
3 - Truncates the MySQL table
4 - renames the file in step 2 adding datetime stamp for historical purposes.
The package runs perfectly from within SSIS however it doesn't run correctly when scheduled from an agent.
The first 3 steps run fine when called from an agent but the fourth step actually deletes the file created in step 2. It gives me no errors and shows to have run correctly.
Has anyone seen this before and does anyone have any recommendations? I have searched the forums and the net and can't find any similar situation. Any help would be greatly appreciated.
June 17, 2009 at 7:30 am
Check to make sure the file system rights are the same for the login the job is using and the login you're testing from.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 7:53 am
I went down that path unsuccesfully. I changed the SQL Server Agent Service to use the domain admin account that I created the package with. I also set the file system to Full Control for the domain account I am using and replaced permissions on the subfolders.
Unless there is something else to check that I am missing I think I have covered this. Am I missing something obvious? My head is stirring after two days of dealing with this so maybe I am overlooking a security setting somewhere.
I appreciate the suggestion and please let me know if you have any more
June 17, 2009 at 8:23 am
Update -
The file is actually getting renamed but it is also getting Moved to C:\Windows\System32 which is why I couldn't find it and thought that it was being deleted.
When I run it from the SSIS package the renamed file gets stored in d:\Folder\Historical
Any idea how this could happen? It's really a much different issue now than I originally thought.
June 17, 2009 at 8:31 am
robert.jones (6/17/2009)
I went down that path unsuccesfully. I changed the SQL Server Agent Service to use the domain admin account that I created the package with. I also set the file system to Full Control for the domain account I am using and replaced permissions on the subfolders.Unless there is something else to check that I am missing I think I have covered this. Am I missing something obvious? My head is stirring after two days of dealing with this so maybe I am overlooking a security setting somewhere.
I appreciate the suggestion and please let me know if you have any more
That account must be admin in SQL Server and the Server with the file system, check the thread below for more info.
http://www.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
June 17, 2009 at 9:18 am
Gotcha, we are good there.
Since the file is getting created (just in the wrong place) I don't think it's a security issue at all.
I had a counterpart try this as well on a completely different SQL Server environment and domain and he was able to recreate.
Perhaps I should start a new post b/c the prob is different.?.?.?
When we use the rename task and run from an agent the file doesn't go where it belongs, it goes into C:\Windows\System32. This does not happen when run from the SSIS package itself.
I don't know if it matters but here is the ConnectionString expression that I am using on the Destination File to rename it.
(DT_WSTR,2)Datepart("Mm",GetDate())+(DT_WSTR,2)Datepart("Dd",GetDate())+(DT_WSTR,4)Datepart("yyyy",GetDate())+(DT_WSTR,2)Datepart("Hh",GetDate())+(DT_WSTR,2)Datepart("Mi",GetDate())+"dpt_xfer.csv"
Thanks!
June 17, 2009 at 9:33 am
When we use the rename task and run from an agent the file doesn't go where it belongs, it goes into C:\Windows\System32. This does not happen when run from the SSIS package itself.
What I gave you is correct what is wrong is your company installation of SSIS which is not a configurable component which means it must be installed in your C drive but it is in d drive I am assuming the Agent must check only C drive to execute filesystem tasks. When execution is in the Agent context it helps to implement as intended.
Kind regards,
Gift Peddie
June 17, 2009 at 9:42 am
Wow, I had no idea about that. This does leave me some options (not one of which is reinstalling SQL) now that I know the cause.
Thank you for all of your help,
Robert
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply