Solved

SSIS package running fine from visual studio, but not from Sceduler ?

Posted on 2013-01-26
5
291 Views
Last Modified: 2016-02-10
Hi!

When i run my OLAP package (SSIS)  from visual studio. It runs just fine.

But setup this package to run from Microsoft server manager..
as a sceduler job it fails....

What is wrong ?

Olso try to run the pack from dos promt, using -> dtexec.exe
And here it works fine to

Please help
0
Comment
Question by:team2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:liija
ID: 38822461
How have you taken care of user authentication in the data sources?
If you haven't defined anything inside the agent job - or are not using e.g. package configuration files, the agent service credentials are used. Probably it doesn't have rights to the data sources.

Check out this:
http://www.mssqltips.com/sqlservertip/1180/sql-server-integration-services-package-scheduling-with-sql-agent/
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38823076
<What is wrong ?>

What is the error?

make sure you do not use C:\yourPCfolder
use instead UNC path:

\\servername\SharedFolder_With_NTFSSqlserverLoginPermissions_That_youSetSqlAgentJob
0
 
LVL 2

Author Comment

by:team2005
ID: 38823937
Hi!

Get tis errors:

Error: Failed to acquire connection "Olap". Connection may not be configured correctly or you may not have the right permissions on this connection.
0
 
LVL 6

Accepted Solution

by:
liija earned 500 total points
ID: 38825814
My first comment applies.
Most likely you are trying to run SSIS package with Agent Service user account - which might be local system account. Agent Service user account doesn't have access to the OLAP cube  you are trying to process.
There are several different ways to get around this, here are a couple of them:

- Implement proxy account:
http://www.buildingmeaning.com/?p=89

- Check the link in my first post. In agent job there is a tab 'Data Source'. There you are able to manage the connections within the specific package inside the scheduled job.

- Implement package configurations.
http://www.simple-talk.com/sql/ssis/xml-configuration-files-in-sql-server-integration-services/

- Hardcode the user credentials into the SSIS package. Now you are probably using trusted connection. When you run this scheduled it won't work. Add a user and password inside your SSIS package. Set on the package security option 'EncryptSensitiveWithPassword'. This encrypts the connection credentials. Use naturally the same password in SQL Server Agent Job.
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38863313
thanks
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question