Why Azure data factory is throwing error when pulling data from on premise sql server?

Question

Dear All,

I am new in Azure Data Factory, and trying to pull data from on premise SQL Server database AdwentureWorks2019 Employee Table.

When I loaded same Employee data into csv file on Azure Blob storage, its get succeeded very easily. But when I am trying to load the same to Azure SQL Sever(Target) database, It is throwing error.

Error:
ErrorCode=SqlBulkCopyInvalidColumnLength,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL Bulk Copy failed due to receive an **invalid column length from the bcp client.,**Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The service has encountered an error processing your request. Please try again. Error code 4815. A severe error occurred on the current command. The results, if any, should be discarded.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40197,Class=20,ErrorCode=-2146232060,State=1,Errors=[{Class=20,Number=40197,State=1,Message=The service has encountered an error processing your request. Please try again. Error code 4815.,},{Class=20,Number=0,State=0,Message=A severe error occurred on the current command. The results, if any, should be discarded.,},],'

Answer 1

Ensure all data types are mapped correctly and similar data types are available in target also the length.

Employee table in Adv works 2019 DB has some columns having user defined data types. If you are using ADF for practice or test, I'd suggest you to try copy some simpler smaller table

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

I just tested this and it worked fine after removing the computed column from the destination table:

CREATE TABLE [HumanResources].[Employee](
 [BusinessEntityID] [int] NOT NULL,
 [NationalIDNumber] [nvarchar](15) NOT NULL,
 [LoginID] [nvarchar](256) NOT NULL,
 [OrganizationNode] [hierarchyid] NULL,
 [OrganizationLevel]  smallint null,
 [JobTitle] [nvarchar](50) NOT NULL,
 [BirthDate] [date] NOT NULL,
 [MaritalStatus] [nchar](1) NOT NULL,
 [Gender] [nchar](1) NOT NULL,
 [HireDate] [date] NOT NULL,
 [SalariedFlag] [dbo].[Flag] NOT NULL,
 [VacationHours] [smallint] NOT NULL,
 [SickLeaveHours] [smallint] NOT NULL,
 [CurrentFlag] [dbo].[Flag] NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
 [BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

It's possible that this is a network issue. Is there any more information in the Integration runtime logs? What are the IR's proxy settings? If going through a proxy, try changing to direct connection.

Answer 3

Hi @Sonu Ojha ,

This error because the length of the data coming into destination column is bigger than the column size defined. I suggest that you can chose a data type that is large enough for your destination.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.