kmc10314
asked on
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!
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!
Anthony Perkins
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.
ASKER
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
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
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.
Sure it can, but glad you found a workaround.
>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.
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.
ASKER
Acperkins,
Here is the code
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
ENDASKER
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?
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?
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
Accepted answer: 0 points for kmc10314's comment #a39568755
for the following reason:
Starting closing for Asker.
Netminder
Senior Admin
Automated closure triggered too soon - author claimed to have found solution and then realised that (s)he was wrong.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.