Post reply

Could not bulk load. The sorted column XYZ is not valid

  • July 6, 2015 at 8:17 am

    #298323

    Hi everyone,

    I am trying to test a bulk load in a empty table with a clustered index, without dropping the index.

    I have a single DFT with a OLE DB Source direct mapped to a OLE DB Target.

    The records are sorted by the column, let's say XYZ. I edited the advance properties of the source to set it as sorted, and put a "1" in the XYZ column Sort Key Position property.

    I set the target to use a "Table or view - fast load" with "0" as the Maximum insert commit size, table lock and the ORDER(XYZ Asc) in the Fast Load Options.

    I want to use the same key as in the source in my target table.

    The target table has no identity specified for the primary key.

    The data type of the XYZ is big int. I am using SQL Server 2012.

    I am getting this error message and I don't really know what else can I do:

    Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Could not bulk load. The sorted column 'XYZ' is not valid. The ORDER hint is ignored.".

    Could anyone help me? Any comment will be appreciated.

    Kind Regards,

    Paul Hernández

  • July 8, 2015 at 8:10 am

    #1810963

    Hi

    Did you put the property IsSorted to True ?

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 9, 2015 at 12:52 am

    #1811185

    Hi Mohamed,

    yes, I edited the output of the OLE DB source using the advance editor and set the output as sorted and the sorted column as sorted key = 1, but it didn't work.

    Kind Regards,

    Paul Hernández

  • July 9, 2015 at 2:49 am

    #1811212

    Hi Paul

    Can you post the sql code source entry

    and the structure of the destination table ?

    Please.

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 9, 2015 at 6:27 am

    #1811261

    Hi Mohamed,

    thanks for your interest:

    The source table name is dbo.SalesOrders and has an identity big int primary key SalesOrdersId

    This is the source query:

    SELECT

    NS.SalesOrdersId ,

    NS.Qty AS NatSalesQty,

    NS.GrossSales AS NatSalesGrossValueDomestic,

    NS.NetSales AS NatSalesNetValueDomestic,

    NatArt. ArticleId,

    NS.IntAssignedReportingSeasonId AS IntAssignedReportingSeasonId,

    NS.ISOCurrId AS ISOCurrId,

    CAST(CAST(c.IntPupMasterId AS NVARCHAR(10)) + '-' + CAST(c.IntPDUId AS NVARCHAR(10)) + '-' + c.NatCustNo AS NVARCHAR(50)) AS CustomerId,

    ISNULL(CAST(CAST(s.IntPupMasterId AS NVARCHAR(10)) + '-' + CAST(s.IntPDUId AS NVARCHAR(10)) + '-' + s.NatSalesmanCode AS NVARCHAR(50)), CAST(0 AS NVARCHAR(50))) AS SalesmanId,

    NS.IntPDUId AS IntPDUId,

    --ETL technical columns

    NS.ValidFrom AS ValidFromDate,

    NS.ValidTo AS ValidToDate,

    NS.NatSalesCrtDate

    FROM

    dbo.SalesOrders NS with (nolock)

    INNER JOIN dbo.Articles NatArt with (nolock) ON NS.[ArticleHashValueBK] = NatArt.[HashValueBK] AND NatArt.[CurrentFlag] = 1 -- identify latest version of article

    INNER JOIN dbo.Customers c with (nolock) ON NS.[CustomerHashValueBK] = c.[HashValueBK] AND c.[CurrentFlag] = 1 -- identify latest version of customer

    INNER JOIN dbo.BusClass cbc with (nolock) ON c.IntCustBusClassId = cbc.IntCustBusClassId

    OUTER APPLY (SELECT MAX(D.DateId) AS DateId FROM dbo.Dates D with (nolock) WHERE D.DateCode = CAST(NS.ValidTo AS DATE) OR CAST(NS.ValidTo AS DATE) = CAST('99991231' AS DATE)) ValidToDate

    WHERENS.InterfaceId =34 AND c.NatIsCustFlag = 1

    AND cbc.IntCustBusClassCode <> 20

    AND (NS.[ValidFrom] > '' OR (NS.[ValidTo] > '' AND NS.[ValidTo] <> CONVERT(datetime,'9999-12-31')))

    Order by dbo.SalesOrders.SalesOrdersId ASC

    The target table has the same structure of the source table, the only difference is the primary key is not an identity column (use the surrogated key from the source instead). And this one, is the one I want it to use in the ORDER hint.

    This is the structure:

    CREATE TABLE [fact].[SalesOrders](

    [SalesOrdersId] [bigint] NOT NULL,

    [NatSalesQty] [decimal](13, 0) NOT NULL,

    [NatSalesGrossValueDomestic] [decimal](17, 2) NOT NULL,

    [NatSalesNetValueDomestic] [decimal](17, 2) NOT NULL,

    [ArticleId] [nvarchar](100) NOT NULL,

    [ISOCurrId] [int] NOT NULL,

    [CustomerId] [nvarchar](50) NOT NULL,

    [ValidFromId] [int] NOT NULL,

    [ValidToId] [int] NOT NULL,

    [CurrentFlag] [bit] NOT NULL,

    [IntPupMasterId] [int] NOT NULL,

    CONSTRAINT [PK_SalesOrders] PRIMARY KEY CLUSTERED

    (

    [SalesOrdersId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    I will try to create an isolated example using adventure works or a sample database to see if it works.

    Kind Regards,

    Paul Hernández

  • July 9, 2015 at 6:50 am

    #1811280

    Thank you for posting the code

    I have another question.

    Are you sure your query source does not have duplicate lines on the salesorderid ?

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 9, 2015 at 8:55 am

    #1811346

    I checked and there are no duplicates in the source query.

    I need to verify the collation of the databases, but a bigint column shouldn't have problems with different collations.

    Paul Hernández

  • July 9, 2015 at 9:52 am

    #1811364

    Do you have replication enabled on your target database ?

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 10, 2015 at 6:25 am

    #1811581

    To be honest my knowledge about replication is very limited but I think there is nothing configured:

    I will ask the system admins to confirm.

    Paul Hernández

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply