.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS job failing successfully

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server


I have a series of SSIS packages I use to copy data from a MYSQL database to SQLServer.  The packages are set up to execute via a scheduled job.  I each case, the data is copied, but the step that executes the package fails.


Date  7/31/2008 12:54:42 PM
Log  Job History (Test t_accounts)

Step ID  1
Server  xxxxxx

Job Name  Test t_accounts
Step Name  exec
Duration  00:00:02
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Executed as user: xxxxxxx. 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:  12:54:42 PM  DTExec: The package execution returned DTSER_SUCCESS (0). 
Started:  12:54:42 PM 
Finished: 12:54:43 PM 
Elapsed:  0.969 seconds. 
The return value was unknown.  The process exit code was -532459699.  The step failed.


I've turned on logging for every event in the package, but don't see anything that looks like a failure.



Source = MYSQL 5.0 using MYSql Connector /.Net 5.1.6

Destination = MSSQL 200

View Complete Post

More Related Resource Links

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

SQL Agent Failing when trying to run my SSIS Package

My SQL Agent was working fine.  Now when I try to run it, it failes when trying to run my SSIS package.  I looked in the logs, and job history, no luck in finding any critical errors. My SSIS package runs fine through VS 2005 in debug mode. Here are the only errors: 01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 101/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.139901/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860

SSIS Package failing

Hi there, There is SQL agent job that runs a SSIS package once a day, but for the last few days its been failing. As far as I can tell nothing has changed. The Database name is 'Greece' The error message is:   Message Executed as user: S2\ServiceAccount. ...9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:14:18  Error: 2010-09-13 10:14:49.36     Code: 0xC0202009     Source: NL LCDB Import Products Connection manager "Greece.eu.LB.com,10563.msdb.BNLSQL"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'BNLSQL'.".  End Error  Error: 2010-09-13 10:14:49.36     Code: 0xC020801C     Source: NL LCDB Import Products Log provider "NL LCDB Import Products"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Greece.eu.LB.com,10563.msdb.BNLSQL" failed with error code 0xC0202009.  There may be error messages

Data Flow Task failing to drag-and-drop onto SSIS Control Flow

This morning, I started having problems with Visual Studio 2005 on XP when I tried to create an SSIS project; I was getting an error message about the failure to create an SSIS runtime object, with the reference "Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type". Digging here and elsewhere on the Net, I used regsvr32 to re-register msxml3.dll, msxml6.dll, and dts.dll. This allowed me to create a new SSIS project, but I was unable to add a data flow task to the Control Flow pane -- when I dragged the control onto the pane, nothing happened, and when opened the Data Flow pane and clicked on the 'No data flow task has been defined. Click here to create a data flow task" link, I got a 'Failed to create the task. The designer could not be initialized." error. Searching here and on the Net, I uninstalled and reinstalled the SQL Server 2005 workstation tools.

After doing this, I am still unable to create a data flow task by dragging the task out of the toolbox; the pointer assumes the right form for the drag-and-drop, as it does for other tasks, but when I release the mouse button, nothing happens. However, when I go to the Data Flow pane and click on the 'click here to create a new data flow task', it creates the task, and I am able to use copy-and-paste to make copies of the data flow task

Uniqueidentifier field failing during conversion in SSIS



I am trying to convert a table from one schema to another and the destination table (SQL CE) contains a uniqueidentifier field. This field can accept nulls and is optional. I am not supplying any values for it.

But when I run the package it throws an error.

SQL Server Compact Destination [107]: Data conversion failed. [ OLE DB status value (if known) = 2 ]

If I map a dummy value for this column then the error doesnt occur. Any help would be appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

When dynamically creating an excel file, can SSIS create xlsx files successfully? I can only get SSI


Problem: An Excel file (XLSX) dynamically created by a SQL Task cannot be opened via Excel. The task succeeds, but when you try to open the workbook in Excel you get the fillowing error message: Excel cannot open the file 'myfile.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

