MS SQL Server :: Can Run SSIS Job In BIDS But Not Scheduled From SQL Server

Package Runs Fine In BIDS, Fails When Scheduled

Dec 28, 2007

I have a 7 step SSIS package that manipulates some data on a DB2 database. The package executes perfectly in Business Intelligence Development Studio. I save the package to my SSIS store and then point my scheduled task to it and it fails after about 9 seconds everytime. I have an identical job that works with a different DB2 database that works without any problem. The only difference is the database it's pointing to.

The package is executing as the same user who created it, which has sysadmin to both the SSIS store and the SQL instance the package is executing on. When I saved the package I selected "Rely on server storage roles for access control" for the protection level.

This one is driving me crazy, can't figure it out. Any idea's?

View 7 Replies View Related

Scheduled SQL Server Agent SSIS Package Job Problem

May 9, 2006

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domainuser. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

View 17 Replies View Related

FTP Task Fails - SSIS Scheduled With SQL SERVER AGENT As JOB

Nov 21, 2006

Hi,
I have created a SSIS package and tested it in V.Stdio where it runs fine and uploads file on the remote server.

Now I have scheduled it to run as a job in SQL SERVER AGENT as JOB, but it fails when it comes to the upload task?

I am quite sure as it is something with FTP connection string or security, but can't figure it out.

I have simply added the IP/login/password in the connection manager for upload task.

Any help/ guidance would be great.

Thanks,
Ad

View 4 Replies View Related

BIDS Bug With SSIS Annotations

Jul 24, 2006

I would like to report what appears to me to be a bug. I found it while researching an answer for another thread. http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=554926&SiteID=17

Since that thread has not received any further replies I thought I would start a new one to see what answer we can get from MS as well as if anyone else would care to reproduce.

The bug is not a major one and the work around is easy, but it can be annoying.

The bug is this: if I open an SSIS package in BIDS (RTM or SP1) and the only change I make is adding or modifying an annotation and then click on Save and then close BIDS does not save the new annotation or changes made to existing annotations. If I change something other than an annotation then BIDS does save the annotations with the package.

A careful observer will notice that when opening a package and then modifying or adding an annotation that BIDS does not even register that the package has been changed. This is evident by the lack of the trailing asterisk in the title bar after the package name.

In summary if I go into a package for the sole purpose of adding and/or modifying annotations they will not get saved. Workaround: modify something else in the package and then save.

View 1 Replies View Related

Cannot Open SSIS Project In BIDS

Apr 30, 2007

Hi,
I have been working on an SSIS project for sometime now. The project files are located on a remote server. Suddenly I am not able to open the solution I get a lot of error messages and all the data flow taks are gone. I later found out that SSIS encrypts packages, so that other users will not be able to see them. Fine, but I have been using the same windows user account for months now. What could be the problem?
This is what I get when trying to open the solution:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

And the error list also contains messages saying "Could not load from xml".

Any pointers will be much appreciated.

TIA

View 9 Replies View Related

Error Editing SSIS Pkg In BIDS

Aug 22, 2007

We are using SQL Server 2005 Workgroup Edition SP2. I have a fixed-length flat file import spec created in SQL Server Mgmt Studio using the Import wizard. I then created an Integration Sevices project in BIDS and added the existing SQL Server package created with the wizard to the project. When I try to edit the package with the SSIS designer, it does not appear to handle the package properly. That is, only some of the fields are selected (and when I select all the available fields, still only some show up in the detail pane of the Input Columns tab), the data types are incorrect, and the starting location (it's a fixed length file, remember) for each field (LineageID?) is incorrect. My understanding is that, with Workgroup Edition, there are only two ways, other than say programmatically from a VB program, to run the package: (1) by creating a SQL Agent job or from a BIDS project. I have seen a Cumulative Update package (#2) for SP2 that mentions some problems in BIDS' handling of SSIS pkgs, but the symptoms are in no way similar to this. Can anyone tell me what is going on here?? Thanks.

Paul

View 3 Replies View Related

SSIS, BIDS And Temporary Files

Feb 1, 2007

Why are some SSIS files, generated by the Import/Export Data wizard put into the local users temp folder? Why are these not compiled with the package when the solution is built?

Is there some setting I am missing?

This architecture is kind of silly, as the server always needs access to the temp folder on the local machine to run.

How can I get these temp files packaged with the rest of the package and deployed to the server so the server can run independent of the machine I develop the package on?

Thanks,

Jeff

View 8 Replies View Related

BUG In BIDS When Developing SSIS Packages

Feb 1, 2007

There seems to be a BUG in BIDS when developing SSIS packages using the Import/Export Data wizard.

If you use the wizard to import a large number of tables, and then select all the tables, and then choose to delete exisiting data in each table, the PrologueSQL file does NOT get built correctly. Instead of having a

TRUNCATE tablename Go

for each table, it just has a bunch of "Go"s with nothing between them. In the step immediately prior, where you confirm what the wizard will do, it tells you, after each table, that it will delete any existing data...but it doesn't do this.

If, during the wizard, I select each individual table one at a time and tell it to delete existing data, then it will get built correctly, but not if I select them all at once...YET, if I do select the whole block, choose delete existing data, and then select any single table, it shows that table as being set up to delete existing rows.

This is very frustrating when trying to import large numbers of tables.

Am I missing something? or is this really a bug?

Thanks, Jeff

View 4 Replies View Related

SSIS - Package Performance Faster In BIDS?

Feb 22, 2008

I'm working on a fairly straight forward data transfer package and have found that the package runs dramatically faster when I run the package inside BIDS than with DTExec. When I run the package on the server using debug in BIDS, the job completes 1 million rows in around 6 minutes. When I run DTExec with the same package on the same server it is much slower and the package takes roughly 25 minutes to complete.

I know this sounds crazy and that it's supposed to be the other way around with DTExec running much faster, but I'm stumped as to what could be causing the issue. The machine this is running on is a two processor, dual core CPU with GB of RAM and I'm using terminal server to login and create the package with BIDS on SQL Server 2005 SP2.

The main feature of this package is a Foreach container that uses an ADO record set to loop over a set of values from a control table. There are a large number of iterations so the package loops frequently, but the data flow task is fairly simple and uses an OLEDB source and OLEDB destination to transfer data between two SQL Server 2005 databases.

The package works in either BIDS and DTExec, but I'm really puzzled why it would run so much faster inside BIDS?

Thanks in advance,
-Russ

View 7 Replies View Related

SSIS Package Will Not Run In BIDS Error: 0xC0202009

Dec 12, 2007

I have developed a simple SSIS Package that will export data from an AS400 iSeries server to a flat file. When I try to debug the package I receive this error. I have tried to change the security level of the package to EncryptAllWithPassword and specified a password. For some reason the password for the connection to the AS400 is not being retained. when I enter the password the following error disappears when I try to debug the package the error returns.
Does anyone know how to correct this? Thanks in advance for your help.

Things to also know:

I am using a Native OLE DBIBM AS400 OLE DB Provider w/user name and password (Allow saving password checked) Test Connection succeeded.

I am using a OLE DB Source to extract the data with Data Access Mode of Table or View. When I try to select a table I am prompted from the AS400 to enter password. Then I can see the tables.

I can select the columns I need and click OK to save.

Error 1 Validation error. Data Flow Task: OLE DB Source [39]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SERVERNAME.USERNAME" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SSIAS400DataExport.dtsx 0 0

View 7 Replies View Related

BIDS Keeps Hanging When Opening SSIS Packages

Jun 5, 2007

BIDS hangs when I open any SSIS packages. "Microsoft Visual Studio Is Busy" message displays in the system tray. It indicates that Microsoft Visual Studio is waiting for an internal operation to complete; however, it never seems to complete. I have recycled the server hoping to break it loose, however nothing seems to free it up. I have not had this situation before and I cannot figure out why it is having problems. BIDS shows it is "Validating Data Flow Task". Has anyone encountered this problem before?

Would it be a true statement that whenever you use BIDS to alter a SSIS package the source has to be available because verification and validation is always done on the source and destination? If the source were not available could that cause the hang in BIDS???

View 6 Replies View Related

SSIS Work Offline Switch In BIDS

Jun 5, 2007

If in BIDS I set "work offline" on to change an SSIS package, the connections for source and destination had already been build. Can I move this package to another server and execute it without going into BIDS and changing the switch back?

View 7 Replies View Related

SSIS Package Designing And Execution In BIDS Is Very Slow

Feb 9, 2007

Hi,

I have created a package that has

2 SQL Execute Task, One Loop container, 2 Data Flow tasks, 1 Foreach loop container, 1 ftp task. The data flow tasks has 1 oledb source, 1 flat file source, 1 row count transformation, 1 recordset destination and 1 oledb destination.

When I load the package into BIDS it takes 125 MB of memory and then everything is slow, the properties panel slides in slowly and exists slowly. The object is the packages are not painted properly. to make changes and run takes lot of time.

Am I doing anything wrong here? Why is it consuming so much of memory?

Thanks in advance for your help.

Regards,

$wapnil

View 2 Replies View Related

SSIS Package Executes Within BIDS But Not From A Stored Procedure

Feb 1, 2007

I have a SSIS package that contains a DTS 2000 package in it. The DTS 2000
package imports data into several tables from an ODBC data source. When I
execute the package through BIDS, no problems. Everything works great. I am
now trying to execute the SSIS package in my stored procedure & it gives me
the following error:
Error: 2007-01-30 11:54:24.06
Code: 0x00000000
Source: Populate IncrTables
Description: System.Runtime.InteropServices.COMException (0x80040427):
Execution was canceled by user.
at DTS.PackageClass.Execute()
at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
End Error

I did a search for this & found KB 904796. It had the exact error message
but I don't believe my packages uses 2000 metadata services. Just to be
safe, I reinstalled the backward compatibility features & the DTS 2000 tools
on the server. That still did not fix anything. I found another forum that
suggested loading the DTS 2000 package internally, which I did & it did not
fix anything. I am using a password for the protection level so that is not
causing my issue. Does anyone else have any suggestions as to what I might be
able to try?

SQL 2005 Dev Ed SP1 & post SP1 hotfixes installed
Win 2k3 server
Thanks!
John

View 3 Replies View Related

Problem Loading And Executing An SSIS Package With Too Many Lookups In BIDS

Nov 29, 2007

I have an SSIS package with around 25 lookups. Developing the package itself was slow. Now, everytime I try to load the package it takes forever and whenever I execute it I get an error.

Here are my questions:

1. Is there a way I can optimize the package?
2. Is it abnormal to have so many lookups? I am loading a dimension table with many fields and I need to look up on 25 tables to get the keys. I know one alternative is to use left joins in the source query and get the keys in the Source itself but we can have more visibility of what's happenning with Lookups. I would like to know other possibilities with lookups.

Thanks,
Srini

View 6 Replies View Related

Urgent -- BIDS Opening SSIS Packages In Text Mode

Apr 30, 2008

Hi All,

I am not able to open the package in BIDS. When I open the package I am seeing only the XML. Below I had given what I have done.

First I have installed Visual studio 2008 Professional and next I installed SQL Server 2005 with Integration services, database services, workflow components.

I am able to see the BIDS in Start --> All Programes --> Microsoft SQL Server 2005 --> "SQL Server Business Intelligence Development Studio"

Any help would be appreciated.

Thanks in Advance.

View 3 Replies View Related

Cannot Get Scheduled SSIS To Run

May 10, 2007

Hi,

I'm new to SSIS after spending a long time with DTS. I am having a difficult time getting a SSIS package to run as a scheduled job, even though the package works fine when run manually.

I read up on these problems and have completed all the neccesary instructions here:

http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

but the package still refuses to execute through SQL Agent. The only error message I get in the History Log is:

"The package execution failed. The step failed."

I am running the job as the new Proxy user that I set up but it will not execute when scheduled. Can anyone offer any advice please?

Thanks.

View 4 Replies View Related

Scheduled SSIS

Sep 14, 2006

Hello all,

I was very familiar in creating quite complex DTS packages in SQL2000. Now with 2007 that is all gone. I looked at the command line utility and it's UI counter part but these are just for executing. I keep getting references to the SSIS designer but I can't find where it is.

Can someone point me in the right direction?

Thanks,

Jesse

View 6 Replies View Related

Should We Allow SSIS To Be Scheduled Via SQL Agent

May 5, 2006

Hi,

Can we allow SSIS to be scheduled via SQL Agent. Is there any security concerns.

Thanks & Regards

Shri.DBA

View 4 Replies View Related

USING SSIS For Scheduled Import

Sep 27, 2007

Hi msdn forum members,

I need to import data from another server using Express edition every minute.
So I ran the import wizard, and I saved the packaged on the hard drive.
Then when I try to make a new job and new schedule for scheduling, I can't seem to find SQL server integration service option for the Type.

does anybody have any idea why I can't see SSIS there or had the same problem??
Or is there another way of importing data every minuite other than this?

BTW my server is 2005 standard edition.

Thanks in advance,

Mike Kim

View 10 Replies View Related

Using SSIS Instead Of Scheduled Tasks?

Jan 22, 2008

What are peoples opinions on using SSIS as a "central repository" and replacement for all scheduled tasks. Example, we have a bunch of servers which we have installed services which we have written. Currently we have scheduled tasks on each machine to stop and start the services. One of my collegues is using SSIS to run a system which runs tasks on multiple machines by remotely running programs on other machines via scheduled tasks and then collects the data and puts it into a database.

He's now pitching the idea that we remove the scheduled tasks on each machine and start and stop our services via SSIS so that it's centralized. In addition, we can also check for holidays in our database before starting services. Since it doesn't seem like SSIS was meant for this type of use, I'm weary of using the tool to do something it wasn't intended for.

Any opinions? I'm also worried that the learnnig curve for everyone is going to be too high.

View 11 Replies View Related

SSIS Scheduled Job Failed....

Nov 21, 2006

We run 4 nightly scheduled jobs on our sql 2005. All use the same domain account and were running fine until a few days. Then the SSIS job started failing when run via the scheduler. (It does run when I execute it manually). The sql 2005 History simply says the package failed and was "Executed as <Domain-User>", the same user that successfully ran the other 3 scheduled jobs.

SQLAgent.out shows SQLServer Error:: 2812 Could not find stored procedure: msdb.dbo.xp_sqlagent_notify.

Relevant?

Any ideas wherewhat to look for next?

TIA,

Barkingdog

View 1 Replies View Related

SSIS Error Only From Scheduled Job

Jul 12, 2007

Quick question - What are the different ways of executing SSIS Package besides Scheduled Job?Does SSIS support command line execution (DTSRUN command supported in 2005)?

The reason I asked was that Scheduled Job triggered SSIS always error out. But when I call the same SSIS in Visual Studio, it executes beautifully. I wonder if there's some reason behind the error when calling from Scheduled Job.

Appreciate any feedback,

-Lawrence

View 16 Replies View Related

Scheduled SSIS Failing All The Time - Help!

Jun 11, 2008

Hi all. We recently migrated to SQL Server '05 from 2000 and I'm trying to learn SSIS by crash course.

I just wrote an SSIS for one of our nightly processes in Studio 2005. It built with no errors, and the solution and resulting package are saved on the server housing SQL Server.

I ran the package in debug and it completed 100% with no errors. However, when I scheduled the package in SQL Server, it starts and fails.

Now, I'm very new to SQL '05 and I'm trying to figure out how to log the SSIS jobs, but is there any way to find out why the scheduled packages fail?

I'm desperately needing help on this, and any help would be greatly appreciated!

View 3 Replies View Related

SSIS Package Fails As Scheduled Job

Apr 17, 2007

I have a SSIS package that consists of 3 Script Tasks, two Data Flow Tasks. Each script tasks executes a FTP command. The first one renames the file, the second gets the renamed file and third resets it back to original name for testing.

When I run via BIDS or DTSExec all work fine. When I schedule it, only the first script runs. It never downloads the file.

Setup - SQL 2005 SP2, SSIS is running, 32 bit OS, SQL Agent Account is local admin. Package protection level is DontSaveSensitive. PrecompileScriptIntoBinaryCode = True

What am I missing??

Thanks

Mike Allen

View 7 Replies View Related

SSIS Package Failing Via Scheduled

Apr 8, 2008

I have 3 SSIS packages I had in a project and exported and scheduled via an SQL Agent job. 2 are working great and one is failing. I have noticed on this one its also having issues in BIDS. It runs sucessfully once then next day when I go in it fails. If I just go into the Data flow and open up the SQL Destimation and then hit ok and try re-running it works. It is like there is something that it doesn't remember.

Same thing happens in SQL Agent if before I promote I go into SQl destination editor hit ok save then

create my build run it via agent job it works 1st time but fails on the following time. I don't see anything different between this SSIS and the others but there must be something.

All of these are simple copy data from as/400 to SQL server with some data conversions.

Here is the error msg I am receiving.

Message
Executed as user: PERFORMANCEstacyadmin. ....3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:55:11 AM Error: 2008-04-08 05:56:00.88 Code: 0xC0202009 Source: Load WeeklySalesHistory Current Week WeeklySalesHistory [184] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". End Error Error: 2... The package execution fa... The step failed.

What am I missing.

Stacy

View 3 Replies View Related

SSIS Package Runs Fine Except As A Scheduled Job

Mar 14, 2006

I have an SSIS package created from a SQL 2000 DTS using the Migration Wizard. The package imports data from a MySQL database to a SQL 2005 64-bit database running on 64-bit windows server 2003. The package runs fine when executed from SQL Server Management Studio but when I schedule it as a job it fails with:

Executed as user: [the domain admin account]. The package execution failed. The step failed.

I've tried a lot of different ways to make this work including creating a new SSIS package. Again, the package ran fine except when it was scheduled as a job.

Any ideas would be appreciated.

thanks!

View 20 Replies View Related

SSIS: Oracle Import Works, But Not When Scheduled

Dec 26, 2006

Hi,

I am running SQL Server 2005 and have built a simple SSIS package to import data from an oracle database to my SQL Server 2005 database. When I run it in SSIS, it works and it imports just fine. When I schedule it, it gives me problems. Help!

It might be a problem with Oracle Provider client I installed. Is there a client version I can download and install? the one I downloaded from oracle doesn't work. I bet i did something wrong though.

Here is my version:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Thanks,

Ryan

View 5 Replies View Related

SSIS Package With An FTP Task Fails When Scheduled

May 7, 2006

I have a SSIS package with an FTP task to download a Excel file and populate a table using an excel connection mnager and a SQL Server Destination and it always fails with the following error when scheduled:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (FTP-DM-CRN_ALLOCATION_COMMENTS).

Executed as user: WEB-INTSQLSYSTEM. The package execution failed. The step failed.

The box on which SQL Server is installed is on a workgroup on the domain and the SQL Server is started up by the Local System user on the box.

I am thinking this has to do with windows security based on all the information that I have read on these kind of error messages. Any input on resolving this will be much appreciated.

Thanks,

MShah

View 1 Replies View Related

Variable Within SSIS Inconsistently Populated When Scheduled

Aug 22, 2006

I have a SSIS package with multiple objects executing ODBC SQL to Teradata. All of the SQL uses the same variable (today) to susbtitute today's date into the native teradata SQL.

Today is populated using the following expression:

"'" + (DT_WSTR, 4) DATEPART( "yyyy", GETDATE() ) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "mm", GETDATE() ) ,2) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "dd", GETDATE() ) ,2) + "'"

