?
Solved

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

Posted on 2013-01-11
4
Medium Priority
?
2,727 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
[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
4 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 1200 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 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

764 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