November 20, 2009 at 12:37 pm
I have a package that loads data into a table from a flat file and it runs fine, when scheduled as a job it also runs fine. The problem here is that every time it is ran as a job, the job executes successfully but the data does not load. Anybody ever experienced this? Any help on how to fix this? Could it be a permission issue? Pretty clueless at this point !
November 20, 2009 at 1:04 pm
johnsonchase7 (11/20/2009)
I have a package that loads data into a table from a flat file and it runs fine, when scheduled as a job it also runs fine. The problem here is that every time it is ran as a job, the job executes successfully but the data does not load. Anybody ever experienced this? Any help on how to fix this? Could it be a permission issue? Pretty clueless at this point !
It may not be permissions but the installation drive of SSIS because when run as a job it uses Win32 folder which is in C drive but most companies install it in other drives and strange things happen.
Kind regards,
Gift Peddie
November 20, 2009 at 1:20 pm
Interesting, is there a way to fix this that you would suggest?
November 20, 2009 at 1:29 pm
There can be some other reasons for this strange behavior, so I would suggest you enable logging of the package (if not done already) and look for some information that you may be missing right now.
When you say it is successful, does the package execute without any error being reported?
In the Job step property what is the value corresponding to Run as: Check that account has admin level access rights on that machine.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 1:51 pm
Hey Bru and gift, thx for your response. How would I enable logging of the package? Yes the package runs without any errors being reported. The run as is under "sql server agent service account" Where do I check for the admin level access rights on it? Thanks
November 20, 2009 at 2:18 pm
Open the package in the BIDS, right click anywhere in the background of the control flow and select the first menu option is logging. Configure SSIS Logs window appears and choose the type of log file you wish. I prefer text format for easier readability and editing purpose.
After choosing the file, move to the Details tab to the right and choose as many events to be logged.
Save the package, and build and deploy it back to the SSIS server and execute using the sql agent.
You should see some information logged into the file that you configured.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 2:42 pm
johnsonchase7 (11/20/2009)
Hey Bru and gift, thx for your response. How would I enable logging of the package? Yes the package runs without any errors being reported. The run as is under "sql server agent service account" Where do I check for the admin level access rights on it? Thanks
Check the thread below for custom logging including using tables with code you can use.
http://www.sqlservercentral.com/Forums/Topic771588-364-1.aspx
Kind regards,
Gift Peddie
November 20, 2009 at 3:01 pm
Hey Bru, thanks for that. how do i add the sql service agent account to be a member of the local admin group?
November 20, 2009 at 3:49 pm
Gift,
Thanks for the helpful link, i set up a log for the package and the same thing happened, when i run the package manually, i see the events logged bbut when i run it with the service agent account, it doees not log anything but the job runs successfully.
November 20, 2009 at 6:50 pm
johnsonchase7 (11/20/2009)
Gift,Thanks for the helpful link, i set up a log for the package and the same thing happened, when i run the package manually, i see the events logged bbut when i run it with the service agent account, it doees not log anything but the job runs successfully.
Hi Johnson,
Here is the Agent account requirement fact page if you cannot create the account get a system admin to create it and then go into configuration manager right click on the Agent go to properties and change the account with the new one with Admin level permissions. Post again if you still need help.
http://msdn.microsoft.com/en-us/library/ms345380.aspx
Kind regards,
Gift Peddie
November 25, 2009 at 6:07 am
Hi
You may also want to check for any truncation errors at either the data flow Source or Destination and how these are being handled in the error output of the transformation.
If you are ignoring the truncation errors, you may be getting errors on every row but task will complete sucessfully.
Cheers
-Matt
November 26, 2009 at 11:22 pm
Hi,
Please add data viewers in dataflow and check whether records are inserting in destination or not.
Regards,
Ashis
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply