November 23, 2011 at 10:16 am
Hi,
As Part of reports I wrote a SQL query to pull the data. I am running in sql and copying the output from server and pasting in excel(local machine). But now I want to automate this by creating a SSIS package and schedule to run as a job.
I tried to add the sql query in source , but throwing error. Appreciate your help.
here is the query:
DECLARE @accountID INT
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SET @accountID= 2
SET @FromDate= '2010/01/01'
SET @ToDate= '2011/11/30'
Declare @mkey varbinary(max)
set @mkey = 0x000000000000000000000000000000000000000000000000000000
CREATE TABLE #temp_patients
(
PatientID BIGINT,
PatientDisplayID nvarchar(100),
StatusName nvarchar(100),
LastLoginDateTime datetime,
SugarEntryCount BIGINT,
CarbsEntryCount BIGINT,
MedsEntryCount BIGINT,
BPEntryCount BIGINT
)
INSERT INTO #temp_patients(PatientID, PatientDisplayID, StatusName, LastLoginDateTime)
(
SELECT T1.LoginID, dbo.FN_Decrypt(@mkey, T1.LoginSKey, T2.AccountPatientDisplayID), T4.StatusName, T1.LastLoginDateTime
FROM [Login] T1
INNER JOIN [Patient] T2 ON T1.LoginID = T2.PatientID AND T1.StatusID IN (1,2) -- Active, Idle
INNER JOIN [status] T4 ON T1.StatusID= T4.StatusID
INNER JOIN [PatientAccount] T3 ON T2.PatientID = T3.PatientID AND T3.ACCOUNTID = @accountID
)
UPDATE #temp_patients
SET SugarEntryCount = (
Select count(ElogID) from
(
SELECT ELOGBGEntry.ElogID, dbo.ELogBGEntry.PatientID , dbo.ELogBGEntry.BGEntryTime, ROW_NUMBER() OVER
(PARTITION BY dbo.Elog.ElogID, dbo.ELog.PatientID ORDER BY dbo.ELogBGEntry.BGEntryTime DESC) AS "RANK" from ELOG
inner JOIN ELOGBGEntry ON ELOG.ELOGID = ELOGBGEntry.ELOGID
AND dbo.ELog.PatientID = dbo.ELogBGEntry.PatientID
AND ELOG.RetestIndicator =0 AND [ELogBGEntry].PatientID = #temp_patients.PatientID
AND (CAST(CONVERT(CHAR(10), BGEntryTime, 126) AS SMALLDATETIME) BETWEEN @FromDate AND @ToDate)
) BGCount Where Rank = 1
GROUP BY PatientID
)
UPDATE #temp_patients
SET CarbsEntryCount = (
select count(ElogID) from [ELogCarbsEntry]
WHERE [ELogCarbsEntry].PatientID = #temp_patients.PatientID AND
(CAST(CONVERT(CHAR(10), CarbsEntryTime, 126) AS SMALLDATETIME) BETWEEN @FromDate AND @ToDate)
Group By PatientID)
UPDATE #temp_patients
SET MedsEntryCount = (
select count(ElogID) from ELogMedEntry
WHERE ELogMedEntry.PatientID = #temp_patients.PatientIDAND
(CAST(CONVERT(CHAR(10), MedEntryTime, 126) AS SMALLDATETIME) BETWEEN @FromDate AND @ToDate)
Group By PatientID
)
UPDATE #temp_patients
SET BPEntryCount = (
select count(PatientBPTestResultID) from PatientBPTestResult
WHERE PatientBPTestResult.PatientID = #temp_patients.PatientIDAND PatientBPTestResultStatus = 1 AND
(CAST(CONVERT(CHAR(10), PatientBPEntryTime, 126) AS SMALLDATETIME) BETWEEN @FromDate AND @ToDate)
Group By PatientID
)
Select PatientDisplayID as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',
ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count',
ISNULL(BPEntryCount,0) as 'BP Count'
FROM #temp_patients
DROP TABLE #temp_patients
GO
Error:
===================================
Error at Data Flow Task [OLE DB Source [423]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_Decrypt", or the name is ambiguous.".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.SaveConnectionPage(ConnectionAttributesEventArgs args)
November 23, 2011 at 11:11 am
Sorry...need to rephrase.
Check the query in management studio first. Does it work there? If it does, are you pointing to the right database in your task? It seems to have an issue finding the function you are calling...
Secondly and as far as I know, there are some issues that pop up when creating temp tables in execute SQL tasks. During the pre-validation phase it may fail because the temp table does not exist yet and it wants to validate the query.
You may want to consider persisting the table, use a table variable (depending on how many rows) or create a stored procedure with the code and rather execute that.
November 23, 2011 at 11:25 am
The problem you are experiencing is related to the temp tables and the pre-validate. I would dump this data into a persisted table if this is something you are going to do on a regular basis. SSIS will play more nicely.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply