June 16, 2008 at 1:38 am
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.
June 16, 2008 at 3:23 am
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
June 16, 2008 at 3:48 am
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
June 16, 2008 at 6:29 am
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?
June 16, 2008 at 1:41 pm
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
June 17, 2008 at 3:45 am
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.
June 17, 2008 at 12:56 pm
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
January 27, 2011 at 6:26 am
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.
January 27, 2011 at 1:45 pm
David Jahngir (1/27/2011)
I have the below query in a data flow and cannot get it to work no matter what I doSELECT 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
January 28, 2011 at 2:27 am
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
January 28, 2011 at 4:18 am
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
January 28, 2011 at 4:31 am
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
October 19, 2011 at 12:16 pm
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.
July 23, 2012 at 6:57 am
Check if any of the column resulted by your query or procedure consists with out column name [no column name]
June 14, 2013 at 2:08 am
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 15 total)
You must be logged in to reply to this topic. Login to reply