Error: Invalid field size for datatype

I am receiving the error "bcp_sendrow failed: HY000 [Microsoft][SQL Server Native Client 11.0]Invalid field size for datatype" when I try to perform the load in a transformation.

Any suggestions?

The target table's create script is:
CREATE TABLE [dbo].[Orders_ShoppingCart](
[OrderNumber] [int] NOT NULL,
[ShoppingCartRecID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[VariantID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[ChosenColor] [nvarchar](100) NULL,
[ChosenColorSKUModifier] [nvarchar](50) NULL,
[ChosenSize] [nvarchar](100) NULL,
[ChosenSizeSKUModifier] [nvarchar](50) NULL,
[OrderedProductName] [ntext] NULL,
[OrderedProductVariantName] [ntext] NULL,
[OrderedProductSKU] [nvarchar](100) NULL,
[OrderedProductManufacturerPartNumber] [nvarchar](50) NULL,
[OrderedProductWeight] [money] NULL,
[OrderedProductPrice] [money] NULL,
[OrderedProductRegularPrice] [money] NULL,
[OrderedProductSalePrice] [money] NULL,
[OrderedProductExtendedPrice] [money] NULL,
[OrderedProductQuantityDiscountName] [nvarchar](max) NULL,
[OrderedProductQuantityDiscountID] [int] NULL,
[OrderedProductQuantityDiscountPercent] [money] NULL,
[IsTaxable] [tinyint] NOT NULL,
[IsShipSeparately] [tinyint] NOT NULL,
[IsDownload] [tinyint] NOT NULL,
[DownloadLocation] [ntext] NULL,
[FreeShipping] [tinyint] NOT NULL,
[IsSecureAttachment] [tinyint] NOT NULL,
[TextOption] [ntext] NULL,
[CartType] [int] NOT NULL,
[SubscriptionInterval] [int] NULL,
[ShippingAddressID] [int] NOT NULL,
[ShippingDetail] [ntext] NULL,
[ShippingMethodID] [int] NULL,
[ShippingMethod] [ntext] NULL,
[DistributorID] [int] NULL,
[GiftRegistryForCustomerID] [int] NULL,
[Notes] [ntext] NULL,
[DistributorEmailSentOn] [datetime] NULL,
[ExtensionData] [ntext] NULL,
[SizeOptionPrompt] [ntext] NULL,
[ColorOptionPrompt] [ntext] NULL,
[TextOptionPrompt] [ntext] NULL,
[CreatedOn] [datetime] NOT NULL,
[SubscriptionIntervalType] [int] NOT NULL,
[CustomerEntersPrice] [tinyint] NOT NULL,
[CustomerEntersPricePrompt] [ntext] NULL,
[IsAKit] [tinyint] NULL,
[IsAPack] [tinyint] NULL,
[IsSystem] [tinyint] NULL,
[TaxClassID] [int] NOT NULL,
[TaxRate] [money] NOT NULL,
[IsGift] [bit] NOT NULL,
[UpdatedOn] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Can you post here sample of the source data please.

We will investigate and get back to you

Peter

OK, so I have messed around with this for hours....

When I disconnect the transforms for all of the NTEXT fields, the transform is successful.

When I connect the OrderProductName and put a Substring on it, the Batch Insert is successful with a SubString length of 10 but crashes AETL when I have length of 11. I have attached Log file, SQL table with sample data, aetl transforms, and screenshots. The Target and the Source tables are identical structure.

When I leave the Substring at 10 and I connect any other of the NTEXT fields, I get: bcp_bind Failed. Field:OrderNumber HY010 [Microsoft][SQL Server Native Client 11.0]Function sequence error

Re this error

OrderNumber HY010 [Microsoft][SQL Server Native Client 11.0]Function sequence error

We will check everhing from our site as usual, but based on our expirience we can say,
that very often it is related to havind old version of MS SQl server ODBC drivers.

Can you make sure that you are using the latest 32 bit drivers please

M.R.

I was unable to install 32 bit driver (I am running on 64 bit machine, I assume that is why). I did update the to the latest 64-bit ODBC driver from www.microsoft.com/en-us/download/details.aspx?id=36434

It says it was version 11, which is what I had before, but I went ahead with it, just in case.

The error DID change and instead of saying "Function sequence error" I got "Invalid Field Size for Data Type" and "Access Violation". The substring from 10 to 11 is still occurring with no change, though.

Here are my ODBC Driver versions and screenshots of the errors.

It this is what you need

www.microsoft.com/en-us/download/details.aspx?id=30440

file is

sqlncli_x86.msi

Mike