1. If the Excel file (xlsx) is created manually in Excel, then the SQL Task has no problem creating table (new worksheets) in the xlsx (so the "CREATE TABLE" statement works fine)
2. If I adjust the connection and task to work with Excel 97-2003 (xls) format then the approach works perfectly
3. The execution results show success of the SQL Task, but there are two warning messages. These same warnings appear when the SQL Task creates a new table (worksheet) successfully in a workbook (xlsx) which was created manually in Excel (and is not corrupt). Messages as follows:
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

SSIS Details:
1. ConnectionString: "Data Sour

SSIS SQL jobs failing urgent help please!!!


Here is the error I am getting when running the SQL jobs, this sql job basically runs the SSIS package. Not sure where to look at to fix this error:

Description: The log provider type "DTS.LogProviderTextFile.2" specified for log provider "SSIS log provider for Text files" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.



Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

how to know when a user finish his file download successfully ?


hi all

i am doing a page to sell files online

 the paying method is by a card or bank account and the customer is not registered to the site so i want to make sure that the customer has downloaded the file successfully after that the system completes the payment operation

so if there is any way to know do that tell me please

i use c# thanx all



Web Deployment - Setup failing - aspnetca.exe


I have created websetup in windows 7 environment, asp.net 3.5, IIS 7.

Client System is windows 7 and IIS 7

Our client configuration is Windows 7, IIS7 dotNet Framework 3.5.
Following error raising below...
Faulting application name: aspnetca.exe, version: 7.5.7600.16385, time stamp: 0x4a5bcd3c
Faulting module name: aspnetca.exe, version: 7.5.7600.16385, time stamp: 0x4a5bcd3c
Exception code: 0xc0000005
Fault offset: 0x0001d912
Faulting process id: 0xe80
Faulting application start time: 0x01cb3604624975cd
Faulting application path: C:\Windows\system32\inetsrv\aspnetca.exe
Faulting module path: C:\Windows\system32\inetsrv\aspnetca.exe
Report Id: a05d368d-a1f7-11df-8336-4487fc612414
PFA Here I"m attaching custom action script. 

ASMX ws: An unexpected error occurred on a receive; failing intermittently



we I have asmx web service, this seems to be working fine for 25 client machines but one site keeps failing. The client gets a small dataset and tries to send it across to server to process. The dataset saved in XML file only makes 450 - 550kB. The web service is contacted twice in a short time every half an hour. The other smaller dataset (50kB) goes across everytime without any troubles. This site is not trying to send the biggest dataset, other site are sending much bigger datasets without problem. The catch is this failing site keeps retrying for 20 times (10 hours) in average and then eventually goes across. I don't think the service webmethod is erroring out as that is all wrapped up in the try and catch and the exception is not getting thrown, also every other try for the exact same webmethod used by any other client site seems to be working fine.

System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Ne

using SharePoint List for SSIS package

I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

Migrate DB + files outside DB into SharePoint using SSIS and scripts


Hello, just wanted to ask a question about SSIS and SharePoint, any input will be greatly appreciated.... did search the forum but could not find anything....

So basically we have a SQL Server DB with the metadata ONLY, WITHOUT the related files(doc/ppt/pdf..etc) which should've been in the DB and they are on a separate web server inside folders. Each row in the DB has a key (column) which matches the related folder's name, so for row with ID #112, there's a folder named ID112 on the web server with the relevant files inside. There could be more than 8+ files for one folder.  

Now, I think it is possible but just wanted to double check, would it be possible to use SSIS and some kind of a script to get the metadata inside the SQL DB AND the related files inside those folders, combine & relate them and upload them into SharePoint as a custom list with file attachments? would they display the attachment normally (like the paperclip icon in a sharepoint list...etc) I wonder if anyone has tried this...

Any guidance, thoughts, suggestions for other solutions, inputs will be great!!! Thank you!!



Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

How to get some Return value from SSIS

Hello, My SSIS Package executed from a Job and the Job is called from a SP. Is it possible to get Package Result (Pass or Fail) and If Fail Can I retrive the value of one or more varibale from SP? Thanks, Prabhat

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat


Hello, I am using SSIS to import data from XML to OLEDB Destination. (using XML Source and generated schema from this xml) XML that I will get, sometimes will have more nodes and sometimes less. How do I use xsd schema that I generated from one xml file if it can be different? Thank you.V. A.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend