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

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

Bulk Insert Task Failure

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


I have an SSIS package with a Bulk Insert Task that was working last night. But when I tried running the same package this morning it failed.

This is the error that I get:

"[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (100) was exceeded.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 102, column 275..."

Does anyone know why what may have caused  it?



View Complete Post

More Related Resource Links

Bulk Insert Task Failure



I am trying to load a flat file with the Bulk Insert Task. However, I keep getting this error. I understand that it maybe a permission/security issue, so I made sure that I have Bulkadmin and sysadmin rights.

I even tried to place the file on my local drive (C), but I keep getting the same error:

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\Deployment\AtmDialog20100713.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).".

Any ideas on how to fix this?



Get error message from bulk insert task.

Hello, I've created a dtsx package that bulk inserts some data from a csv file into a sql table (using the BULK INSERT TASK). Because this package is scheduled to run automatically, I also added a EXECUTE SQL TASK that will insert a row in a 'log table'. It is linked to the BULK INSERT task with a 'Succes constraint (green arrow)', so I know about all the successes. But I am more interested in the failures, so I also added another EXECUTE SQL TASK and linked it to the BULK INSERT task with the 'failure constraint (red arrow)'. Question: How can I get the error message from the BULK INSERT TASK (like 'file does not exist' or 'error converting .....') and store it in a field in the 'log table'? Thanks in advance, Peter

Bulk Insert Task


I m using flat file connection manager my source is csv file

which contains TOP 5 rows from AdventureWorks.person.contact

select top 5  * from AdventureWorks.Person.Contact - my source as csv


"AdditionalContactInfo"- column  is XML data type in Destination.


When I use Bulk Insert Task I am getting error.

[Bulk Insert Task] Error: An error occurred with the following error message: "XML parsing: line 1, character 33, whitespace expected".

I have tried changing code page to ANSI 1252 and SSIS datatype for AdditionalContactInfo - DT_WSTR.

Nothing worked. My motive here is to learn, nothing business-logic behind this question.



Setting up a variable for a Bulk Insert Task in a For Each Loop


Is it possible to set up variables in a Foreach Loop for a Bulk Insert Task?

The Bulk Insert Task works fine without using a variable. But I have many file so I need to set the variable inside the Foreach Loop to read files one by one.

Everything I've tried keeps failing. I set my Destination and Source connections and then in the Bulk Insert Task Editor set up the expression with the variable that I have decalared (str_SourceFullName), and then set the bulk insert task delayValidation property to true and it keeps failing...I have tried almost changing every setting but it does not pick up the value of the variable.

I even tried to using an ActiveX Script Task by using the following script to pick up the variable, but the ActiveX Script errors out as well. Here is the ActiveX code:

Dim connmgr As ConnectionManager = Dts.Connections("Dialog2010.txt")

connmgr.ConnectionString = Dts.Variables("User::str_SourceFileName").Value.ToString()


 Has anyone had this problem?




Can I bulk insert to specific columns when bulk inserting a text file or do I have to bulk insert into all columns of a table? The extra column in the table is an identity integer. So I had to create a view without the identity integer to make it work. Maybe there is a more direct way?? If so what would be the syntax? I am a beginner to intermediate SQL guy.   strSQL = "BULK " & _        "INSERT CustomersUpload " & _        " FROM 'C:\MydataFile.txt' " & _        " WITH " & _        " ( " & _        " FIELDTERMINATOR = '|', " & _        " ROWTERMINATOR = '\n' " & _        " )" Thanks

script task to insert blank row into excel sheet

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor

Errors during bulk insert

Hi, While writing a stored procedure in SQL 2008, for a bulk insert, I am getting several errors. Part of the SP is written below for your reference. Need to know where am I going wrong. Any help will be thankfully acknowledged.-Learn24x7----------CREATE PROCEDURE Sp_BulkInsert(@Directory nvarchar(256), @TblName nvarchar(50), @Collation nvarchar(50), @FieldSeparator nvarchar(50),    @RowTerminator nvarchar(50))ASBEGIN    SET NOCOUNT ON;                BULK INSERT [@TblName]    FROM ''+@Directory+'' --Error1: see below    WITH --Error2: see below     (     FIRSTROW = 2,     CODEPAGE = (SELECT CAST(@Collation AS INT)), --Error3: see below     FIELDTERMINATOR = ''+@FieldSeparator+'',     ROWTERMINATOR = ''+@RowTerminator+''     )  ENDGO----------FROM ''+@Directory+'' --Error1: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.WITH --Error2: Incorrect syntax near the keyword 'with'. If this statement is a commo

How to go to next iteration of ForEach Loop container on task failure

I'm using a ForEach Loop container to iterate through a set of files and import/validate the data.  Within each loop, the first thing that happens is that I fill a variable from a table and then use a script task to make sure that the rest of the package should be run for this particular file.  If it should continue, I set Dts.TaskResult = Dts.Results.Success, and if it shouldn't, I set it to Dts.Results.Failure.  My question is, how do I get it to exit the current iteration of the loop and continue to the next iteration?  The way it is now, the package just stops completely.  I set the FailPackageOnFailure property to False for the script task, but that doesn't make a difference.  I also tried pointing a failure precedence contstraint bacl to the loop container, but that's not allowed.  How can I make it just continue in the loop?   Thanks.

Unable to prepare the SSIS bulk insert for data insertion

Hi,   I am using SQL Server Destinations in my data flow tasks. I'm running this package in the server until i encountered this error:   OnError,,,LOAD AND UPDATE Dimension Tables,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion. OnError,,,Load Dimensions,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion. OnError,,,Discount Reason, ISIS Condition, ISIS Defect, ISIS Repair, ISIS Section, ISIS Symptom, Job Status, Parts, Purchase SubOrder Type, Service Contract, Service Reason, Service Type, TechServiceGrp, WarrantyType, Branch, Wastage Reason,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Dim_T_ISISDefect" (56280) failed with error code 0xC0202071. 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.  There may be error messages posted before this with more information about the failure. What could be the reason for this? I don't usually have an error.   cherriesh

SQL ServerDestination error - Unable to prepare the SSIS bulk insert for data insertion.

Having searched the forum, this one clearly has form... However beyond assisting those who have fallen at the first hurdle (i.e. forgetting/not knowing that they cannot execute the package remotely to the instance of SQL Server into which they are inserting), the issues raised by others have not been addressed. Thus I am bringing nothing new to the table here - just providing an executive summary of problems which others have run into, written about, but not received answers for.   First the complete error: Description: Unable to prepare the SSIS bulk insert for data insertion.  End Error  Error: 2008-01-15 04:55:27.58     Code: 0xC004701A     Source: <xxx> DTS.Pipeline     Description: component "<xxx> failed the pre-execute phase and returned error code 0xC0202071.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:53:34 AM  Finished: 5:00:00 AM  Elapsed:  385.384 seconds.  The package execution failed.  The step failed.   Important points It mostly works - It produces no error more than 9 times out of 10. It fails on random dataflows - My package has several dataflows, (mostly) executing concurrently. Where the error occurs it does not do so on the same dataflow each time: on one run it'll fail on dataflow A whilst B,C,D and E succeed, then A-E will all succeed (and continue doing so for the next ten runs thereafter), and

Insert error description in sysjobhistory from ssis script task

hi. in ssis i have script task that, in some situations, raise error (Dts.TaskResult = ScriptResults.Failure). But in sysjobhistory table there is only shor description what happend (Description: The script returned a failure result.). Is there any way that i can set some system variable to describe what is error so that description ens up in sysjobhistory table? in the end, i want to look in job activity monitor and see that job is "red" and see the description that couses this failure.

How to do a parameterized bulk insert into SQL Server 2008 R2 from C# (.NET 4.0)

Hi, I'm trying to execute a BULK INSERT with a parameterized SqlCommand from a C# .NET 4.0 project. This is the code: private System.Data.SqlClient.SqlCommand sqlCmd2; private string sqlCmd2String = "BULK INSERT ExchangeTrackingLogs.dbo.ParsedLogs FROM @name WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)"; private System.Data.SqlClient.SqlParameter[] sqlCmd2Params = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar) }; (...) sqlCmd2Params[0].Value = source; sqlCmd2.Parameters.Clear(); sqlCmd2.Parameters.AddRange(sqlCmd2Params); sqlCmd2.ExecuteNonQuery(); where source is a string containing the full path to a file, i.e. a value "C:\\TEMP\\MSGTRK20100701-1.LOG_EXHUB01". When I execute this code, I get an Exception with Message "Incorrect syntax near '@name'." In the database trace I see an RPC:Completed event with TextData equal to exec sp_executesql N'BULK INSERT ExchangeTrackingLogs.dbo.ParsedLogs FROM @name WITH (FIELDTERMINATOR='','', ROWTERMINATOR='' '', FIRSTROW=2)',N'@name varchar(36)',@name='C:\TEMP\MSGTRK20100701-1.LOG_EXHUB01' I tried using verbatim string literals, but doesn't seem to help. Any idea?

Bulk Insert Problem

I am trying to perform a bulk insert on pipe delimited file (approx 25 columns,5000+ rows of data).  Normally this is no problem however this file has a header and trailer record with no delimiters (1 column).  I could use a utility like grep to remove the header/trailer record but I would like to capture the entire file inside sql server. I can bulk insert the entire file into a single column (wide) that would include the delimiters.  My problem from there is to process the single column data into the appropriate rows /columns.  I am looking for a set solution as opposed to a cursor type solution. HTML?  Seems slow.  Cross Apply?   Changing the file format is not a viable solution. I’m likely to have more than a few of these oddball formats with a variable number of columns.  Any ideas?   I thank you for your time on this puzzle.

Bulk insert CSV files

Hello, I am trying to Bulk Insert a CSV file, and I can't seem to find the row terminator. When I use \m I get an error message that an unexpected EOF has occurred, which usually means a carriage return at the end of the file. I removed the carriage return from the file, but I am still getting the same error message. I have tried the following row delimiters... \r,\n,{CR},{CR}{LF}. These work as the data is indeed bulk loaded into the table, however it dumps the majority of the data in the last column, so 7 or 8 columns are stuffed into 1. Has anyone had this issue before? Any help would be appreciated. DaveDave SQL Developer

Permission Denied to SA on Bulk insert

Dear All,I am experiencing strange problem (SQL 2005, build 9.0.1399), receiving following error:Msg 4834, Level 16, State 4, Line 1You do not have permission to use the bulk load statement.I am running the stored procedure SP1.  This stored procedure calls another one, SP2.  Stored procedure SP2 contains a dynamic SQL statement to insert data into the temporary table, created by SP1 and given to SP2 via parameter name.Something like:SET @szSQL = 'BULK INSERT ' + @TName + ' FROM ' + '''' + @FilePath + '''' +  ' WITH ( ' +   ' DATAFILETYPE = ''char'', ' +      ' FIELDTERMINATOR = ' + '''' + @Terminator + ''', ' +      ' ROWTERMINATOR = ''\n'' ' + ' ) 'EXEC (@SQL)If I execute SP1 I receive an errorMsg 4834, Level 16, State 4, Line 1You do not have permission to use the bulk load statement.But I can execute SP2 with no problem.All operations are done under SA login.Any thoughts/ideas?Thanks

SSIS Send Mail Task - Failure Sending Mail

Dear all, I have switched off the firewall settings on my system and as suggested im entering the minimal information and data to send the mail. but still the Mail Task is failing.. plz suggest.

SSIS Send Mail Task - Failure Sending Mail

Hi Everyone, Hopefully someone out there will have an idea as this is driving me nuts. I've setup a task to email on success/failure and keep receiving the following message when executed: Progress: The SendMail task is initiated. - 0 percent complete[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.". Progress: The SendMail task is completed. - 100 percent completeTask Send Mail Task failed When I configure Outlook Express on the same machine with the same settings it works. On the SMTP Connection Manager I have left the default name, tested with both an IP address and Server Name, and no authentication or SSL. On the Send Mail Task, it uses the above connection. The To: , From: , Subject  fields are populated. Message SourceType is DirectInput, MessageSource is Test, Priority is Normal and no attachments or expressions etc. Nothing useful is logged in the Event Viewer even with full logging turned on. Any suggestions appreciated, Thanks.
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