Skip to content
Advertisement

Error in SSIS Package – There is a data source column with no name

I have a straightforward SQL statement that I posted into a DataFlow Task and got this dreaded error. There is no stored procedure or temp tables involved. What am I missing? The SQL works fine in SSMS and returns appropriate results. The query is pretty simple and I have never seen this error in SSIS before.

select cast([CREATE_DATE] as date) as create_date
      ,[SALES_DOCUMENT_NUM]
      ,[ORDER_LINE_NUM]
      ,[340b_id]
      ,[ALT_CHAIN_ID]
      ,[ALT_CUSTOMER_NUM]
      ,[BUYING_GROUP_NUM]
      ,[BUYING_GROUP_NAME]
      ,[COID]
      , chain_id 
      ,[CHAIN_ID_DESC]
      ,[CITY]
      ,[CURRENT_GL_CODE]
      ,[CURRENT_GL_DESC]
      , [CUSTOMER_NUM]
      ,[CUSTOMER_NAME]
      ,cast(nullif([CUSTOMER_PACK_SIZE] ,'') as numeric(18,3)) as [CUSTOMER_PACK_SIZE]
      ,[DEA_NUM]
      ,[FORMULARY_ITEMS]
      ,[GLN]
      ,[HIN_NUM]
      ,[HISTORICAL_GL_CODE]
      ,[HISTORICAL_GL_DESC]
      ,[STATE]
      ,[STORE_NUM]
      ,[STREET]
      ,[TELEPHONE]
      ,[ZIP_CODE]
      ,[ABC_NUM]
      ,[ABC_6]
      ,[ABCHUN_IN_BLUEBOOK_FORMAT]
      ,[ABC_SELLING_UOM]
      ,[AHFS_THERAPEUTIC_CLASS_CODE]
      ,[AHFS_THERAPEUTIC_CLASS_DESC]
      ,[ABBREVIATED_DESC]
      ,[CORPORATE_ITEM_STATUS]
      ,[CORPORATE_ITEM_STATUS_DESC]
      ,[CURR_HUN_NUMBER]
      ,[DEA_CLASS]
      , [DIVISION_STATUS]
      ,cast(isnull(nullif([FDB_PACKAGE_SIZE_QTY] ,''),0)  as numeric(18,3)) as [FDB_PACKAGE_SIZE_QTY]
      ,cast(isnull(nullif([FDB_SWP_WHOLESALE_FACTOR] ,''),0)  as numeric(18,3)) as [FDB_SWP_WHOLESALE_FACTOR]
      ,[FINE_LINE_CLASS]
      ,[FINE_LINE_CLASS_DESC]
      ,[FORM_CODE]
      ,[GCN]
      ,[GCN_SEQ_NUM]
      ,[GTIN_NUM]
      ,[GENERIC_DESC]
      ,[GENERIC_EXTENDED_DESC]
      ,[GENERIC_ABBREV_DESC]
      ,[GENERIC_FULL_DESC]
      ,[GENERIC_HICL]
      ,[GENERIC_PARENT_NUM]
      ,[HCPCS_CODE]
      ,[HRI_NUM]
      ,[HAZARD_CODE]
      ,[HAZARD_CODE_DESC]
      ,[ITM_CAT_CD]
      ,[ITM_CAT_DSC]
      ,[NDC]
      ,[OMP_ITEM_FAMILY]
      ,[OMP_ITEM_FAMILY_DESC]
      ,[OMP_ITEM_INDICATOR]
      ,cast(isnull(nullif([PACKAGE_SWP] ,''),0)  as numeric(18,3)) as [PACKAGE_SWP]
      ,[PRICE_STICKER_RETAIL_QTY]
      ,[PRIMARY_INGREDIENT_HIC4]
      ,[PRIMARY_INGREDIENT_HIC4_DESC]
      ,[PRIVATE_LABEL_INDICATOR]
      ,[PRODUCT_CATEGORY]
      ,[PRODUCT_CATEGORY_DESC]
      ,[PRODUCT_DESCRIPTION]
      ,[PRODUCT_ENTERED]
      ,[PRODUCT_GROUP]
      ,[PRODUCT_GROUP_DESC]
      ,[PRODUCT_PRICING_CLASS] 
      ,cast(isnull(nullif([SIZE_QTY],''),0)  as numeric(18,3)) as [SIZE_QTY]
      ,[SWP]
      ,[SWP_DATE]
      ,[SINGLE MULTI_SOURCE]
      ,cast(isnull(nullif([STANDARD_UNIT_SWP] ,''),0)  as numeric(18,3)) as [STANDARD_UNIT_SWP]
      ,[SUPPLIER_NUM]
      ,[SUPPLIER_MATERIAL]
      ,[SUPPLIER_NAME]
      ,[UPC_BARCODE]
      ,[UNIT_DOSE_CODE]
      ,cast(isnull(nullif([UNIT_SIZE_QTY] ,''),0)  as numeric(18,3)) as [UNIT_SIZE_QTY]
      ,[UNIT_STRENGTH_CODE]
      ,[UNIT_STRENGTH_QTY]
      ,[CONTRACT_NUM]
      ,[CONTRACT_ABBREV_NAME]
      ,cast(isnull(nullif([CONTRACT_COST] ,''),0)  as numeric(18,3)) as [CONTRACT_COST]
      ,[CONTRACT_EFFECTIVE_DATE]
      ,[CONTRACT_EXP_DATE]
      ,[CONTRACT_NAME]
      ,[CONTRACT_SALE_FLAG]
      ,[CREATED_BY]
      ,[CREDIT_REASON_CODE]
      ,[CREDIT_REASON_DESC]
      ,cast(isnull(nullif([CURRENT_ACQ_COST] ,''),0)  as numeric(18,3)) as [CURRENT_ACQ_COST]
       ,[CUSTOMER_DEPT_NUM]
      ,[CUSTOMER_ITEM_NUM]
      ,[CUSTOMER_PO_NUM]
      ,[DISTRIBUTION_CENTER]
      ,[DROPSHIP_FLAG]
      ,cast(isnull(nullif([DRUG_FORM_COST] ,''),0)  as numeric(18,3)) as [DRUG_FORM_COST]
      ,cast(isnull(nullif([EXCEPTION_QTY] ,''),0)  as numeric(18,3)) as [EXCEPTION_QTY]
      ,cast(isnull(nullif([EXTENDED_WHOLESALE_COST] ,''),0)  as numeric(18,3)) as [EXTENDED_WHOLESALE_COST]
      ,[HISTORICAL_CUSTOMER_ITEM__NUM]
      ,[INVOICE_NUM]
      ,[INVOICE_DATE]
      ,[INVOICE_DUE_DATE]
      ,[INVOICE_LINE_NUM]
      ,[INVOICE_MONTH]
     ,cast(isnull(nullif([INVOICE_PRICE] ,''),0)  as numeric(18,3)) as [INVOICE_PRICE]
      ,[INVOICE_TYPE]
      ,[INVOICE_TYPE_DESC]
      ,[INVOICE_YEAR]
      ,[LAST_INVOICE_NUM]
      ,[LAST_PURCHASE_PRICE]
      ,[LAST_PURCHASE_DATE]
      ,[NARCOTIC_BLANK_NUM]
      ,cast(isnull(nullif([ORDER_QTY] ,''),0)  as numeric(18,3)) [ORDER_QTY]
      ,[ORDER_SOURCE]
      ,[ORDER_SOURCE_DESC]
      ,[ORDER_TYPE_CODE]
      ,[ORDER_TYPE_DESC]
      ,[ORIGINAL_INVOICE_NUM]
      ,[PRICE_METHOD]
      ,[PRICE_METHOD_DESC]
      ,[PROCUREMENT_CODE]
      ,[PROCUREMENT_MESSAGE]
      ,[PROGRAM_CODE]
      ,[PROGRAM_CODE_DESC]
      ,cast(isnull(nullif([QTY_SHIPPED] ,''),0)  as numeric(18,3)) as [QTY_SHIPPED]
      ,[REASON_CODE]
      ,[REJECTION_REASON_CODE]
      ,[REJECTION_REASON_DESC]
      ,cast([SHIP_DATE] as date)
      ,[SPCL_HANDLING_CODE]
      ,[SPCL_HANDLING_CODE_DESC]
      ,[SUBMITTED_BY]
      ,[SUBSTITUTION_CODE]
      ,[SUBSTITUTION_CODE_DESC]
      ,[SUPPLIER_CONTRACT_NUM]
      ,cast(isnull(nullif([TAX_AMOUNT] ,''),0)  as numeric(18,3)) as [TAX_AMOUNT]
      ,cast(isnull(nullif([TOTAL_EXTENDED_COST],''),0)  as numeric(18,3)) as [TOTAL_EXTENDED_COST]
      ,[WAC_WITH_NET_OVERRIDE]
      ,cast(isnull(nullif([WHOLESALE_COST] ,''),0)  as numeric(18,3)) as [WHOLESALE_COST]
      ,[8 BYTE MATNR]
      ,[NDC NUMBER]
      ,[ITEM STS DESCRIP]
      ,[MATERIAL DESCRIP]
      ,cast(isnull(nullif([FDB PKG SIZE] ,''),0)  as numeric(18,3)) as [FDB PKG SIZE]
      ,[AWF SELL FACTOR]
      ,cast(isnull(nullif([PKG METRIC SIZE] ,''),0)  as numeric(18,3)) as [PKG METRIC SIZE]
      ,[GCN SEQ NUMBER]
      ,[GNC GROUP NUMBER]
      ,cast(isnull(nullif([ACQUISITION COST] ,''),0)  as numeric(18,3)) as [ACQUISITION COST]
      ,[CONTRACT NUMBER]
      ,[CONTRACT NAME]
      ,cast(isnull(nullif([ORIG CONTRACT COST] ,''),0)  as numeric(18,3)) as [ORIG CONTRACT COST]
      ,[CONTRACT EFF DATE]
      ,[CONTRACT EXP DATE]
      ,cast(isnull(nullif([ABC WAC COST],''),0)  as numeric(18,3)) as [ABC WAC COST]
      ,cast([ABC WAC EFF DT] as date) as [ABC WAC EFF DT]
      ,cast(isnull(nullif([MSRP PRICE] ,''),0)  as numeric(18,3)) as [MSRP PRICE]
      ,cast(isnull(nullif([CSRP PRICE] ,''),0)  as numeric(18,3)) as [CSRP PRICE]
      ,cast(isnull(nullif([RETAIL PRICE],''),0)  as numeric(18,3)) as [RETAIL PRICE]
      ,[LEGACY ITM NBR]
from dbo.ABC_Invoice_File_Staging inv
left join dbo.ABC_Catalog_File_Staging cat on cast(inv.CUSTOMER_NUM as numeric) = cat.account_number
and inv.CREATE_DATE between cat.filedate and cat.filedate+6
and inv.NDC = cat.[NDC NUMBER]
and inv.[ABC_NUM]=cat.[8 BYTE MATNR]
where inv.CREATE_DATE = '20191015'```

Advertisement

Answer

no column name

CAST([SHIP_DATE] AS date)

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement