question

SonuOjha-3226 avatar image
0 Votes"
SonuOjha-3226 asked CarrinWu-MSFT commented

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

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.,},],'

77246-image.png


sql-server-generalazure-data-factory
image.png (35.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you create the table in Azure SQL manually or it was auto created?

0 Votes 0 ·

Table created by DDL from on premise sql server.

0 Votes 0 ·

If you generated create table script from on prem and used the same in Azure SQL, how is the data type different for 2 flag columns?

0 Votes 0 ·

Hi @SonuOjha-3226, We have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered SonuOjha-3226 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Vaibhav,

I tried other table (Address) from on premise sql server to Azure SQL(target) and it loaded without any issue.
But when I am trying to load [HumanResources].[Employee] to Azure SQL db with exact same structure, same datatype, it is failing.

Even I tried after removing below 3 columns. Still it is throwing the same error.



  • Columns


[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,




0 Votes 0 ·
DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered DavidBrowne-msft commented

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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi David,

I think, network is fine, as I when I am running few other tables, and its working absolutely fine.

Regards,
Sonu

0 Votes 0 ·

By the way, this same DDL can't be executed successfully as it has dbo.flag as datatype in it at 2 places.

Could you please share the table structure what you have created in Target DB ( Azure SQL Server)?

0 Votes 0 ·

The database I ran it in has that user-defined type:


 CREATE TYPE [dbo].[Flag] FROM [bit] NOT NULL



0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @SonuOjha-3226,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.