Member Avatar for renvilo

Hi guys, I need some help please. I am new to SSIS. I created a SSIS in BI and when I run it it works fine (All it does is to run a select statement to get data and save it into a flatfile. The server I'm accessing is on a different machine.)

So I save it to the SQL Server (my local pc) with security set to: ServerStorage. When I try to add this job to my SQL Agent it gives me an error in my Job Log:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
02/05/2009 15:23:07,X,Error,0,ZAWWHS48184,X,(Job outcome),,The job failed. The Job was invoked by User AG\RVilonel. The last step to run was step 1 (X).,00:00:17,0,0,,,,0
02/05/2009 15:23:11,X,Error,1,ZAWWHS48184,X,X,,Executed as user: NT AUTHORITY\NETWORK SERVICE. ... Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 03:23:11 PM Error: 2009-02-05 15:23:23.86 Code: 0xC0010018 Source: Description: Error loading value " {BDD4D117-AC6E-4713-B" from node "DTS:ConnectionManager". End Error Could not load package "\Xeik" because of error 0xC0010014. Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. Source: Started: 03:23:11 PM Finished: 03:23:23 PM Elapsed: 12.047 seconds. The package could not be loaded. The step failed.,00:00:13,0,0,,,,0

Date 05/02/2009 03:39:21 PM
Log Job History (Xeik)

Step ID 0
Server ZAWWHS48184
Job Name Xeik
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User AG\RVilonel. The last step to run was step 1 (Xeik).

The Database I'm accessing is on a different machine and it uses a different username and password. But why will it work fine in BI but I can't flippen schedule the thing?


I have all the right I can on msdb...

MS also said to replace the datetime with slash (/) in the package file but it's already in slash format.

  • 2 Contributors
  • 9 Replies
  • 157 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Ramy Mahrous
Member Avatar for Ramy Mahrous

Check the Job on SQL Server use the same connection string used in SSIS project, and check the permissions granted to this user AG\RVilonel on the targeted server can perform maintenance tasks!

Member Avatar for renvilo

Thanks man.

Well the SQL server I'm connecting to has a diff username and pass. I'm connecting with drlink2 as the username... ag\rvilonel is on my local machine...

drlink2 has this access:

eplivedb db_datareader

epihndb db_owner
ReportServer db_owner
ReportServerTempDB db_owner

the connection has to go to the eplivedb database...

Should I rather request my own user account? With what security?

But why is this working in BI and not in SQL?

Thanks again

Member Avatar for Ramy Mahrous

In BI I think you mentioned that you connecting to your local server but in SQL you're connecting to another server! right or I misunderstood!

Member Avatar for renvilo

No in BI I setup a package to connecto to DR server with the drlink2 username and password, then it has to save the file in a flatfile on the network. If I run this in BI it runs 100%.

If I add this to a job in SQL it doesn't work at all... They do give me limited access on the DR server because it's very important data and and and.... lol...

So the BI and the SQL uses the same SQL Statement and they both connect to the same server...

If I run the package (not in BI or SQL, just running it) it works as well.

Member Avatar for renvilo

Hi,

Ok I did test what they said on that site.

I use their method 2 when saving to the SQL server (also tried 1 and 3 no diff)

Then I tried the dtexec.exe with the logging...

It worked just fine. Can I schedule this or not? It worked 100% so... I just don't want to run the job every morning.

Thanks

Member Avatar for Ramy Mahrous

From Management->Maintenance plans->Right click on your plan->Subplan schedule..

Member Avatar for renvilo

Yes man, I can only do a maintenance plan that is part of my SQL agent jobs...

I can't use Maintenance plans to export the data to a flat file....

Member Avatar for Ramy Mahrous

Create new Job and select your SSIS package and schedule it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.