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)