SSIS with Dynamic SQL - No column information was returned

Hello, experts.
I have some trouble executing a sql statement in SSIS package.
Here is what I'm trying to do.
I have a SQL statement (or stored proc) that I'm running to get a result set, and I want to put the result set into flat files.

In the sql statement, I have a dynamic query that will populate the data.
When I run it in SSMS, it runs without problem.
However, when I try in SSIS, I run into a problem.

I have used both temp table and table variable , but the result is same.
Please help me with this problem.

Thanks!
kmc10314Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
More than likely you have failed to include SET NOCOUNT ON in your Stored Procedure or it does not return any resultsets, but post your Stored Procedure and we can tell you for sure.
0
kmc10314Author Commented:
Actually, I found a solution.
It was because I was using OLE DB Connection.
For some reason, it seems like OLE DB Connection cannot process temp table or dynamic query. Once I used ADO Connection, it worked fine
0
Anthony PerkinsCommented:
For some reason, it seems like OLE DB Connection cannot process temp table or dynamic query.
Sure it can, but glad you found a workaround.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For some reason, it seems like OLE DB Connection cannot process temp table or dynamic query.
You are correct in that SSIS cannot read the schema of a dynamic query, so it doesn't know how to 'contract' those columns as a data source for mapping.

If you want to go the SSIS route, you'll need a physical table as the source, regardless of how it is populated.
0
kmc10314Author Commented:
Acperkins,
Here is the code

DROP TABLE dbo.Scheduled_Load
DROP TABLE dbo.Scheduled_Load_Detail

DECLARE @Seq_Num INT
SET @Seq_Num = 0

SELECT IDENTITY(INT, 1, 1) AS Seq_Num, CH.HospID, CH.DropoffLocation, CH.FileName
INTO dbo.Scheduled_Load
FROM Level2WorkArea.dbo.CorpLoadHospital CH
INNER JOIN Level2WorkArea.dbo.CorpLoadSchedule CS ON CH.HospID = CS.HospID
WHERE CS.ScheduleDate = CONVERT(DATE, GETDATE()) 

DECLARE @Row_Count INT 
SET @Row_Count = (SELECT COUNT(*) FROM dbo.Scheduled_Load) 

IF @Row_Count = 0 
	BEGIN
		RETURN
	END	
	
