There is a data source column with no name.Each data source column must have a name.

  • rashid nizam

    SSCommitted

    Points: 1605

    Hi every body,

    I am trying to get data in SSIS dataflow task from a strore procedure, but strangly i m getting this error

    "Error at Data Flow Task [OLE DB Source [1]]: There is a data source column with no name. Each data source column must have a name.

    Exception from HRESULT: 0xC0207016"

    However i have checked the output of my store proecdure and every column has a name, there isnt any coulmn, with no name, but the SP is complex having some case satatement and column alias...but surely every column has name.

    I wonder how to resolve this issue, i cant find any solution on web, i wonder wat is the problem and how to resolve this issue.

    Any help in this connection is highly appreciated.

  • Mark Beringer-730383

    SSCommitted

    Points: 1714

    Hi

    Have you tried setting the NOCOUNT to ON in the beginning of your SP?

    This could prevent extra result sets from interfering with SELECT statements

    SET NOCOUNT ON

  • rashid nizam

    SSCommitted

    Points: 1605

    yes SET NOCOUNT is ON in the beginnig of SP,

    let me paste the code here.........

    /****** Object: StoredProcedure [dbo].[usp_Inv_Lot_Ship_History_2201s] Script Date: 06/16/2008 02:40:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[usp_Inv_Lot_Ship_History_2201s]

    AS

    /*

    ** This proc replaces the view Inv_Lot_Ship_History_2201s.

    **

    ** Version History

    ** 1 04/20/2007 kchong Initial Version

    ** 2 05/27/2008 bsmet Add time restriction

    **

    */

    if (datepart(hh, getdate()) not between 12 and 24)

    begin

    select 'Error - Running outside allowed time window'

    select 1/0

    return

    end

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET NOCOUNT ON

    CREATE TABLE #PSXLATITEM (

    FIELDNAME varchar(18),

    FIELDVALUE varchar(4),

    EFF_STATUS varchar(1),

    XLATLONGNAME varchar(30)

    )

    INSERT INTO #PSXLATITEM

    SELECT

    FIELDNAME,

    FIELDVALUE,

    EFF_STATUS,

    XLATLONGNAME

    FROM SPOT_RAW..PSXLATITEM

    CREATE INDEX IDX_PSXLATITEM ON #PSXLATITEM (FIELDNAME, FIELDVALUE, EFF_STATUS, XLATLONGNAME)

    CREATE TABLE #PS_EN_BOM_COMPS (

    INV_ITEM_ID varchar(18),

    COMPONENT_ID varchar(18),

    DATE_IN_EFFECT datetime,

    DATE_OBSOLETE datetime)

    INSERT INTO #PS_EN_BOM_COMPS

    SELECT

    INV_ITEM_ID,

    COMPONENT_ID,

    CONVERT(VARCHAR(10),DATE_IN_EFFECT,101),

    CONVERT(VARCHAR(10),DATE_OBSOLETE,101)

    FROM

    SPOT_RAW..PS_EN_BOM_COMPS

    CREATE INDEX IDX_PS_EN_BOM_COMPS ON #PS_EN_BOM_COMPS (INV_ITEM_ID, DATE_IN_EFFECT, DATE_OBSOLETE)

    CREATE TABLE #PS_INV_ITEMS

    (

    SETID varchar(5),

    INV_ITEM_ID varchar(18),

    EFFDT datetime,

    INV_ITEM_TYPE varchar(5)

    )

    INSERT INTO #PS_INV_ITEMS

    SELECT

    SETID,

    INV_ITEM_ID,

    EFFDT,

    INV_ITEM_TYPE

    FROM SPOT_RAW..PS_INV_ITEMS F

    WHERE

    F.SETID='SHARE' AND (F.INV_ITEM_TYPE='CHIP' OR F.INV_ITEM_TYPE='TRNKY')

    CREATE INDEX IDX_PS_INV_ITEMS ON #PS_INV_ITEMS (INV_ITEM_ID)

    CREATE INDEX IDX_PS_INV_ITEMS1 ON #PS_INV_ITEMS (SETID,INV_ITEM_ID,EFFDT)

    SELECT

    A.BUSINESS_UNIT,

    A.DEMAND_SOURCE,

    A.SOURCE_BUS_UNIT,

    A.ORDER_NO,

    A.ORDER_INT_LINE_NO,

    A.SCHED_LINE_NO,

    A.INV_ITEM_ID,

    A.DEMAND_LINE_NO,

    A.INV_LOT_ID,

    A.DT_TIMESTAMP,

    SH.SHIP_CUST_NAME1,

    SH.CUST_NAME,

    SH.PARENT_PROD_ID,

    SH.PRODUCT_ID

    INTO #A_SH

    FROM SPOT_RAW..PS_DEMAND_PHYS_INV A with (nolock)

    INNER JOIN

    SPOT_RAW..PS_SHIP_INF_INV SH with (nolock)

    ON A.BUSINESS_UNIT=SH.BUSINESS_UNIT

    AND A.DEMAND_SOURCE=SH.DEMAND_SOURCE

    AND A.SOURCE_BUS_UNIT=SH.SOURCE_BUS_UNIT

    AND A.ORDER_NO=SH.ORDER_NO

    AND A.ORDER_INT_LINE_NO=SH.ORDER_INT_LINE_NO

    AND A.SCHED_LINE_NO=SH.SCHED_LINE_NO

    AND A.INV_ITEM_ID=SH.INV_ITEM_ID

    AND A.DEMAND_LINE_NO=SH.DEMAND_LINE_NO

    AND SH.SHIPPED_FLAG='Y'

    where A.DEMAND_SOURCE='OM'

    CREATE INDEX IDX_A_SH ON #A_SH (INV_ITEM_ID, INV_LOT_ID)

    DECLARE @DATE VARCHAR(10)

    SET @DATE = CONVERT(VARCHAR(10),GETDATE(),101)

    SELECT

    A.INV_ITEM_ID AS "BRCM Item",

    B.MODEL_NBR AS "Marketing Part",

    RIGHT(A.INV_LOT_ID,9) AS "Lot ID",

    LEFT(A.INV_LOT_ID,6) AS "Date Code",

    SUBSTRING(A.INV_LOT_ID,3,4) AS "Datecode YYWW",

    A.ORDER_NO AS "Order No",

    A.ORDER_INT_LINE_NO AS "Order Line",

    A.SHIP_CUST_NAME1 AS "ShipTo Name",

    A.CUST_NAME AS "SoldTo Name",

    "End Cust Name" =

    ISNULL((SELECT max(CU.NAME1)

    FROM

    SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

    SPOT_RAW..PS_SET_CNTRL_REC SI,

    SPOT_RAW..PS_CUSTOMER CU

    WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

    AND A.ORDER_NO = BOL.ORDER_NO

    AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

    AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

    AND SI.REC_GROUP_ID='FS_23'

    AND SI.RECNAME='INV_CUST_ADR_VW'

    AND SI.SETID=CU.SETID

    AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),

    "End-End Cust Name" =

    ISNULL((SELECT max(CU.NAME1)

    FROM

    SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

    SPOT_RAW..PS_SET_CNTRL_REC SI,

    SPOT_RAW..PS_CUSTOMER CU

    WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

    AND A.ORDER_NO = BOL.ORDER_NO

    AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

    AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

    AND SI.REC_GROUP_ID='FS_23'

    AND SI.RECNAME='INV_CUST_ADR_VW'

    AND SI.SETID=CU.SETID

    AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),

    "Customer Product ID" =

    ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)

    FROM

    SPOT_RAW..PS_BRC_ORD_PRODKIT KIT

    WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT

    AND A.ORDER_NO = KIT.ORDER_NO

    AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO

    AND A.PARENT_PROD_ID=KIT.PRODUCT_ID

    AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),

    C.ITEM_FIELD_C4 as "P-Code",

    "Die Item"=

    ISNULL((SELECT max(BOM.COMPONENT_ID)

    FROM #PS_EN_BOM_COMPS BOM

    WHERE

    A.INV_ITEM_ID=BOM.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

    AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE

    AND 1=(SELECT COUNT(*) FROM #PS_EN_BOM_COMPS BOM2

    WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

    AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),

    J.BRC_PART_DIE),

    C.ITEM_FIELD_C10_C AS "Core Part",

    D.BRC_FAB_ID AS "Fab Lot ID",

    D.COUNTRY_ORIGIN AS "COO",

    G.BRC_A_VEND AS "Assy Vendor ID",

    H.VNDR_NAME_SHRT_USR AS "Assy Vendor",

    G.BRC_F_VEND AS "Fab Vendor ID",

    I.VNDR_NAME_SHRT_USR AS "Fab Vendor",

    XPROCESS.XLATLONGNAME as "Fab Process",

    XPHASE.XLATLONGNAME as "Item Phase",

    XPKGTYP.XLATLONGNAME as "Package Type",

    XPKGSIZ.XLATLONGNAME as "Package Size",

    J.BRC_PKG_PIN_COUNT as "Pin Count",

    F.INV_ITEM_TYPE as "Item Type",

    A.DT_TIMESTAMP

    FROM

    #A_SH A

    LEFT OUTER JOIN

    SPOT_RAW..PS_BRC_LOT_CNTL D

    ON A.INV_ITEM_ID=D.INV_ITEM_ID

    AND A.INV_LOT_ID=D.INV_LOT_ID

    INNER JOIN

    #PS_INV_ITEMS F

    ON A.INV_ITEM_ID=F.INV_ITEM_ID

    INNER JOIN

    SPOT_RAW..PS_PROD_ITEM B

    ON A.INV_ITEM_ID=B.INV_ITEM_ID

    AND B.SETID='SHARE'

    INNER JOIN

    SPOT_RAW..PS_BRC_ITEM_TBL J

    ON B.SETID=J.SETID

    AND B.INV_ITEM_ID=J.INV_ITEM_ID

    INNER JOIN

    SPOT_RAW..PS_MASTER_ITEM_TBL C

    ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPHASE

    ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE

    AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'

    AND XPHASE.EFF_STATUS='A'

    INNER JOIN

    SPOT_RAW..PS_BRC_DC_PREFX G

    ON SUBSTRING(A.INV_LOT_ID,1,2)= G.BRC_DC_PREFIX

    AND G.SETID='SHARE'

    LEFT OUTER JOIN

    SPOT_RAW..PS_VENDOR H

    ON G.BRC_A_VEND_SETID=H.SETID

    AND G.BRC_A_VEND=H.VENDOR_ID

    LEFT OUTER JOIN

    SPOT_RAW..PS_VENDOR I

    ON G.BRC_F_VEND_SETID=I.SETID --

    AND G.BRC_F_VEND=I.VENDOR_ID

    LEFT OUTER JOIN

    #PSXLATITEM AS XPKGTYP

    ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE

    AND XPKGTYP.FIELDNAME='BRC_PACKAGE'

    AND XPKGTYP.EFF_STATUS='A'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPROCESS

    ON J.BRC_PROCESS=XPROCESS.FIELDVALUE

    AND XPROCESS.FIELDNAME='BRC_PROCESS'

    AND XPROCESS.EFF_STATUS='A'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPKGSIZ

    ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE

    AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'

    AND XPKGSIZ.EFF_STATUS='A'

    INNER JOIN

    SPOT_RAW..PS_ORD_LINE OL

    ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT

    AND A.ORDER_NO=OL.ORDER_NO

    AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO

    WHERE

    F.EFFDT=

    (SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV

    WHERE F.SETID = INV.SETID

    AND F.INV_ITEM_ID=INV.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))

    UNION ALL

    SELECT

    A.INV_ITEM_ID AS "BRCM Item",

    B.MODEL_NBR AS "Marketing Part",

    A.INV_LOT_ID AS "Lot ID",

    LEFT(A.INV_LOT_ID,6) AS "Date Code",

    'N/A' AS "Datecode YYWW",

    A.ORDER_NO AS "Order No",

    A.ORDER_INT_LINE_NO AS "Order Line",

    A.SHIP_CUST_NAME1 AS "ShipTo Name",

    A.CUST_NAME AS "SoldTo Name",

    "End Cust Name" =

    ISNULL((SELECT max(CU.NAME1)

    FROM

    SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

    SPOT_RAW..PS_SET_CNTRL_REC SI,

    SPOT_RAW..PS_CUSTOMER CU

    WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

    AND A.ORDER_NO = BOL.ORDER_NO

    AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

    AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

    AND SI.REC_GROUP_ID='FS_23'

    AND SI.RECNAME='INV_CUST_ADR_VW'

    AND SI.SETID=CU.SETID

    AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),

    "End-End Cust Name" =

    ISNULL((SELECT max(CU.NAME1)

    FROM

    SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

    SPOT_RAW..PS_SET_CNTRL_REC SI,

    SPOT_RAW..PS_CUSTOMER CU

    WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

    AND A.ORDER_NO = BOL.ORDER_NO

    AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

    AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

    AND SI.REC_GROUP_ID='FS_23'

    AND SI.RECNAME='INV_CUST_ADR_VW'

    AND SI.SETID=CU.SETID

    AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),

    "Customer Product ID" =

    ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)

    FROM

    SPOT_RAW..PS_BRC_ORD_PRODKIT KIT

    WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT

    AND A.ORDER_NO = KIT.ORDER_NO

    AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO

    AND A.PARENT_PROD_ID=KIT.PRODUCT_ID

    AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),

    C.ITEM_FIELD_C4 as "P-Code",

    "Die Item"=

    ISNULL((SELECT max(BOM.COMPONENT_ID)

    FROM

    #PS_EN_BOM_COMPS BOM

    WHERE

    A.INV_ITEM_ID=BOM.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

    AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE

    AND 1=(SELECT COUNT(*) FROM

    #PS_EN_BOM_COMPS BOM2

    WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

    AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),

    J.BRC_PART_DIE),

    C.ITEM_FIELD_C10_C AS "Core Part",

    D.BRC_FAB_ID AS "Fab Lot ID",

    D.COUNTRY_ORIGIN AS "COO",

    'N/A' AS "Assy Vendor ID", 'N/A' AS "Assy Vendor",

    'N/A' AS "Fab Vendor ID", 'N/A' AS "Fab Vendor",

    XPROCESS.XLATLONGNAME as "Fab Process",

    XPHASE.XLATLONGNAME as "Item Phase",

    XPKGTYP.XLATLONGNAME as "Package Type",

    XPKGSIZ.XLATLONGNAME as "Package Size",

    J.BRC_PKG_PIN_COUNT as "Pin Count",

    F.INV_ITEM_TYPE as "Item Type",

    A.DT_TIMESTAMP

    FROM

    #A_SH A

    LEFT OUTER JOIN

    SPOT_RAW..PS_BRC_LOT_CNTL D

    ON A.INV_ITEM_ID=D.INV_ITEM_ID

    AND A.INV_LOT_ID=D.INV_LOT_ID

    INNER JOIN

    #PS_INV_ITEMS F

    ON A.INV_ITEM_ID=F.INV_ITEM_ID

    INNER JOIN

    SPOT_RAW..PS_PROD_ITEM B

    ON A.INV_ITEM_ID=B.INV_ITEM_ID

    AND B.SETID='SHARE'

    INNER JOIN

    SPOT_RAW..PS_BRC_ITEM_TBL J

    ON B.SETID=J.SETID

    AND B.INV_ITEM_ID=J.INV_ITEM_ID

    INNER JOIN

    SPOT_RAW..PS_MASTER_ITEM_TBL C

    ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPHASE

    ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE

    AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'

    AND XPHASE.EFF_STATUS='A'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPKGTYP

    ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE

    AND XPKGTYP.FIELDNAME='BRC_PACKAGE'

    AND XPKGTYP.EFF_STATUS='A'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPROCESS

    ON J.BRC_PROCESS=XPROCESS.FIELDVALUE

    AND XPROCESS.FIELDNAME='BRC_PROCESS'

    AND XPROCESS.EFF_STATUS='A'

    LEFT OUTER JOIN

    #PSXLATITEM AS XPKGSIZ

    ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE

    AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'

    AND XPKGSIZ.EFF_STATUS='A'

    INNER JOIN

    SPOT_RAW..PS_ORD_LINE OL

    ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT

    AND A.ORDER_NO=OL.ORDER_NO

    AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO

    WHERE

    F.EFFDT=

    (SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV

    WHERE F.SETID = INV.SETID

    AND F.INV_ITEM_ID=INV.INV_ITEM_ID

    AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))

    AND NOT EXISTS

    (SELECT * FROM SPOT_RAW..PS_BRC_DC_PREFX DC

    WHERE SUBSTRING(A.INV_LOT_ID,1,2)= DC.BRC_DC_PREFIX

    AND DC.SETID='SHARE')

    DROP TABLE #PSXLATITEM

    DROP TABLE #PS_EN_BOM_COMPS

    DROP TABLE #PS_INV_ITEMS

    DROP TABLE #A_SH

  • Matt Miller (4)

    SSC Guru

    Points: 124166

    hmm...needle in the haystack time. The best I can offer up would be to start commenting sections out to isolate where the error might be. The second though would be to double-check your views if any are involved.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • matzer

    SSCrazy Eights

    Points: 9726

    The 'column has no name' error is caused by the statement 'select 1/0'. I suggest to use the RAISERROR statement for raising errors. I tested this using the following procedure:

    create procedure up_test

    as

    begin

    if (datepart(hh, getdate()) not between 12 and 24)

    begin

    -- select 'Error - Running outside allowed time window'

    -- select 1/0

    -- return

    raiserror ('Error - Running outside allowed time window', 16, 0)

    end

    select 'A' as A into #temp

    select A from #temp t

    drop table #temp

    end

    Next problem you will face is that BIDS is not able to collect metadata of procedures returning data from temporary tables. If you call the procedure above with an OLE DB reader, the object will have no columns. Questions about this behaviour in SSRS projects comes up every now and then (for example, see the thread last week started by someone name 'scott'). In SSRS the workaround is to add the columns of the output of the stored procedure manually to a dataset. Unfortunately, in the above example I wasn't able to add the column using the Advanced Editor. If I hit the datatype property, the dialog disappears. Anybody know what the 'Add column' button is for?

    Peter

  • rashid nizam

    SSCommitted

    Points: 1605

    Thnx peter you are abs rite, column has no name, error was coz of select 1/0 statement, and yup now i m getting same problem that u have mentioned with BIDS when it invloves temporary tables it returns no cloumns.

    I m working on this, if you get any scuess to get hold on this problem without changing SP plz reply me on this forum.

    Thanks once again for ur feedback n help.

  • matzer

    SSCrazy Eights

    Points: 9726

    After some googling I found two solutions:

    1. use a dummy select statement as the first select statement in your procedure to provide the metadata:

    ALTER procedure [dbo].[up_test]

    as

    begin

    set nocount on

    if 1=0

    begin

    select cast('A' as varchar(1)) as A

    end

    select 'A' as A into #temp

    select * from #temp

    drop table #temp

    end

    2. Use table variables instead of temporary tables

    ALTER procedure [dbo].[up_test]

    as

    begin

    set nocount on

    declare @temp table (A varchar(1))

    insert into @temp values ('A')

    select * from @temp

    end

    Peter

  • David Jahngir

    SSC-Addicted

    Points: 409

    I have the below query in a data flow and cannot get it to work no matter what I do

    SELECT DISTINCT

    B12.MODLN_USC_C + ';' +

    LEFT(ENT_ENTITY_C, 1) +

    SUBSTRING(ENT_ENTITY_C, 3, 1) +

    SUBSTRING(ENT_ENTITY_C, 6, 1) +

    SUBSTRING(ENT_ENTITY_C, 4, 1) +

    SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +

    b10Opt.USC_C + ';' +

    LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, '')))

    FROM

    (select ent_entity_c, modln_usc_c, ent_opt_usc_c

    from CDMStaging..mfslb12_entlst

    where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12

    INNER JOIN

    (SELECT usc_c, usc_desc_x

    FROM CDMStaging..mfslb10_elm

    where mkt_c = 'FF') As B10Opt

    on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0

    ORDER BY 1

    I have tried the dummy data methods but still no joy.

  • Koen Verbeeck

    SSC Guru

    Points: 258888

    David Jahngir (1/27/2011)


    I have the below query in a data flow and cannot get it to work no matter what I do

    SELECT DISTINCT

    B12.MODLN_USC_C + ';' +

    LEFT(ENT_ENTITY_C, 1) +

    SUBSTRING(ENT_ENTITY_C, 3, 1) +

    SUBSTRING(ENT_ENTITY_C, 6, 1) +

    SUBSTRING(ENT_ENTITY_C, 4, 1) +

    SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +

    b10Opt.USC_C + ';' +

    LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, '')))

    FROM

    (select ent_entity_c, modln_usc_c, ent_opt_usc_c

    from CDMStaging..mfslb12_entlst

    where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12

    INNER JOIN

    (SELECT usc_c, usc_desc_x

    FROM CDMStaging..mfslb10_elm

    where mkt_c = 'FF') As B10Opt

    on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0

    ORDER BY 1

    I have tried the dummy data methods but still no joy.

    What are the errors that you get?

    Also, is it possible to bring some formatting in your code, because it's a real pain to read.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • David Jahngir

    SSC-Addicted

    Points: 409

    I get the "Error at Data Flow Task [OLE DB Source [1]]: There is a data source column with no name. Each data source column must have a name.

    Exception from HRESULT: 0xC0207016" error

  • David Jahngir

    SSC-Addicted

    Points: 409

    Problem fixed using the below code and adding the as col1 highlighted

    --File 2 OPTION.txt

    SELECT DISTINCT

    B12.MODLN_USC_C + ';' +

    LEFT(ENT_ENTITY_C, 1) +

    SUBSTRING(ENT_ENTITY_C, 3, 1) +

    SUBSTRING(ENT_ENTITY_C, 6, 1) +

    SUBSTRING(ENT_ENTITY_C, 4, 1) +

    SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +

    b10Opt.USC_C + ';' +

    LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, ''))) as col1

    FROM

    (select ent_entity_c, modln_usc_c, ent_opt_usc_c

    from CDMStaging..mfslb12_entlst

    where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12

    INNER JOIN

    (SELECT usc_c, usc_desc_x

    FROM CDMStaging..mfslb10_elm

    where mkt_c = 'FF') As B10Opt

    on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0

    ORDER BY 1

  • David Jahngir

    SSC-Addicted

    Points: 409

    Problem solved with the below code and adding (as col1) where highlighted..

    --File 2 OPTION.txt

    SELECT DISTINCT

    B12.MODLN_USC_C + ';' +

    LEFT(ENT_ENTITY_C, 1) +

    SUBSTRING(ENT_ENTITY_C, 3, 1) +

    SUBSTRING(ENT_ENTITY_C, 6, 1) +

    SUBSTRING(ENT_ENTITY_C, 4, 1) +

    SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +

    b10Opt.USC_C + ';' +

    LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, ''))) as col1

    FROM

    (select ent_entity_c, modln_usc_c, ent_opt_usc_c

    from CDMStaging..mfslb12_entlst

    where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12

    INNER JOIN

    (SELECT usc_c, usc_desc_x

    FROM CDMStaging..mfslb10_elm

    where mkt_c = 'FF') As B10Opt

    on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0

    ORDER BY 1

  • Ray Sotkiewicz

    Right there with Babe

    Points: 796

    The reason you're getting the "No Column Name" error is because you're using temp tables. SSIS can't read metadata from your statement due to this issue.

    My workaround is instead of using temp tables, I use permanent staging tables, and divide the failing query into 2 sections:

    1. Stage the data

    2. Read the data.

    Works every time.

  • lovinglychinna

    Valued Member

    Points: 70

    Check if any of the column resulted by your query or procedure consists with out column name [no column name]

  • jeimarzsupan

    Newbie

    Points: 9

    Just always make sure to put an alias to any column you are customizing/formatting. It's like this:

    SELECT ID,

    FName+' '+MNane+' 'LName FullName, --on this Line, Name is the Alias

    ISNULL(Address, 'TBD') FullAddress, --on this Line, FullAddress is the Alias

    Occupation,

    Month(Birthday) BirthMonth --on this Line, it is the BirthMonth

    FROM TBL_PeronalInfo

    Hope I explained it clearly...

Viewing 15 posts - 1 through 15 (of 16 total)

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