I am using SQL stored procedure in my SSIS package , when I run the Stored procedure in SQL server managements studio I am getting 2 rows as results. But when I execute my SSIS package I am getting 10 rows as results (Duplicfate rows) ..
How can I avoid this ? Can any one please help me on this?
Thank you.
can you post your proc here?
AS
BEGIN
SET NOCOUNT ON;
DECLARE
--name of previous month
@MonthName VARCHAR(MAX) = DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),
--first day of previous month
@BeginDt DATETIME = DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) ,
--end of last day of last month
@EndDt DATETIME = DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))
INSERT INTO PhaLabInrGlucose (Facility,MonthTitle,DataElement,Number)
select 'ABC' as Organization,
@MonthName,
--DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 3, 0)) as MonthTitle,
'BSGLU_Numerator' as DataElement,
(ResultRW + ResultRW1) As Number from
(select
COUNT(LST.ResultRW) as ResultRW
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
ISNUMERIC(LST.ResultRW)=1 and
CAST(LST.ResultRW As Integer) < '40' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt ) A
cross join (select
COUNT(LST.ResultRW) as ResultRW1
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
--ISNUMERIC(LST.ResultRW)=1 and
LST.ResultRW = '< 20' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt) B
INSERT INTO PhaLabInrGlucose (Facility,MonthTitle,DataElement,Number)
select 'ABC' as Organization,
@MonthName,
--DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 3, 0)) as MonthTitle,
'BSGLU_Denominator' as DataElement,
COUNT(LST.ResultRW) as Number
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
--ISNUMERIC(LST.ResultRW)=1 and
--CAST(LST.ResultRW As Integer) < '40' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt
select Facility ,MonthTitle ,DataElement ,Number ,ID , LoadDate from PhaLabInrGlucose where MonthTitle = @MonthName
SET NOCOUNT OFF
END
Can anyone please help me on this please?
Are you running both SSMS and the SSIS package from the same machine (e.g. your workstation)?
Then let's think about this:
Both SSMS and DTEXEC (which runs packages) open a connection to the same server.
Both ultimately send the same EXEC command to run the stored procedure (easy to verify, just fire up profiler and run both ways)
Yet you say results differ.
Profiler will tell you that too. Look at the row counts returned from both executions of your proc. Both should be the same
Yes I am running SSMS and SSIS from my local machine... May be because of that we are getting duplicate rows..
How can I avoid this?
Please help me on this?
without seeing your query..
SELECT DISTINCT ...