Solved

Scheduled SQL Server Agent Job "SSIS Package"  Failed.  Job Failed  Import Data from remote database

Posted on 2013-01-11
4
2,613 Views
Last Modified: 2016-02-10
Hi Experts,

I imported data into a "test database" using "Import Data" wizard, the "Data Source" is a remote database, with "read-only permissions".  

>I chose the "Write a query to specify the data transfer" so I can only import certain tables.

>Then I chose to "Save" the "SSIS Package" so I can later use it to create a scheduled SQL server agent job.  I also chose to "Execute Immediately".  

I was able to import the data to the "test database" successfully!  

But here is the problem.  I apologize! I am rookie at this!  My goal is to schedule an "Import Data" from the remote read-only database to the local "test database".


I created a scheduled job to run the "SSIS package".  Unfortunately it failed and gave me this error.  

Message
Executed as user: DOMAIN\administrator. ...00.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:17:44 PM  Error: 2013-01-11 15:17:45.07     Code: 0xC0202009     Source: Reports Connection manager "SourceConnectionOLEDB"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for the user.".  End Error  Error: 2013-01-11 15:17:45.07     Code: 0xC020801C     Source: Data Flow Task Source - Query [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-01...  The package execution fa...  The step failed.


Is remote database logon/password written into the "SSIS package"?
Is this a permissions issue?

To summarize this issue:

I am able to successfuly "Import Data" from a remote database into the local database.
I used the saved SSIS package from the "Import Data" to create a schedule SQL Server agent job.  

Job Failed:
Message
The job failed.  The Job was invoked by User DOMAIN\administrator.  The last step to run was step 1 (Reports).

Please help!  Thank you!

Don
0
Comment
Question by:TFHDIT
4 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 300 total points
ID: 38769282
Is this a permissions issue? => Yes
Is remote database logon/password written into the "SSIS package"? => No, not by default, you can do it but is in clear text somewhere then
Once set up as a job by default the package will run under the user that SQL Server Agent was set up with, evidently "DOMAIN\administrator" in your case.  If you can give that user access to the remote database, that might solve the problem.  There is also an option to "RUN AS" you can try playing with.
Permission issues with setting up and executing SSIS packages from Jobs always seems a little tricky to me, I have to work through issues.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 100 total points
ID: 38769395
try to change the sql agent job owner to Sa
and\or
add DOMAIN\administrator to the destination sql logins with the db write\or dbo permissions
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 100 total points
ID: 38769555
Job Owner and the check REmote SQL Instance Credentials. Those have permissions to access the data or not.
0
 

Author Closing Comment

by:TFHDIT
ID: 38769602
Definitely a permission issue. All your recommendations helped me understand and successfully run the ssis package/SQL agent job.
Thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now