Hi there,
if I have some packages (.dtsx) that are depolyed to Server A and I have SQL Agent installed on Server B - if I run a run a package from SQL Agent, does it run on Server A or Server B?
Thanks in advance.
I need some help from you on a problem with SQL Server Agent that I believe many of you have experienced. When searching on the web I find some loose answers, but it would not solve my problems. So here is my story.
1. I have created two SSIS packages, package 1 and package 2. Both packages have a Execute SQL Task making a select sql statement to a table, say Customer in AdventureWorks DB. Package1 also makes a Execute Pacakge Task to Package 2. Both packages have protectionlevel property set to EncryptSensitiveWithUserKey
2. Then I created two package configuration files per package, one indirect xml configuration and one xml configuration file. The former I created an environment variable called pack1, which was specifying the path and file name of second config file. Likewise, I did for package2. Maybe I could have reused thie first configuration file, but I am not sure since package1 calls package2.
3. Now I build and deploy my packages. Then I run my manifest file, going through the deployment wizard setting the necceccary properties like server name, user, password, I have also create the same environment variables on the deployment machine. I ensure that the package1 and package2 can be run under integration services (file system). And both package works. So far so good
4. Now I want to create a SQL server agent job that runs package1 and package2. Here I found an article that decribed How to Schedule and Run a SSIS package ( DTS ) Job in SQL Server 2005 http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp
5. I created a job executor account and then Create SQL proxy account and associate proxy account with job executor account as described. I created a new ssis job, specifying type ssis integration services, run as proxy, setting package source to SSIS package store, naming the server and package name (package2).
6. Now, I try running job1 which runs package2. ANd it works. I was aware if I needed to give file permission on users (read and execute) on my dtsx/dtsconfig/log files. So far so good
7. Now, I try to run job test which runs package1 (which also runs package2). But now the job fails :-(. The job runs for some seconds, but fails. I have to say that I have some script tasks with message boxes inside!!! hope this doesn't matter. I have created all neccessary accounts, proxies, credentials and so on. here is the view history
Date 17.10.2006 00:50:53
Log Job History (test)
Step ID 1
Server ABEO-GEIR
Job Name test
Step Name test
Duration 00:00:07
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: abeo-geir
as_jobuser. The package execution failed. The step failed.
Continue: This error is well known for you. It has been mentioned in many postings. Well, how do I go from here. Setting logging on the job results in only this information
PackageStart,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:40,17.10.2006 00:59:40,0,0x,Beginning of package execution.
PackageEnd,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:41,17.10.2006 00:59:41,1,0x,End of package execution.
Well, I read some place that I could run the job as type operating system (cmdexec). Here i could not choose Run as, but had to select SQL SErver agent account. NOw I got this message
Date 17.10.2006 01:05:21
Log Job History (test)
Step ID 0
Server ABEO-GEIR
Job Name test
Step Name (Job outcome)
Duration 00:00:00
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 sa. The last step to run was step 1 (test).
As you see, I can't come any further. I NEED HELP FROM YOU. I AM STUCK. I know many of you have solved this and I hope you can guide me through thise. I don't know how many hours or days I have used on this.
Regards
Geir F
I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is
"An out-of-memory condition prevented the creation of the buffer object. "
Anyway, the package runs successfully when it runs automatically under the job agent.
I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.
js40
Hi,
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!
Eero Ringmäe
Hi All
I have a meaty job that currently runs as a set of sql scripts under sql agent and I'm transferring this job to run as a SSIS package. I have managed to set it up so that it runs as a sql job (cheers guys!), and I'm watching the job run now, maxing out the CPU usage as it tries to run 10 different tasks at the same time . great stuff!
However, I've been thinking about how to view the progress of this job/package (the job only has one step; "run SSIS package"). With the current job as single scripts, in SQL agent you can view which step of the job you are at.... is it possible to somehow view which package task you're at (ideally with nice shiny yellow green and red updates like in debugging), or do you have to output each step result into a log file?
Oh, and another question! If i have a checkpoint in a package, and the package falls over, when the job is rerun through SQL agent, will it start at the checkpoint, or run from the start?
Thanks!
michal
We are just migrating to sql servr 2005. I have created ssis pkg that runs fine when run mnaulli under BI. However, when I setup the ssis pkg to run as a SQL job. It errors out. Err is listed below. Seems like it is running the job under the SQL Agent account which does not have permissions to access the server i am pulling data from. Can u assit.
1984-2005. All rights reserved. Started: 1:30:51 PM Progress: 2008-02-22 13:30:57.55 Source: DTSTask_DTSDataPumpTask_1 Validating: 0% complete End Progress Error: 2008-02-22 13:30:58.33 Code: 0xC0202009 Source: Laser- Connection manager "FTWSQL" 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 'CFC 0SQLA'.". End Error Error: 2008-02-22 13:30:58.33 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "FTWSQL" failed with error code 0xC0202009. There m... The package execution fa... The step failed.
They wre runnning fine until 4/21/2008 then all SSIS packages started to fail.
Simple SQL jobs are still running fine thouhgt and reporting success.
Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.
I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.
I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?
I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...
I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...
Anyway, any suggestions would be very much appreciated!
Hello!
I have SSIS Package that needs special permissions in network. I want to schedule SSIS running using SQL Agent. I don't want to other Jobs starts with special permissions. So, I decided to use proxy account to impersonate SSIS starting process by account with those permissions. I've perfomed following steps:
1. Create Login for the future proxy account and add it to sysadmins role.
2. Create Credential with account name and password information.
3. Create proxy account and give to it rights to run SSIS, CmdExec and ActiveX create.
4. Set Run as for step that starts my package to this proxy
5. Start my job.
and I receive error "Process couldnot be created for step 1 job xxx. Reason: 1314"
SQL Agent account and proxy accounts has also administrators rights on SQL Server computer and rights to login as service.
When I change step configuration to run as SQL Agent account job completes succesfull.
What do I do wrong?
Thx,
Olga
Is there any way to check from SQL code if a specific SSIS stored package is running like a SQL system view or stored procedure? Management studio option works fine but not if you need to determine if a specific package already is running or not?
View 3 Replies View RelatedWhen I run an SSIS package from the MSDB in SQL Server Management Studio it never shows up under 'Running Packages'.
I have confirmed that the package is indeed running.
I've also tried to check the packages running on the server programatically by using the GetRunningPackages() method. This too returns nothing when clearly packages are running.
Any ideas?
Malcolm Barclay
Disney Consumer Products UK
Hi All,
I have a requirement of logging the failure and the error of a job executing a SSIS package to text file.
However, when I go to job step -> Advanced, in the section "SQL Server
Integration Services Package" where that info to setup logging is, it
is blank and just says:
"There are no options for this command type"
Any ideas?
I thought that this might not be supported in Standard Edition as I am running standard edition. Then I checked on two other servers, where I have Enterprise edition with SP2. On one of the installations, I am able to give the log file, the other one still shows,
"There are no options for this command type"
What is the problem? Both of them I am using the sysadmin role account. I have proxy account created on both the SQL instances. I don't think this is the problem, but still I checked this.
Can anybody help me?
Regards,
Virendra
Hi,
Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?
What is the recommended way to monitor SSIS execution?
Thanks,
- Joel
Hi
Just a brief question,
is there a way of displaying a success or not success message when running (testing) an SSIS package on SQL 2005 ?
Because i have to go through the steps to see if there was an error or not and for large SSIS packages this is a pain.
With DTS 2000 after it ran you got a message saying successfull or not, is there a way to activate this in SQL 2005 ?
I suppose i could just run the job ... but I prefer the other way as sometimes a job fail is a permissions problem, not a SQL/datapump task problem...
I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"
These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.
e.g. REPLACE( @[User::ConString] , "Royalty", @[User::RoyDb] )
These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:
-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.
This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.
I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.
I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.
http://support.microsoft.com/kb/918091#appliesto
Any ideas welcome
We are using SQL Server 2005 Standard edition. We have the development studio installed on a developer€™s workstation. The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.
The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server. I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.
When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.
I€™m sure this is a permission based problem since it runs fine on my workstation. I tried logon onto the SQL Server with my id (I€™m an admin to the server). I tried running the agent with my windows id. It looks like I€™m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.
In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.
I seem to be missing something with SQL 2005 SSIS and I am wondering how I would accomplish my taks. With SQL 2000 and DTS we designed our DTS packages so that we could load one file or database from start to finish. We then have controlling programs written in VB that will monitor directories, when a file shows up in a directory it loads the package, changes the necessary properties and executes it. These programs handle all of our error notifications and dealing with the files afterwards, making sure files are complete in the directory before the DTS runs, etc. With .NET and SQL 2005 I have learned that I can change the properties by using variables and expressions, which I think would handle all of our situation but I can not run them remotely. The documentation says that I could setup a scheduled job and then call sp_start_job from my program. That seems like it would require a lot of scheduled jobs and then I still need to get the parameters into the packages. The other option is to use a web service, that would require IIS on my SQL box which everywhere says I don't want. Am I using SSIS for something I am not supposed to be or how do I accomplish this in 2005?
I have noticed that you can not even run packages through the SQL Server Management Studio. We have a number of packages that are built that are run on demand when needed. In SQL 2000 I could just click and execute the package. In 2005 is my only option to setup some sort of fake job in the job scheduler?
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
I have created ssis package. It has been successfully running at UI level.
But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error
i have entered the command as follows.
dtexec /f "c:somefolderpackage1.dtsx"
Any points regarding this issue would be helpful.
Hello,
I currently use DTSRun to run packages on my scheduler box. I would like to use the same set up to run SSIS packages.
Is there any way to duplicate this without installing SSIS on my scheduler box? Can I specify the server on which it should run?
Just don't want topurchase another license, if possible.
- Dave-
I have a SQL Scheduled Job in SQL 2005 SP2 that executes a few different SSIS packages. When I update the SSIS Packages, SQL Agent runs the job and shows success; however, the underlying packages are not executed. The packages are located on the server but we are using UNC paths in the jobs since much of the time they are started manually from a remote workstation and the jobs seem to look at the initiating workstation's file server rather than that of the SQL Server. Is this a known issue?
Thank you,
Jennifer
It is clear to me that in order to be able to use certain SSIS components (for example the Excel jet provider) I must launch my packages using the 32bit DTEXEC located at Program Files (x86)Microsoft SQL Server90DTSBinn. However, when I do this it seems that there are other components of the package that no longer work as expected.
To test this I have created a simple package with two tasks (Run64BitRuntime is set to False):
1. Data flow task importing data from Excel
2. Execute SQL Task which does a simple select (select 1) from a Native OLE DB SQL data source (same SQL Server on which packages are stored). This task contains no input or output parameter.
When I try to execute the package using the 64bit DTEXEC, task 1 fails with the following error (as expected):
Code: 0xC0202009
Source: connection1 Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
When I execute the package with the 32bit DTEXEC, task 2 fails with the following error
Code: 0xC002F210
Source: Execute SQL Task Execute SQL Task
Description: Executing the query "" failed with the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Now here is the confusing part: When I change task 2 to use the .Net provider instead of the OLE DB the package works fine. According to the MS documentation, both of these providers are supported on 32 and 64 bit so am I missing something? One more thing to note: before I was able to use the 32 bit DTEXEC I had to re-register it as described in this KB article: http://support.microsoft.com/kb/919224
Hi,
I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.
The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.
Here is a sample list of ssis-packages from procedure:
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataaaa.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedatabb.dtsx" '
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataccc.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataddd.dtsx"'
exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataeee.dtsx"'
So, if eg bbb.dtsx would fail, is it possible to get the whole procedure failing?
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.
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!
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!
Hi, I was wondering if anybody would be able to help me and realise by looking round that is a reasonably common problem, but it€™s starting to drive me mad now!!
I€™ve 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. I€™ve had a look around various forums and seen that many people have had this problem and I€™ve followed advice that other people have given, but try as I might nothing will work for me.
Currently I€™m trying to go down the package configurations route. I€™ve 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. I€™ve gone into the xml file and noticed that the passwords aren€™t saved so I€™ve added the passwords manually into the file after everytime it says username=sa but this hasn€™t made a difference. I€™ve also made sure that the package is saved to dontsaveencrypted.
I€™ve gone through the following article:
http://support.microsoft.com/kb/918760
and tried to use things such as credentials but these haven€™t helped either.
I€™m 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, I€™m using the sa account wherever I can to make sure I have no access / rights issues, so I really have no idea why this won€™t work. All I can think of is that the sql2k5svc account that the sql agent job then runs under doesn€™t have authority to get to the database or the package configuration file, but I€™ve 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 it€™s the highest level account??
I€™ve read so many different articles on this problem and tried to follow so many different resolutions that I really don€™t know how any of this is meant to work anymore!!
Any help would be gratefully received!!
Thanks
Brad
Hi Everyone!
I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?
"C:Program Files (x86)Microsoft SQL Server90DTSBinndtexec.exe" /F "F:ProjectsSSISCustomerMaster_1CustomerMaster_1Package.dtsx" /CONNECTION "APLUS70F70.AKIN";""uid=AKIN;Dsn=APLUS70F70;"" /CONNECTION "CRMPSQL.IBT_Aplus";""Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Thanks in advance!
Anthony Akin
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
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
We are in the process of upgrading to a new SQL 2012 server but we have many packages that load data from dbf files created with FoxPro into one of our databases. We have not converted the packages and run them with DTS but we get the following error:
Error: 2013-08-26 11:05:27.36
Code: 0xC0209303
Source: BenchmarkLoad Connection manager "OLEDB NPIONE.Investment.middleTierSQL"
Description: The requested OLE DB provider SQLNCLI.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
[Code] .....
I searched for OLEDB and ODBC drivers for SQL 2012 64 bit but cannot find any that is newer than the 2005 that we have. What can I do short of changing the source files to overcome this issue?
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 RelatedHello
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.