June 10, 2012 at 10:03 pm
I am working in SQL 2008 R2 and am fairly new to T-SQL. I am taking over a weekly process that hoping can be streamlined a bit. Can someone tell me how I might kick off a process that brings some data files into a SQL database and then runs some reports summarizing the results? I am thinking it will involve running an SSIS package and then some reports in SSRS. Is there some way to combine this process? Can I run a report from within SSIS or create a job that runs an SSIS package and then runs a set of reports in SSRS? Or is there another way to do this that I am not aware of? Any help would be greatly appreciated.
TIA,
Jon
June 11, 2012 at 12:37 am
It depends what you want to see in the summary. If just a small table with number of rows transferred, I would just generate a html table with results and email it to a distribution list. You could do that in SSIS or tsql with sp_db_sendmail. Although some shops frown on using sql mail, in which case, use an SSIS task or create an SSRS report and have the user "subscribe" to the report which can be set to run daily. Probably better with ssrs for easier expandability.
June 11, 2012 at 3:19 am
I would vote for using SSIS to process the data , in conjuction with SSRS for letting users subscribe to the reports.
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
June 11, 2012 at 10:46 pm
Thanks for your suggestions. I was not clear but the reports I have in mind are just for me as a way of reviewing the results so as to make sure that the counts by various perspectives look reasonable.
June 12, 2012 at 10:39 am
If its more of an import status/validation I would go for the suggestion above by foxxo to use sp_send_dbmail.
Generally I use this pattern within a stored procedure.
-create an entry in a sessions table
-log the initiation of the import process
-use dtexec to call the SSIS import package
-check and log the success/failure of the package
-analyze the imported data and log any counts, etc
-use sp_send_dbmail to send the results of the logs table for the current session using html
June 12, 2012 at 2:25 pm
Chrissy321,
This sounds just like what I have in mind. Being a newbie I have to admit I don't understand a lot of what you said (:unsure:
This is appears to be enough info to get me pointed in the right direction and this probably isn't the right forum for you to address all my questions but could you clarify just a few of them for me?
When you mention a sessions table are you referring to a system table(s) or is this something you create to summarize results? Do you have an example?
Are you envisioning all this being done within a (scheduled) stored procedure? (e.g. exec dtexec and using sp_send_dbmail
Where would I learn how to generate the results of the longs tables using html?
If I am asking too many questions just answer what you feel up to. What you have given me is already a big help.
Thank you all,
Jon
June 12, 2012 at 3:36 pm
The sessions I create and initialize every time the procedure runs. It facilitates logging if you want to record the history of the process.
I do tend to do this all in one proc, less moving parts.
CREATE TABLE [dbo].[ProcessingSessions](
[SessionID] [int] IDENTITY(1,1) NOT NULL,
[InitiationDateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Logs](
[Message] [varchar](200) NOT NULL,
[MessageType] [varchar](14) NOT NULL,
[Action] [varchar](120) NOT NULL,
[CallingProcedure] [varchar](50) NOT NULL,
[SuccessFlag] [char](1) NOT NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [varchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [varchar](4000) NULL,
[DateTimeStamp] [datetime] NOT NULL,
[SessionID] [int] NOT NULL,
[SecurityID] [int] NULL,
[Sort] [int] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Logs] ADD CONSTRAINT [DF_Logs_DateTimeStamp] DEFAULT (getdate()) FOR [DateTimeStamp]
GO
DECLARE @SessionID int --A unique identifier created everytime the procedure has run
DECLARE @PackageString varchar(400)--The command that will execute the package
DECLARE @PackagStatus int--The success/failure status of the package
DECLARE @tableHTML NVARCHAR(MAX) --The container for the email body
--Start processing
--So here I initiate the session and log that initiation.
INSERT INTO dbo.ProcessingSessions (InitiationDateTime) VALUES (getdate())
--Retrieve the most recent SessionID using the IDENT_CURRENT function; Set the @SessionID variable to the SessionID created
SET @SessionID= (SELECT IDENT_CURRENT('dbo.ProcessingSessions'))
PRINT @SessionID
--Insert a log message that a session has initiated
INSERT INTO dbo.Logs (Message,MessageType,Action,CallingProcedure,SuccessFlag,ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,SessionID,SecurityID,Sort)
SELECT 'Processing session ' + CONVERT(varchar(8),@SessionID) + ' has initiated.','Info', 'Session initiation','uspYourProc','Y',NULL,NULL,NULL,NULL,NULL,NULL,@SessionID,NULL,1
--Execute the package and log the results
SET @PackageString = 'dtexec /sq MyPackage/ser SERVER1
EXEC @PackagStatus = xp_cmdshell @PackageString
IF @PackagStatus <> 0
BEGIN
INSERT INTO dbo.Logs (Message,MessageType,Action,CallingProcedure,SuccessFlag,ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,SessionID,SecurityID,Sort)
SELECT ' Export Package associated with sessionID: ' + CONVERT(varchar(8),@SessionID) + ' has failed.','Failure', 'Package Status','uspProcessing','N',NULL,NULL,NULL,NULL,NULL,NULL,@SessionID,NULL,5
END
ELSE
BEGIN
INSERT INTO dbo.Logs (Message,MessageType,Action,CallingProcedure,SuccessFlag,ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,SessionID,SecurityID,Sort)
SELECT 'Export Package associated with sessionID:' + CONVERT(varchar(8),@SessionID) + ' has succeeded.','Success', 'Package Status','uspProcessing','Y',NULL,NULL,NULL,NULL,NULL,NULL,@SessionID,NULL,5
END
--Send an email if there is a failure in the logs (or you can insert your counts in the logs and always generate an email)
IF EXISTS
(
SELECT Message FROM dbo.Logs WHERE SessionID = @SessionID AND (SuccessFlag = 'N' OR MessageType = 'Failure' OR MessageType = 'Error')
)
BEGIN
SET @tableHTML =
N'<H3> Processing Error</H3>' +
N'<table border="1">' +
N'<th>Message</th>' +
'<th>CallingProcedure</th>' +
'<th>ErrorMessage</th>' +
'<th>ErrorLine</th>' +
'</tr>' +
CAST ( ( SELECT
td = COALESCE(Message,''), '',
td = COALESCE(CallingProcedure,''), '',
td = COALESCE(ErrorMessage,''), '',
td = COALESCE(ErrorLine,''), ''
FROM dbo.Logs
WHERE
SessionID = SessionID AND
(SuccessFlag = 'N' OR
MessageType = 'Failure' OR
MessageType = 'Error')
ORDER BY Message
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'youremail@yourdomain.com',
@copy_recipients = 'youremail@yourdomain.com',
@profile_name = 'Yourprofile',
@subject = 'Processing Error',
@body = @tableHTML,
@body_format = 'HTML'
END
Also you can use TRY and CATCH to catch any errors. It's nice to get an email with the cause of your failure, obviously facilitates troubleshooting.
This may be overkill for a quick email status check of your job but if you are going to do a lot of this type of stuff I would invest the time in your first project knowing you can quickly adapt to any new import/exports that come along.
Post back if you have any questions.
June 15, 2012 at 8:54 am
Geesh, I realize how much I have to learn after seeing your response! The kind of reporting I was thinking of was more along the lines of after a process runs successfully and I want to see if the new data is reasonable (i.e. did the right file get used and was the data clean). For example I want to look at what the last posting date of the data was, how many department numbers have null department names, what the total charges by month are...that sort of thing. What you suggest is something I hadn't even considered but I will try to add that as well. As I type this I am thinking I can probably insert the statistics I describe into a table and email it to myself. Does that seem reasonable?
June 15, 2012 at 9:30 am
I think what you are doing is completely reasonable, you can certainly insert your relevant statistics into a table and email them yourself. I didn't meant to overwhelm you, just to gives you some ideas on monitoring and notification if you are going to develop automation.
June 15, 2012 at 11:00 am
That's ok, I would rather be a little overwhelmed and be learning than not doing a proper job. Thank you for your suggestions. I appreciate it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply