Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Jan 11, 2008

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

View 2 Replies


ADVERTISEMENT

Error Running SSIS Pkg From Dtexec Utility

Feb 21, 2008

Hi All,

I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.

COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'

ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds

PLease let me know solution to creating batch job for executing this pkg with input param.

View 4 Replies View Related

Running SSIS-packages In Stored Procedure Using Dtexec

Apr 2, 2008



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?

View 5 Replies View Related

SQL Agent Running SSIS Packages

Apr 18, 2006



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.

View 1 Replies View Related

SQL Agent Job Fails Running SSIS Packages

Oct 16, 2006

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

View 2 Replies View Related

Errors While Running Ssis Packages From The Command Prompt.

Dec 23, 2005

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.

 

View 7 Replies View Related

Behavior Of Ssis Packages Running Under SSMS Manually Vs. Job Agent Automatically

Sep 5, 2007



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

View 2 Replies View Related

How To Check The Number Of Rows Transfered In The Dataflow Task By Using Dtexec Utility

Jun 6, 2007

We run the SSIS through tidal scheduling agent using the dtexec utility. We want to see the number of rows transfered while running or after it has run our package. We require answers for the following:

1) How to see the number of rows transfered while running the package using dtexec utility

2) what parameter should be used in dtexec command line to get the number of rows transfered in the Log file after execution.



Thanks

Subhash Subramanyam

View 1 Replies View Related

Running Packages In Parallel And Isolated (like A Sepearte Instance)

Feb 2, 2007

I'm a bit confused about how ssis handles concurrent package runs

let's say I'm running this package and I 've got 3 variables set in it
VARA
VARB
VARC
and by default they are all set to 0
if I run

dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARA].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARB].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARC].Value;1

I'm expecting to run 3 isloated version of the package with in
first version
VARA=1
VARB=0
VARC=0
second version
VARA=0
VARB=1
VARC=0
third version
VARA=0
VARB=0
VARC=1
but it doesn't seem like doing that the maxconcurrent variable is set to 40 to be on the safe side.

when I run I get

first version
VARA=1
VARB=0
VARC=0
second version
VARA=1
VARB=1
VARC=0
third version
VARA=0
VARB=1
VARC=1

any ideas?
Thanks

View 2 Replies View Related

Lookup Concurrency Issue In Packages Running Simultaneously In Parallel

Mar 27, 2007

I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :

"
The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

"



The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.



My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.



Has anyone experienced this? Can someone shed some light on this?



Thanks a lot



-Alex

View 15 Replies View Related

Error Executing SSIS Package With DTEXEC Utility

Feb 29, 2008

Hi All,

I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.

COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'

ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds

PLease let me know solution to creating batch job for executing this pkg with input param.

View 8 Replies View Related

Parallel Development Of SSIS Packages

Jun 29, 2007

I have seen a number of posts regarding parallel development of SSIS packages and need some further information.



So far we have been developing SSIS packages along a single development stream and therefore have managed to avoid parallel development of our packages.



However, due to business pressures we will soon have multiple project streams running in parallel, and therefore multiple code branches, as part of that we will definitely need to redevelop the same SSIS packages in parallel. Judging from your post above and some testing we have done this is going to be a nightmare as we cannot merge the code. We can put in place processes to try and mitigate this but there are bound to be issues along the way.



Do you know whether this problem is going to be fixed? We are now using Team Foundation Server but presumably the merge algorythm used is same/similar to that of VSS and therefore very flaky?



However, not only are we having problems with the merging of the XML files, but we also use script tasks within the packages which are precompiled, as the DTSX files contain the binary objects associated with the script source code, if two developers change the same script task in isolated branches the binary is not recompiled as the merge software does not recognise this object.



Do you know whether these issues have been identified and are going to be fixed to be in line with the rest of Microsoft Configuration Managment principles of parallel development?



Many thanks.

View 7 Replies View Related

SSIS Parallel Processing Of Packages

Oct 7, 2006

Hi,

I am facing some problem's while using the FOR loop container to execute 7-10 packages in parallel.

The main package has 7 FOR loop containers say F1-F7.

Each FOR loop container has 2 task's

T1==> exec child package C1

T2==> exec delay task Delay1.

The idea is to run child packages c1-c7 in parallel ...delay for some time and then run again since there are in the FOR loop container.

I am facing someproblems.

1. The execution of tasks T1-T7 is not guranteed. This means SSIS picks up any 6 tasks of T1-T7 randomly to start with. 6 is the max it processes whereas i have more than that. Can i change this setting???

2. Its not guranteed that if say Task t1 of FOR loop F1 is executed the subsequent task for Delay within tat For loop would be executed next. Typically wat happens is it starts with T1-T6 (T7 onhold) and then exec the delay for T1-T5 and passes control to T7 without going into the delay for T6.This is not the intended execution.

What i want is exec T1-t7 ..delay for the next exec and start again.

How do i do this.









View 5 Replies View Related

How To Force A SQL Server Job To Always Succeed Even When SSIS Packages Have Errors

Jan 11, 2008


I have added an email task to the ON Error Event of my SSIS package, so that I will always know when there are errors.
However I would like the SQL Server job executing the package to succeed even if the package fails.
What setting do I change in the SSIS packageto achieve this? MaximumErrorCount?




View 1 Replies View Related

EncryptSensitiveWithPassword Protection Option Cannot Be Used With DTexec Execution Of SSIS Packages

Nov 6, 2006