The problem is that some of the code uses the correct date, but others appear to use the date that the SSIS package is added to the scheduler. It is not always the same SQL statements which deriver the incorrect date. The code below is four of the SQL statements generated at the same time using this variable - two show the correct date (2006-08-22) and two the date the package was added to the schedule (2006-08-18):
SELECT MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) , COUNT ( * ) FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeDate = (dAtE'2006-08-22') ) GROUP BY MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode ORDER BY TradeTypeInd , MarketSegmentCode

SELECT ParticipantCode , CASE WHEN ParticipantCode = ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END AS Counterparty , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeTypeInd = 'O' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY ParticipantCode , ParticipantCodeBuyer , ParticipantCodeSeller , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode

SELECT ParticipantCode , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-22') ) GROUP BY ParticipantCode , CurrencyCode , MarketSegmentCode

SELECT ( CASE ParticipantCode WHEN ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END ) AS Cparty , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY Cparty , CurrencyCode , MarketSegmentCode

Has anybody experienced similar problems with variables in SSIS packages and the scheduler?

Thanks

View 3 Replies View Related

SSIS Package Scheduled In SQL Agent Fails On Occasion

May 13, 2008

I have one SSIS package that fails on occasion. And I can then run this in the job by itself after it fails and it runs fine. Any ideas? Any ideas on what is causing this. It is not every day but about once a week lately but it just happened again today to this is 2nd time in 4 days this has happened. It is always on this same part in the SSIS that it fails when it does fail. 4 times in last 2 weeks.

This step it is on here has a OLE DB - DB2 source and SQL server as destination. It does a couple data conversion, derived columns and then just copies the data from the db2 table to the sql server table.

Message
Executed as user: PERFORMANCEstacyadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:06:12 AM Error: 2008-05-13 06:09:22.84 Code: 0xC0202071 Source: Copy SalesTender Retail TmpSalesTenderRetail [97] Description: Unable to prepare the SSIS bulk insert for data insertion. End Error Error: 2008-05-13 06:09:23.42 Code: 0xC004701A Source: Copy SalesTender Retail DTS.Pipeline Description: component "TmpSalesTenderRetail" (97) failed the pre-execute phase and returned error code 0xC0202071. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:06:12 AM Finished: 6:09:23 AM Elapsed: 191.157 seconds. The package execution failed. The step failed.

Stacy

View 2 Replies View Related

SSIS Writing To MSMQ Fails Only When Scheduled By Agent Job

Feb 12, 2008

Hi All,

I'm a bit lost on this...

I have an SSIS package which writes to a configured public MSMQ. When run directly through Visual Studio, file system or msdb it works fine (I guess these use me as the user).
However, when I schedule it through an SQL Agent Job it fails with the following:

Failed to write message ... to the queue.

This seems like a permissions thing to me, but I've tried giving full control to everyone in the MSMQ permissions, also explicitly giving full control to the user that the agent job executes as. Neither made a difference.

Any help would be greatly appreciated.
Cheers,
Tony Vaughan.

View 2 Replies View Related