ELSE 
	BEGIN
		DECLARE @StarStatement0 VARCHAR(8000) 
		
		SET @StarStatement0 = 
		'DECLARE @Extraction_STAR TABLE
		(
			HospID VARCHAR(10) NOT NULL,
			FIMActive INT NOT NULL,
			FIMChargeCode VARCHAR(20) NOT NULL,
			FIMDepartment NUMERIC(10, 0) NOT NULL,
			FIMDescrption VARCHAR(50) NOT NULL,
			FIMDateCreated DATETIME NULL,
			SIMActive INT NOT NULL,
			SIMChargeCode VARCHAR(10) NOT NULL,
			SIMDepartment INT NOT NULL,
			SIMDescrption VARCHAR(50) NOT NULL,
			SIMDateCreated DATETIME NULL,
			ServiceType VARCHAR(100) NULL,
			dHCPCSCode VARCHAR(10) NULL,
			dHCPCSModifier VARCHAR(38) NULL,
			dHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			mHCPCSCode VARCHAR(10) NULL,
			mHCPCSModifier VARCHAR(38) NULL,
			mHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			mcalHCPCSCode VARCHAR(10) NULL,
			mcalHCPCSModifier VARCHAR(38) NULL,
			mcalHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			dUBCode VARCHAR(10) NULL,
			dUBEffectiveDate NUMERIC(8, 0) NULL,
			mUBCode VARCHAR(10) NULL,
			mUBEffectiveDate NUMERIC(8, 0) NULL,
			mcalUBCode VARCHAR(10) NULL,
			mcalUBEffectiveDate NUMERIC(8, 0) NULL,
			PrimaryPrice MONEY NULL,
			Price2 MONEY NULL,
			Price3 MONEY NULL,
			Price4 MONEY NULL,
			Price5 MONEY NULL,
			PriceEffectiveDate NUMERIC(10, 0) NULL,
			PriceEffectiveDate2 NUMERIC(10, 0) NULL,
			PriceEffectiveDate3 NUMERIC(10, 0) NULL,
			PriceEffectiveDate4 NUMERIC(10, 0) NULL,
			PriceEffectiveDate5 NUMERIC(10, 0) NULL)'
		
		DECLARE @NonStarStatement0 VARCHAR(8000) 
		
		SET @NonStarStatement0 = 
		'	
		
		DECLARE @Extraction_Non_STAR TABLE
		(
			Active INT NOT NULL,
			HospID VARCHAR(10) NOT NULL,
			ChargeCode VARCHAR(20) NOT NULL,
			Department NUMERIC(10, 0) NOT NULL,
			Descrption VARCHAR(50) NOT NULL,
			AltDescription VARCHAR(50) NOT NULL,
			DateCreated DATETIME NULL,
			ServiceType VARCHAR(100) NULL,
			dHCPCSCode VARCHAR(10) NULL,
			dHCPCSModifier VARCHAR(38) NULL,
			dHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			mHCPCSCode VARCHAR(10) NULL,
			mHCPCSModifier VARCHAR(38) NULL,
			mHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			mcalHCPCSCode VARCHAR(10) NULL,
			mcalHCPCSModifier VARCHAR(38) NULL,
			mcalHCPCSEffectiveDate NUMERIC(8, 0) NULL,
			dUBCode VARCHAR(10) NULL,
			dUBEffectiveDate NUMERIC(8, 0) NULL,
			mUBCode VARCHAR(10) NULL,
			mUBEffectiveDate NUMERIC(8, 0) NULL,
			mcalUBCode VARCHAR(10) NULL,
			mcalUBEffectiveDate NUMERIC(8, 0) NULL,
			PrimaryPrice MONEY NULL,
			Price2 MONEY NULL,
			Price3 MONEY NULL,
			Price4 MONEY NULL,
			Price5 MONEY NULL,
			PriceEffectiveDate NUMERIC(10, 0) NULL,
			PriceEffectiveDate2 NUMERIC(10, 0) NULL,
			PriceEffectiveDate3 NUMERIC(10, 0) NULL,
			PriceEffectiveDate4 NUMERIC(10, 0) NULL,
			PriceEffectiveDate5 NUMERIC(10, 0) NULL)'

		DECLARE @dCPTPayor AS VARCHAR(10)
		DECLARE @mCPTPayor AS VARCHAR(10)
		DECLARE @mcCPTPayor AS VARCHAR(10)
		DECLARE @dUBPayor AS VARCHAR(10)
		DECLARE @mUBPayor AS VARCHAR(10)		
		DECLARE @mcUBPayor AS VARCHAR(10)
							
		DECLARE @HospID	VARCHAR(10)
		
		SET @Seq_Num = @Seq_Num + 1;
		SET @HospID = (SELECT DISTINCT HospID FROM dbo.Scheduled_Load WHERE Seq_Num = @Seq_Num)					
		
		-- Selecting dCPTPayor
		SET @dCPTPayor = 				
		(SELECT DISTINCT 
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS dCPTPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE (HP.IsItActive = 1 AND HP.IsItDefault = 1 AND HP.PayorType = 1)
		  AND HospID = @HospID)
		
		-- Selecting mCPTPayor
		SET @mCPTPayor = 
		(SELECT DISTINCT 
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS mCPTPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE HP.IsItActive = 1 AND HP.OSIPayorCode = 1 AND HP.PayorType = 1
		  AND HospID = @HospID)	
		
		-- Select mcCPTPayor		
		SET @mcCPTPayor = 
		(SELECT DISTINCT 
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS mcCPTPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE HP.IsItActive = 1 AND HP.OSIPayorCode = 100 AND HP.PayorType = 1
		  AND HospID = @HospID)
			
		-- Select dUBPayor
		SET @dUBPayor = 
		(SELECT DISTINCT 
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS dUBPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE HP.IsItDefault = 1 AND HP.IsItActive = 1 AND HP.PayorType = 2
		  AND HospID = @HospID)	
		
		-- Select mUBPayor
		SET @mUBPayor = 
		(SELECT DISTINCT 
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS mUBPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE HP.IsItActive = 1 AND HP.OSIPayorCode = 1 AND HP.PayorType = 2
		  AND HospID = @HospID)	
				
		-- Select mcUBPayor
		SET @mcUBPayor = 
		(SELECT DISTINCT
		CASE WHEN HospPayorID IS NULL
		THEN '0' ELSE HospPayorID END AS mcUBPayor 
		FROM PRODDB1.OSIDB.dbo.osit_HospPayorList HP
		WHERE HP.IsItActive = 1 AND HP.OSIPayorCode = 100 AND HP.PayorType = 2
		  AND HospID = @HospID)							 
		
		IF @dCPTPayor IS NULL BEGIN SET @dCPTPayor = '0' END
		IF @mCPTPayor IS NULL BEGIN SET @mCPTPayor = '0' END
		IF @mcCPTPayor IS NULL BEGIN SET @mcCPTPayor = '0' END
		IF @dUBPayor IS NULL BEGIN SET @dUBPayor = '0' END
		IF @mUBPayor IS NULL BEGIN SET @mUBPayor = '0' END
		IF @mcUBPayor IS NULL BEGIN SET @mcUBPayor = '0' END
																		
		SELECT DISTINCT 
		HI.HospID, HI.CDMID, DL.MachineName, DL.DatabaseName,
		CASE HI.CDMSystemName WHEN 'STAR' THEN 1 ELSE 0 END AS IsItSTAR
		INTO dbo.Scheduled_Load_Detail 							
		FROM dbo.Scheduled_Load SL 
		INNER JOIN PRODDB1.OSIDB.dbo.osit_HospInfo HI ON SL.HospID = HI.HospID
		INNER JOIN PRODDB1.OSIDB.dbo.sect_AppConcurrencyInfo AI ON HI.CDMID = AI.CDMID
		INNER JOIN PRODDB1.OSIDB.dbo.osit_DBLocation DL ON AI.DBLocId = DL.DBLocId
		WHERE HI.HospID IN (SELECT DISTINCT HospID FROM dbo.Scheduled_Load)
		  AND SL.Seq_Num = @Seq_Num AND DL.MachineName NOT LIKE '%None%'
		  AND AI.Productareaid = 74		  
		  
		-- SELECT * FROM #Scheduled_Load_Detail
		-- DROP TABLE #Scheduled_Load_Detail
		
		DECLARE @ServerName AS VARCHAR(25)
		DECLARE @DBName AS VARCHAR(25)
		DECLARE @CDMID AS VARCHAR(10)	
		
		SELECT @ServerName = (SELECT DISTINCT MachineName FROM dbo.Scheduled_Load_Detail)
		SELECT @DBName = (SELECT DISTINCT DatabaseName FROM dbo.Scheduled_Load_Detail)
		SELECT @CDMID = (SELECT DISTINCT CDMID FROM dbo.Scheduled_Load_Detail)
		
		
		DECLARE @StarStatement1 VARCHAR(8000)
		DECLARE @StarStatement2 VARCHAR(8000)
		DECLARE @StarStatement3 VARCHAR(8000)
		DECLARE @StarStatement4 VARCHAR(8000)
		DECLARE @StarStatement5 VARCHAR(8000)
		
		SET @StarStatement1 =
			'
			
			INSERT INTO @Extraction_STAR   
			(FIMActive, FIMChargeCode, FIMDepartment, FIMDescrption, FIMDateCreated, SIMActive, SIMChargeCode, SIMDepartment, 
			SIMDescrption, SIMDateCreated, ServiceType, dHCPCSCode, dHCPCSModifier, dHCPCSEffectiveDate, 
			mHCPCSCode, mHCPCSModifier, mHCPCSEffectiveDate, mcalHCPCSCode, mcalHCPCSModifier, mcalHCPCSEffectiveDate, 
			dUBCode, dUBEffectiveDate, mUBCode, mUBEffectiveDate, mcalUBCode, mcalUBEffectiveDate, 
			PrimaryPrice, Price2, Price3, Price4, Price5, 
			PriceEffectiveDate, PriceEffectiveDate2, PriceEffectiveDate3, PriceEffectiveDate4, PriceEffectiveDate5) '
		SET @StarStatement2 = 
			'
			
			SELECT DISTINCT
			MAX(HI.HospID) AS HospID, MAX(CDM.IsItActive) AS FIMActive, CDM.ChargeCode AS FIMChargeCode,
			O.DeptNum AS FIMDepartment,	CO.PrimaryDesc AS FIMDescription, CO.CreationDate AS FIMDateCreated,
			MAX(SIM.IsItActive) AS SIMActive, MAX(SIM.SIMCode) AS SIMChargeCode, MAX(SIM.SIMDeptID) AS SIMDepartment,
			MAX(SIM.SIMDesc) AS SIMDescription, MAX(AF.Field1) AS ServiceType, MAX(dCPT.HCPCSCode) AS dHCPCSCode,
			MAX(dCPT.Modifier) AS dHCPCSModifier, MAX(dCPT.HCPCSEffDate) AS dHCPCSEffectiveDate, MAX(mCPT.HCPCSCode) AS mHCPCSCode,
			MAX(mCPT.Modifier) AS mHCPCSModifier, MAX(mCPT.HCPCSEffDate) AS mHCPCSEffectiveDate, 
			MAX(mcCPT.HCPCSCode) AS mcalHCPCSCode, MAX(mcCPT.Modifier) AS mcalHCPCSModifier, MAX(mcCPT.HCPCSEffDate) AS mcalHCPCSEffectiveDate,
			MAX(dUB.UBCode) AS dUBCode, MAX(dUB.UBEffDate) AS dUBEffectiveDate, MAX(mUB.UBCode) AS mUBCode,	
			MAX(mUB.UBEffDate) AS mUBEffectiveDate, MAX(mcUB.UBCode) AS mcalUBCode, MAX(mcUB.UBEffDate) AS mcalUBEffectiveDate,
			MAX(PP.Price) AS PrimaryPrice, MAX(P2.Price) AS Price2, MAX(P3.Price) AS Price3, MAX(P4.Price) AS Price4, MAX(P5.Price) AS Price5,
			MAX(PP.PriceEffDate) AS PriceEffectiveDate,	MAX(P2.PriceEffDate) AS PriceEffectiveDate2, MAX(P3.PriceEffDate) AS PriceEffectiveDate3,
			MAX(P4.PriceEffDate) AS PriceEffectiveDate4, MAX(P5.PriceEffDate) AS PriceEffectiveDate5'
		SET @StarStatement3 =
		    '
		    FROM ' + @ServerName +  '.' + @DBName +  '.dbo.cdm_CDMMaster AS CDM
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_FIMSIMLink AS L ON L.CDMMasterID = CDM.CDMMasterID
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_SIMMaster AS SIM ON SIM.SIMMasterID = L.SIMMasterID
			LEFT JOIN	PRODDB1.OSIDB.dbo.osit_DeptInfo AS O ON O.DeptID = CDM.DeptID
			LEFT JOIN	PRODDB1.OSIDB.dbo.osit_DeptInfo AS SO ON SO.DeptID = SIM.SIMDeptID
			LEFT JOIN	PRODDB1.OSIDB.dbo.osit_HospInfo AS HI ON HI.CDMID = CDM.CDMID
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_CDMCommon AS CO ON CO.CDMMasterID = CDM.CDMMasterID
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_AFData AS AF ON AF.CDMMasterID = CDM.CDMMasterID
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS dCPT ON dCPT.CDMMasterID = CDM.CDMMasterID AND dCPT.HospPayorID = '+ @dCPTPayor + '
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS mCPT ON mCPT.CDMMasterID = CDM.CDMMasterID AND mCPT.HospPayorID = '+ @mCPTPayor + '
	 		LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS mcCPT ON mcCPT.CDMMasterID = CDM.CDMMasterID AND mcCPT.HospPayorID = '+ @mcCPTPayor + '
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS dUB ON dUB.CDMMasterID = CDM.CDMMasterID AND dUB.HospPayorID = '+ @dUBPayor + '
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS mUB ON mUB.CDMMasterID = CDM.CDMMasterID AND mUB.HospPayorID = '+ @mUBPayor + '
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS mcUB ON mcUB.CDMMasterID = CDM.CDMMasterID AND mcUB.HospPayorID = '+ @mcUBPayor + ' '
		SET @StarStatement4 =
			'
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS PP ON PP.CDMMasterID = CDM.CDMMasterID AND PP.PriceCode = 1
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P2 ON P2.CDMMasterID = CDM.CDMMasterID AND P2.PriceCode = 2
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P3 ON P3.CDMMasterID = CDM.CDMMasterID AND P3.PriceCode = 3
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P4 ON P4.CDMMasterID = CDM.CDMMasterID AND P4.PriceCode = 4
			LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P5 ON P5.CDMMasterID = CDM.CDMMasterID AND P5.PriceCode = 5
			WHERE CDM.CDMID in (' + @CDMID + ') AND CDM.ChargeCode NOT IN ('''') AND O.DeptNum IS NOT NOT NULL AND CDM.IsPreAdd = 0
			GROUP BY CDM.ChargeCode, CO.CreationDate, CO.PrimaryDesc, O.DeptNum
			ORDER BY FIMActive, SIMActive, CDM.ChargeCode ASC ' 
			
		SET @StarStatement5 = 
		'
		
		SELECT * FROM @Extraction_STAR'			
			
		DECLARE @NonStarStatement1 VARCHAR(8000)
		DECLARE @NonStarStatement2 VARCHAR(8000)
		DECLARE @NonStarStatement3 VARCHAR(8000)
		DECLARE @NonStarStatement4 VARCHAR(8000)
		DECLARE @NonStarStatement5 VARCHAR(8000)
		
		SET @NonStarStatement1 = 
			'
			
			INSERT INTO @Extraction_Non_STAR
			(Active, HospID, ChargeCode, Department, Descrption, AltDescription, DateCreated, ServiceType, 
			dHCPCSCode, dHCPCSModifier, dHCPCSEffectiveDate, mHCPCSCode, mHCPCSModifier, mHCPCSEffectiveDate, 
			mcalHCPCSCode, mcalHCPCSModifier, mcalHCPCSEffectiveDate, dUBCode, dUBEffectiveDate, mUBCode, 
			mUBEffectiveDate, mcalUBCode, mcalUBEffectiveDate, PrimaryPrice, Price2, Price3, Price4, Price5, 
			PriceEffectiveDate, PriceEffectiveDate2, PriceEffectiveDate3, PriceEffectiveDate4, PriceEffectiveDate5) '
			
		SET @NonStarStatement2 =
		    '
		    
			 SELECT DISTINCT
			 MAX(CDM.IsItActive) AS Active, MAX(HI.HospID) AS HospID, CDM.ChargeCode AS ChargeCode, 
		     O.DeptNum AS Department, CO.PrimaryDesc AS Description, MAX(CO.TechnicalDesc) AS AltDescription,
			 CO.CreationDate AS DateCreated,	MAX(AF.Field1) AS ServiceType, MAX(dCPT.HCPCSCode) AS dHCPCSCode,
			 MAX(dCPT.Modifier) AS dHCPCSModifier, MAX(dCPT.HCPCSEffDate) AS dHCPCSEffectiveDate,
			 MAX(mCPT.HCPCSCode) AS mHCPCSCode, MAX(mCPT.Modifier) AS mHCPCSModifier,
			 MAX(mCPT.HCPCSEffDate) AS mHCPCSEffectiveDate, MAX(mcCPT.HCPCSCode) AS mcalHCPCSCode,
			 MAX(mcCPT.Modifier) AS mcalHCPCSModifier, MAX(mcCPT.HCPCSEffDate) AS mcalHCPCSEffectiveDate,
			 MAX(dUB.UBCode) AS dUBCode, MAX(dUB.UBEffDate) AS dUBEffectiveDate, MAX(mUB.UBCode) AS mUBCode,
			 MAX(mUB.UBEffDate) AS mUBEffectiveDate, MAX(mcUB.UBCode) AS mcalUBCode,	
			 MAX(mcUB.UBEffDate) AS mcalUBEffectiveDate, MAX(PP.Price) AS PrimaryPrice,
			 MAX(P2.Price) AS Price2, MAX(P3.Price) AS Price3, MAX(P4.Price) AS Price4, MAX(P5.Price) AS Price5,
			 MAX(PP.PriceEffDate) AS PriceEffectiveDate, MAX(P2.PriceEffDate) AS PriceEffectiveDate2,
			 MAX(P3.PriceEffDate) AS PriceEffectiveDate3, MAX(P4.PriceEffDate) AS PriceEffectiveDate4,
			 MAX(P5.PriceEffDate) AS PriceEffectiveDate5' 
		SET @NonStarStatement3 = 
			'
			 FROM '	+ @ServerName +  '.' + @DBName +  '.dbo.cdm_CDMMaster AS CDM
		 	 LEFT JOIN	PRODDB1.OSIDB.dbo.osit_DeptInfo AS O ON O.DeptID = CDM.DeptID
			 LEFT JOIN	PRODDB1.OSIDB.dbo.osit_HospInfo AS HI ON HI.CDMID = CDM.CDMID
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_CDMCommon AS CO ON CO.CDMMasterID = CDM.CDMMasterID
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_AFData AS AF ON AF.CDMMasterID = CDM.CDMMasterID
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS dCPT ON dCPT.CDMMasterID = CDM.CDMMasterID AND dCPT.HospPayorID = ' + @dCPTPayor + '
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS mCPT ON mCPT.CDMMasterID = CDM.CDMMasterID AND mCPT.HospPayorID = ' + @mCPTPayor + '
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_HCPCS AS mcCPT ON mcCPT.CDMMasterID = CDM.CDMMasterID AND mcCPT.HospPayorID = '+ @mcCPTPayor + '
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS dUB ON dUB.CDMMasterID = CDM.CDMMasterID AND dUB.HospPayorID = ' + @dUBPayor + '
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS mUB ON mUB.CDMMasterID = CDM.CDMMasterID AND mUB.HospPayorID = ' + @mUBPayor + '
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_UB AS mcUB ON mcUB.CDMMasterID = CDM.CDMMasterID AND mcUB.HospPayorID = '+ @mcUBPayor + ' '
		SET @NonStarStatement4 =  
			'
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS PP ON PP.CDMMasterID = CDM.CDMMasterID AND PP.PriceCode = 1
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P2 ON P2.CDMMasterID = CDM.CDMMasterID AND P2.PriceCode = 2
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P3 ON P3.CDMMasterID = CDM.CDMMasterID AND P3.PriceCode = 3
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P4 ON P4.CDMMasterID = CDM.CDMMasterID AND P4.PriceCode = 4
			 LEFT JOIN '+ @ServerName +  '.' + @DBName +  '.dbo.cdm_Price AS P5 ON P5.CDMMasterID = CDM.CDMMasterID AND P5.PriceCode = 5 \
			 
			 WHERE CDM.CDMID in (' + @CDMID + ') AND CDM.ChargeCode NOT IN ('''') AND O.DeptNum IS NOT NULL AND CDM.IsPreAdd = 0
			 GROUP BY CDM.ChargeCode, CO.CreationDate, CO.PrimaryDesc, O.DeptNum
			 ORDER BY Active, ChargeCode ASC '				
			 
		SET @NonStarStatement5 = 
		'
		
		SELECT * FROM @Extraction_Non_STAR'


        ------------------------------------------------------------------------------------
		
		IF (SELECT IsItStar FROM dbo.Scheduled_Load_Detail) = 1
			BEGIN  
				EXEC
					(@StarStatement0 + @StarStatement1 + @StarStatement2 + 
					 @StarStatement3 + @StarStatement4 + @StarStatement5) 
				PRINT 
					(@StarStatement0 + @StarStatement1 + @StarStatement2 + 
					 @StarStatement3 + @StarStatement4 + @StarStatement5) 
			END
			
		ELSE
			BEGIN 
				EXEC
					(@NonStarStatement0 + @NonStarStatement1 + @NonStarStatement2 + 
					 @NonStarStatement3 + @NonStarStatement4 + @NonStarStatement5)  
				PRINT 
					(@NonStarStatement0 + @NonStarStatement1 + @NonStarStatement2 + 
					 @NonStarStatement3 + @NonStarStatement4 + @NonStarStatement5)  
			END
	END

Open in new window

0
kmc10314Author Commented:
Hello, acperkins.
I thought I took care of the problem, but I'm still running with the issue.
I have posted the query that I'm using to create Data Flow Task
Can you please help me?
0
NetminderCommented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for kmc10314's comment #a39568755

for the following reason:

Starting closing for Asker.

Netminder
Senior Admin
0
DcpKingCommented:
Automated closure triggered too soon - author claimed to have found solution and then realised that (s)he was wrong.
0
DcpKingCommented:
Hi KMC!
You posted a chunk of code. I'm assuming that, for use in the SSIS, you're going to wrap it in a stored procedure and use that as a data source You could "simply" put the code into a variable in the SSIS system and use the variable as a datasource, but, to be honest, with this much code it won't be a simple matter!

Right now you have two chains of logic in this code - Star and Non-Star - and you spend most of your code setting up your code. Right at the end you make a decision:

SELECT IsItStar FROM dbo.Scheduled_Load_Detail

I would suggest that you create a single small stored procedure that makes this decision and then executes one or the other of two stored procedures - one for Star and one for non-Star calculations. This will let you get rid of the dynamic SQL and your results will almost certainly run faster, not just because of it being static code, but also because you'll be able to write the results into temp tables (#-type tables) with indexes. Your main, small stored procedure can then return a common result set from either one of the two temp tables.

I'll post next with a cut at what your code will look like, but I don't have your base tables, so you'll have to test it!

hth

Mike
0
DcpKingCommented:
Having worked through and refactored your code I've re-posted it attached below.

Your problem, I believe, is that you have 33 fields per record in your non-star output, but 37 in your star output.

You need to change your code so that the two results are structurally the same - you can do this most easily by adding fields to the non-star calculation that never get used later on.

hth

Mike
MainStar.sql
CreateTempStarTables.sql
CalculatePayorData.sql
StarProc.sql
NonStarProc.sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.