Our SSIS packages use the Web Service Task to call services to send email and write package failure data to a department wide database. These Web Service Calls are failing with HTTP 401 errors. It was caused by the passwords for the HTTP connections not being saved when the SSIS pkgs were saved to .dtsx files. I have tried saving the package with a password and the EncryptSensitiveWithPassword protection option. This password can be supplied when the package is called from another package or a package is executed in Visual Studio but cannot be supplied to DTexec to execute the package in a job. DTexec does have a /Password parameter but it is rejected if the package is loaded from a .dtsx file.
This appears to be a bug in DTexec. It only accepts the /Password parameter when the package is loaded with the /SQL option. Specifying /Password and /File is not supported.

Is this a known bug? Are there any workarounds?
Has anyone successfully called a Web Service from SSIS executed via DTexec?

View 1 Replies View Related

Running Packages Through Sql Agent (best Practices)

Apr 5, 2007

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

View 10 Replies View Related

Errors With SQL Cluster Running .DTSX Packages

Sep 25, 2007

I'm experiencing some frustration with my active/passive SQL cluster not running my .DTSX packages. I am hoping someone can shed some light on what I need to do.


I've created some .DTSX packages with the SQL Server Business Intel Dev Studio. I initially built & tested these packages in a non-SQL cluster environment without any problems. I'm now re-creating them to work on our SQL cluster. If I run the package through the Dev Studio it works great.

The packages basically grab .txt files from one of the shared drives (which is a resource of the sql cluster group) and imports the data into the one of the databases. The database does not have any special settings (right-click -> new database... -> enter name -> click ok).


I've setup a SQL Server Agent Job with 1 step with the following properties.
Step name: I Offices
Type: SQL Server Integration Services Package
Run as: SQL Agent Service Account
Package source: File system
Package: G:ImportRAGFLOffices.dtsx


When I run the SQL Server Agent Job through MSSQL Server Management Studio (right-click -> start job) I get an error on "Execute job 'RAGFL TestJob'".


These are the 2 messages that show up when I view the history of the SQL Server Agent job.

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 0
Server BADBOYS
Job Name RAGFL TestJob
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 sa. The last step to run was step 1 (I Offices).
***********************************************************************************************

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 1
Server BADBOYS
Job Name RAGFL TestJob
Step Name I Offices
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: THEISLANDAdministrator. The package execution failed. The step failed.
***********************************************************************************************

THEISLAND is our internal domain.

View 2 Replies View Related

Running SSIS Packages In MS Server 2005

Jan 31, 2008

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...

View 1 Replies View Related

Unable To Create A SSIS Deployment Utility For A Solution Which Are Having 2 Packages

Dec 28, 2007

Hi All,

I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.

For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.

Am I missing something here?

Thanks.
Venkat.

View 8 Replies View Related

Problems Running SSIS Job From SQL Server Agent

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 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

View 16 Replies View Related

Cloud Of Confusion: Running SSIS Packages On 64 Bit SQL Server

Sep 25, 2007

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

View 5 Replies View Related

Problem Running SSIS Package In SQL Server Agent

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

Data Warehousing :: Running SSIS Package As Server Agent

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

Running A Ssis Package In Sql Server Agent With Execute Process Task

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

Problem With Saving Large SSIS Packages

Jan 25, 2008



When I work with large dtsx files I have problem with saving them in Business Inteligence Studio.
This problems arise when dtsx files is has size more than 7-8 MB.
Any hint about it?

View 5 Replies View Related

Cannot Run SSIS Packages As A SQL Agent Job

Feb 22, 2008

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 'CFC0SQLA'.". 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.

View 4 Replies View Related

Errors When Trying To Build SSIS Packages

Oct 18, 2007

II am using file system for the SSIS Packages. I have several packages in a project. Most of these packages use configuration files. Infact there are several packages which use the same configuration files. Now when I try to run the build utility, it errors out saying that the name.dtsconfig file already exists. Seems like this is a bug. I would really like to use this utility.I know if the config file is not used in any other pacakge probably the build will not fail. But it is not good practice to have one config file for each package, it is redundancy, does not make sense to that. Is there any way I can use the build and deployment utility without this error.

Please advice.

Thank you.

View 4 Replies View Related

DTExec Utility Error

May 8, 2008

I HAVE an SSIS Pkg that gets data from lotusnotes into SQLS ERVER 2005. This SSIS Pkg works just fine, when I run it from manually from Visual studio environment. However, when I run this as a dtsexec from command line it fails with the foll error" The product level is insufficient for component "DataReader Source(1). The dtexec command I use is :

dtexec /f "c:otesPack.dtsx"

View 3 Replies View Related

SSIS Packages Failing To Run. In SQL Agent

Apr 28, 2008


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.

View 1 Replies View Related

Dtexec Utility Command Line

Apr 23, 2008

Hi,
I need to run a package wich is located on the file system of a PC. The problem is that the PC from where I'm running a bat file to call the package from the other PC where the package is, doesn't have SSIS.
So I was wondering if this will work. I believe that I will have to copy the DTExec.exe file to the PC where my bat file calls to the dtsx in the other PC. I am right?
My run.bat will have this line: \PCshared_folderpackage.dtsx.
Will this work?
Thanks

View 6 Replies View Related

Package Execution With SQL Server Agent Or DTEXEC

Apr 4, 2006



Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).



Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?



Thanks for your help !

View 3 Replies View Related

SQL Agent Jobs Hung - SSIS Packages

Jun 26, 2007



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!

View 6 Replies View Related

Using Proxy Accounts To Run SSIS Packages By SQL Agent

Feb 13, 2008



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

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved