Running ETL Package Using SQL Server Agent Over 2 Different Domains
Oct 3, 2007
Okay, i've got an ETL package which works flawless, we've created a scheduled job for it which also worked.
This ETL accesses a flat file located on a server on a different location in the same domain.
Last week we start a migration to a new network, the location where the flat file is stored has already moved to the new domain but the SQL server agent is still on the old domain.
There is a trust between the 2 domains, the network user from the old domain has been added on the server in the new domain where the flatfile is located.
If i run the etl package manually it works flawless, now if i run the etl using the SQL server agent it won't run anymore.
I get the following error:
Executed as user: OIdDomaindwh. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:15:00 Error: 2007-10-02 21:15:02.10 Code: 0xC001401E Source: Rolmer_Product Connection manager "Flatfile Product" Description: The file name "\fs-cha-fs01dwhproduit.csv" specified in the connection was not valid.
Don't tell me its a problem related to the user that the SQL Server Agent is set to because its set to OldDomaindwh which is the same user i use to login on the server where i can manually run it without problems. The problem only occures when i run it using the SQL server agent which uses the same user to execute packages.
This is a very difficult problem i doubt anyone here will be able to crack this nut, but if they do i'll be very impressed because it would require a real smart network admin with SQL experiance to figure this one out...
Good luck
View 3 Replies
ADVERTISEMENT
Aug 31, 2007
I get the following error:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:MyFolderMyRptsMyApplicationMyDatabase.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
The above path "I:...." is mapped on the server that SQL Server is running on.
This package runs with NO problems in MS Visual Studio.
View 1 Replies
View Related
Mar 3, 2008
Hi
I have setup a step in SQL Server Agent to run a SSIS package that I have created. However the step fails straight away and refers me to the history log, which doesnt seem to show what the problem is.
I've tried running the package manually through dtexec.exe and it runs through fine. Does anyone know what the problem could be?
Thanks
View 9 Replies
View Related
Mar 19, 2008
Hello,
I have created a package that runs without problem.
I run the package with the command dtexec /F "package_name.dtsx" > package_name.txt.
Then I run the same package from SQL Server Agent, everything is OK
Then, I tried to edit the command line to have the output file, but I got an error.
The command line is:
dtexec /F "package_name.dtsx" MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E > package_name.txt.
(MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E are created by default)
How can I do?
Thank
View 4 Replies
View Related
Nov 23, 2010
My package is connecting to an external data provider using an OLEDB driver . The package runs fine in debug mode.When i tried to run the same from SQL server agent it failed to aquire the connection. The OLEDB provider does not contain too much of information , ( connection string, initial catalog, blank user name and password).The same package executes successfully if i run using dtexec in BAT file.But if i use the dtexec in sql server job step as operating system command and try to run, the job will fail reporting " can not aquire the connection".
View 2 Replies
View Related
Apr 1, 2008
Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
View 8 Replies
View Related
Aug 28, 2007
Hi,
I'm testing my IS package in my local PC which extracts data from one SQL Server table to another SQL Server database. In BIDS, it's running without error. I also tried it in cmd with DTExec /f "c:package.dts", it also ran ok. I tried assigning it in SQL Agent as job. with same command DTExec. This time, it ran successfully but got 0 rows. I dunno what went wrong, why suddenly it cannot get data from the source.
Any idea?
thanks!
cherriesh
View 3 Replies
View Related
Jun 18, 2008
Hello,
I'm new here and hope you will be able to help me.
I have created several SSIS packages with Visual Studio 2005.
They all work fine in debug mode.
I have been able to make them work with a ODBC connection by using a ADO.NET connection.
Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.
All the packages using the ODBC connection fail with the following error :
Login failed for user XXX
Error : 18456; Severity : 14 , State : 8
This error is a password mismatch.
I tried several database users and checked the passwords multiple times.
It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.
Can you help me?
Thanks,
Ares
View 19 Replies
View Related
Oct 19, 2007
I have a number of packages that I have moved from an old server. Each package was scheduled with a SQL Agent job. On the old server everything ran fine. All of the packages run fine from VS, from DTEXECUI and I have tried one from the command line with DTEXEC and it worked.
When I run from the SQL Agent job, I don't get a failure, the package just hangs. I let one of the agent jobs sit for an hour with no progress. The package typically takes about 15 minutes to complete.
Below is the output from my package log up to the point that it hangs:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnInformation,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,1074016266,0x,Validation phase is beginning.
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,0,0x,Validating
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,50,0x,Validating
I've been reading all of the posts concerning using a Proxy. I tried to set that up and got the same results.
Any help would be greatly appreciated.
View 4 Replies
View Related
Jan 18, 2007
hi all,
thanks for the valuable information all the time!!! saved me a lot of time...
our team developes a system for text data improvement using ssis .
we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .
my question is:
what is the best way to do this?
if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?
or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.
do we need to install sqlserver on these "ssis servers"?
thanks for your replies!
View 9 Replies
View Related
Mar 6, 2008
Hello-
I'm trying to deploy a SSIS package and am having issues getting it to execute from a SQL Agent job. I've read this article http://support.microsoft.com/?kbid=918760 over and over and although it's helpful I must still be missing something.
The SSIS package uses a configuration file for connection string information and the ProtectionLevel is set to DontSaveSensitive. I'm using a proxy account with the appropriate permissions. I can execute the package from SQL Management Studio and from dtexec.exe just fine. I even copied the command line out of the job and used that to execute it with dtexec.exe and it worked.
I'm not sure what to try next. It's like it's not even getting to the SSIS package. I have logging turned on in the package and I"m not getting back any errors from it. Any ideas? One thing that I think is strange is that the job is running on an instance of SQL but Integration Services doesn't seem to support instances. Could that be the issue?
I don't know what else to try at this point...any advice is greatly appreciated!
View 3 Replies
View Related
Jul 19, 2006
Hi,
I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.
The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.
When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.
I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.
Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.
Thanks,
Sam
View 8 Replies
View Related
Mar 9, 2006
I deployed my SSIS package to SQL Server 2005.
I have done the following:
1) Gave the sql agent account permissions to the msdb database and other databases
2) Checked the paths for "Data Sources" under properties of my agent in the SSIS properties
3) Set the authentification to Windows and used the sql agent account (a domain account that I setup) to run the agent. I also tried my login. So it's not a permission issue, because I do not get permission errors...it's past this.
What works:
1) Other SQL Agent packages...they are not running an SSIS package, just SQL
2) Running the SSiS package by right-clicking on it in Management Sudio. No errors found in the execution and the package does it's intended function
3) Package runs fine with absolutely no errors in VS 2005
What doesn't work:
1) My agent that points to my SSIS package. All this agent does is run that package. When I run the agent, I get a minimal error message in the job history logs stating the following error message:
03/09/2006 12:49:58,Run EN Process,Error,0,BG-22SQL,Run EN Process,(Job outcome),,The job failed. The Job was invoked by User domainmy_account. The last step to run was step 1 (Run EN SSIS Package).,00:00:02,0,0,,,,0
03/09/2006 12:49:58,Run EN Process,Error,1,BG-22SQL,Run EN Process,Run EN SSIS Package,,Executed as user: domainsqlagent_account. The package execution failed. The step failed.,00:00:01,0,0,,,,0
View 4 Replies
View Related
Nov 30, 2006
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
View 1 Replies
View Related
Apr 28, 2014
Is it possible to configure transnational replication between two different domains also non trusted domains.
It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.
View 2 Replies
View Related
Jan 11, 2007
Hi,
I'd like to know if there's a way to pass parent package parameters to a package executed by SQL Server Job Agent? It appears that sp_start_job doesn't have any variable that could accomodate this.
Hope to hear your ideas.
View 7 Replies
View Related
May 9, 2007
Hi,
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
View 5 Replies
View Related
Jan 10, 2006
It is SQL server 7. Scheduled jobs were running fine until the hard drive crasked. Replace the hard drive and restored the dabases. However, all the scheduled jobs cannot run. I created new jobs using database maintenance plan and it still doesn't work. the same error occurs when viewing the Job history.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
However, if I create a job by right click on the database and select backup database. This job runs fine.
What should I do to make jobs created by the database maintenance plan running again?
Thank you for your help.
View 9 Replies
View Related
Dec 5, 2007
If a job is currently running and I want to disable it so that it does not run as scheduled for the following night, will it disrupt the current process?
View 1 Replies
View Related
Mar 6, 2007
I've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.
It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.
DTS DETAILS
The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.
The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:
JOB DETAILS
Name: TransferData
Enabled, Target = Local Server
Category: Uncategorized Local
Description: Execute package: TransferData
STEPS
ID: 1
Step Name: TransferData
Type: Operating System Command
On Success: Quit with Success
On Failure: Quit with Failure
PROPERTIES OF STEP
Step Name: TransferData
Type: Operating System Command (CmdExec)
Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137
Nothing special in the advanced tab
SCHEDULES TAB
ID: 20
Name: TransferData
Enabled: No
Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM
and is setup as Recurring
Nothing in Notifications Tab
I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.
I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.
Thanks in advance for any assistance with this.
View 8 Replies
View Related
Dec 6, 2007
Hi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pcs.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign keys, truncate tables, create foreign keys). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - cannot connect to database xxx?
Info - package is preparing to get connection string from parent ?
The child package then executes OK, does all its work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - timeout when connect to database xxx?
Info - package is preparing to get connection string from parent ?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
View 3 Replies
View Related
Jul 24, 2003
Hi,
I ahve attached the bmp doc that will show my screen snapshot.
My SQL server Agent service indicator is not showing green as like other server. I have started the serice and running fine. I don't have any problem running the job.
My curious is why that indicator is not showing up.
Thanks,
Ravi
View 5 Replies
View Related
Jun 3, 2008
HI ALL
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
Any Idea..
View 1 Replies
View Related
Jul 9, 2007
Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.
The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Unable to open shim database version registry key - v2.0.50727.00000."
Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."
We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.
This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.
Has anyone else had this problem?
Thanks!
View 1 Replies
View Related
Oct 13, 2007
Hi All,
I was working on some reports on SSRS. Now I cant view those reports coz I can not open report manager.
I noticed that SQLSERVER and SQL Server agent has stopped running in my local machince. But Other services are running properly (SSIS, SSRS, SSAS). When I go to SQL Configuration manager and try to run them, I get the following error massege.
" The request failled or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details. "
So now I can not connect database engine in my local machine through management studio. When I try to do that, I get the following error massage.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"
Also I can not access reportserver data base and reportservertemDB.
If I reinstall SQL Server again, what would happen? Would I still be able to use and view my old reports?
Please can anyone help me to get this worked again correctly?
Thanks
View 8 Replies
View Related
Jul 30, 2007
I get 2 errors when I run a job in sql server agent 2005 0xc0202009 and 0x80040E2. I cannot see why I am getting these errors, when I run the job with dtexec it is successful. Also when I run it in the debugger or run the executeable it is successful. But when I try to run it with the agent it fails. Any Ideas would be greatly appriciated.
thank you
Joey Kushinski
View 11 Replies
View Related
May 6, 2008
Hi, I was wondering if anybody would be able to help me and realise by looking round that is a reasonably common problem, but its starting to drive me mad now!!
Ive got an SSIS package which takes data from an excel spreadsheet and using a stored procedure updates a table in a database, before moving the spreadsheet to another folder once it finishes. This works fine when in the Business Intelligence Design Studio, but when I try to set it up to run from the SQL Server Agent I get nothing but problems. Ive had a look around various forums and seen that many people have had this problem and Ive followed advice that other people have given, but try as I might nothing will work for me.
Currently Im trying to go down the package configurations route. Ive enabled the use of package configurations in the development studio and specified the configuration to pick up the destinations, usernames and passwords of everything that I have in the Connection Managers. I then go to the SQL agent and as a step tell it to execute the SSIS package using the package configuration xml file that it has created. Both the SSIS package and the package configuration file are saved and been asked to run from the sql server I will be using. I then go to run the package and it thinks about it for a minute or so before falling over. The history then says the below:
Message
Executed as user: UKWACALORsql2k5svc. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:35:31 Progress: 2008-05-06 11:36:32.10 Source: Data Flow Task 1 Validating: 0% complete End Progress Error: 2008-05-06 11:36:32.15 Code: 0xC0202009 Source: WiReCAPSLoad1 Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2008-05-06 11:36:32.15 Code: 0xC020801C Source: Data Flow Task 1 Destination - cstCAPSLoad1 [32] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. ... The package execution fa... The step failed.
The DestinationConnectionOLEDB is pointing at a database on the same server as where I have the SSIS package and configuration file stored. Ive gone into the xml file and noticed that the passwords arent saved so Ive added the passwords manually into the file after everytime it says username=sa but this hasnt made a difference. Ive also made sure that the package is saved to dontsaveencrypted.
Ive gone through the following article:
http://support.microsoft.com/kb/918760
and tried to use things such as credentials but these havent helped either.
Im really not sure why its giving me such hassle, I have the SSIS package on the sql server, I have its package configuration on the same server, I have the databases it wants to access on the same server, Im using the sa account wherever I can to make sure I have no access / rights issues, so I really have no idea why this wont work. All I can think of is that the sql2k5svc account that the sql agent job then runs under doesnt have authority to get to the database or the package configuration file, but Ive added that account to have full control on the database that it will accessing, but again no joy. How do I change it so that another account can be used to run this, one that I know has all the authority required to access everything it should?? And why would the login for sa be failing when its the highest level account??
Ive read so many different articles on this problem and tried to follow so many different resolutions that I really dont know how any of this is meant to work anymore!!
Any help would be gratefully received!!
Thanks
Brad
View 16 Replies
View Related
Apr 18, 2008
Hi folks!!
I am new to installation of SQL Server 2005..I wanted to know while selecting Service Account Screen why Avoid running SQL Server Agent as the Local System account.????
T.I.A
View 2 Replies
View Related
May 29, 2013
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
View 4 Replies
View Related
Apr 30, 2015
I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.
We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).
Here what I have done so far:
I created the database
Created the file and successfully imported data into it using the Import Data feature.
I saved the SSIS package
Scheduled an Agent Job for this package to run at certain time,daily
At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.
Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.
View 9 Replies
View Related
Apr 10, 2007
Dear All,
I'm using SSIS package to extract text file, and load into SQL Server. I test the SSIS package import manually, it works. Then I'm using SQL Server Agent to set up schedule. According to the log, the job agent work at the scheduled time, yet job agent throw out the error message.
Message1:
The job failed. The Job was invoked by User sysAdmin. The last step to run was step 1 (Extract Data).
Message2:
Executed as user: SQLSERVERsysAdmin. The package could not be loaded. The step failed.
Now I only have 1 step and set up "on success action" then "quite the reporting success". Any clue? Thanks
will
View 11 Replies
View Related
Jul 5, 2006
Hi,
I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.
I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."
I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:Program FilesMicrosoft SQL Server90DTSBinnDTEXEC.EXE" /SQL "AFAReductionAFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\<anotherservername>PCStatsliveCurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."
Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.
Please let me know why I might get this error, when the package runs OK on its own.
Many thanks,
Keith.
View 6 Replies
View Related
Apr 11, 2008
I create a package in SSIS and I can run it without any problem in BI studio. However, if I deploy it to a sql agent package and run it thru sql agent job.. I keep getting an error one of lookup transfomation is invalide..
I don;t understadn I can run the package in debug mode but why i keep getting an error when I deploy to a sql agent job?
View 4 Replies
View Related
