September 24, 2007 at 12:53 pm
Hello Forumers,
This is the actual error message I received minus actual job name. Can't display b/c of company guidelines and procedures. Hopefully, I can explain my problem so I can obtain good feedback. Here goes. The objective is create an automated scheduled job that will run daily. The SSIS Package I created in Visual Studio 2005 executes to completion in debug mode with no warnings or errors. So, I ruled out problems with the package. Now, before I copy the SSIS Package to the server, right clicked in the Package on the Control Tab in MS Visual Studio 2005 and selected logging, It should bring up a Configure SSIS Logs dialog box with all tasks contained under package on the left hand side and two tabs (Providers and Logs, Details) on the right hand side. I configured my log to write log to Event Viewer. That's easier for me. If I need explain Event Viewer, I will later, it's an Admin. Tool. Next, all completed SSIS Packages can be copied to the server. I copy the package to the server with encrypt password. I'll explain if someone has a question about it. Just don't want to get too verbose.
Next, in MS SQL Server 2005, I expanded the SQL Server Agents section, opened the Jobs folder. Right Clicked, created job. I will explain what I did in each section under this error message:
Note: (MS SQL Server Agent Service is Automatically started on this Server box.)
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 9/21/2007
Time: 11:00:00 AM
User: N/A
Computer: ServerName
Description:
SQL Server Scheduled Job 'JobName' (0x5C2C51191AA26749ADF7A9763514F4FC) - Status: Failed - Invoked on: 2007-09-21 10:32:12 - Message: The job failed. The Job was invoked by Schedule 20 (Schedule Name - Runs Daily). The last step to run was step 1 (PackageName).
There are six sections of the job: General, Steps, Schedules, Alerts, Notifications and Targets. My focus are General, Steps, Schedules and Notifications.
Sections of Interests:
1)General
--> Filled in the Name: JobName, Owner: sa, Category: Uncategorized[Local] and Description: None
2)Steps
--> Steps I have already added copied SSIS Package off of sever to this job. Actually, I am going to explain how I configured this step. Filled in the Name: StepName, Type: SQL Server Integration Service Package, Run as: SQL Agent Service Account (I have be told to try running it under a different account. I tried and that approach doesn't work either.) There are 9 tabs under the General Page of a SQL Agent Service Account: (General, Configurations, Command files, Data Sources, Execution options, Logging, Set Values, Verification and Command line). My focus are General and Data Sources
a) General -- Package Source: Sql Server, Server: ServerName
-- Log on to the Server info: Selected Use Windows Authentication radial button
-- Package: \PackageName (This is the Name of the SSIS Package Copy of the I saved from MS Visual Studio 2005 to the Server)
b) Data Source
-- Just made sure my data source connections were present
Advanced Page of a SQL Agent Service Account: On success action: Since I only have one step, I set this to Quit the job reporting success, Retry attempts: 0, Retry interval(minutes):0, On failure action: Quit the job reporting failure. All othter fields, I left blank.
3)Schedules
--> Name: ScheduleName, Type: Recurring, Enabled Checkbox selected, Frequency: Daily, Recurs: 1 days, Daily Frequency: Occurs at 4:30:00 AM, Duration: Start Date: Today's Date, Summary: Description - Synopsis of what I selected.
4)Notifications
--> Checkbox Write to the Windows Application event log: When the job fails selected
Hopefully, what I wrote, someone can following what I did and tell me what may be the problem. Look forward to some good feedback.
Mike S.
September 24, 2007 at 3:55 pm
How did you accomplish the deployment to your production system? Did you go through the build process in VS 2005?
😎
September 24, 2007 at 8:19 pm
What's the package protection level set at? It shouldn't be the default - EncryptSensitiveWithUserKey. What account is being used to run the SQL Server Agent service? Is it LocalSystem?? If so, consider using a windows domain account instead. Briefly, what your package do? Is it trying to access resources that are not a part of the ssis server?
September 25, 2007 at 6:22 am
How did you accomplish the deployment to your production system?
I deployed the SSIS Package to our Data Warehouse server from MS Visual 2005 by File| Save Copy of Package.dtsx As. A save copy of package dialog box appears, which contains asked information such as Package Location (Answers: Sql Server, File System or SSIS Package Store) I used Sql Server; Server (only one, which is local), Authenication area- Authenication Type: I used Windows Authentication, instead of Sql Server. I was told by Supervisor, I shouldn't need Sql Server Authentication.
Package Path: saved in the SSIS Packages folder
Protection Level: I used Encrypt sensitive data with passsword and I clicked button on it's right side to input password and saved and it saved a copy to the server for the I could insert that copy into the job as an item.
Did you go through the build process in VS 2005?
Do you mean in VS 2005, select menu item Build | Build SSIS Package ? If so, I did and it gave me a status of Build Suceeded.
September 25, 2007 at 6:49 am
Grasshopper, first thank you for your response and good questions. Let me see if I can answer best of my knowledge here.
What's the package protection level set at? It shouldn't be the default - EncryptSensitiveWithUserKey. My protection level for my SSIS Package is set to EncryptSensitiveWithPassword and I have added that password, saved it and then build that package before making a copy to the server. What account is being used to run the SQL Server Agent service? SQL Agent Service Account. I tried to run as jobproxy and it did not work. Is it LocalSystem?? If so, consider using a windows domain account instead. Local, Yes. Ok, please explain how to create this window domain account. I am a novice at SSIS. Briefly, what your package do? Brief synopsis of what my package does. It does an extract of data from requested tables from source system, which is on another server. Loads that data exactly like it was in the source system, basically a SSIS Stage Level Extract. Is it trying to access resources that are not a part of the ssis server? |
Yes.
September 25, 2007 at 7:04 am
Just to be sure, when deploying your package to the production servr, did you run the Package Installation wizard by double clicking on the manifest file?
September 25, 2007 at 8:15 am
Ten Centuries (Lynn), thank you for your feedback.
Just to be sure, when deploying your package to the production servr, did you run the Package Installation wizard by double clicking on the manifest file?
Please explain what you mean by double clicking on the manifest file. In MS VS 2005, Are you referring to the procedure to copy package to the server? OR Build the Package? (It's only one step there and that's to select Buidl Package and it's either going to say Build Suceeded or Failed) Or MSSMS (Microsoft Sql Server Management Studio), creating the Job. Or the Step section to add the SSIS Package copy to the file?
Just throwing out stuff here. I am a novice.
September 25, 2007 at 9:29 am
When you build the SSIS package for deployment, you create a set of files in a deployment directory for the project, usually bin/deployment. In this directory is a manifest file: .SSISDeploymentManifest. when you double click on this file, it starts the Deployment Wizard whcih copies the files to the target server running SSIS engine (production). You need to be sure that you have the proper access to the server (and the file system if deploying to the file system).
Is this the process you are following in deploying your SSIS packages?
September 26, 2007 at 9:07 am
Please review attached file I have added with screen shot. I want to setup a command file instead of using command line. 😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply