August 15, 2015 at 1:06 am
Hi ,
In My SSIS Package I am using errorlog table for capturing the error in table.
Have a table called SSISErrorLog
Create an Event Handler for OnError (click on the event handler tab, should be self-explanatory from there)
Create an Execute SQL task:
I also create a variable called 'sMessage
CREATE TABLE [SSISErrorLog]
([PackageName] VARCHAR(100)
,[SourceName] VARCHAR(100)
,[SourceDescription] VARCHAR(5000)
,[Event] VARCHAR(100)
,[EventDate] DATETIME
,[ExecutedBy] VARCHAR(100)
)
INSERT INTO [BS_PROD].[dbo].[SSISErrorLog]
([PackageName]
,[SourceName]
,[SourceDescription]
,[Event]
,[EventDate]
,[ExecutedBy])
VALUES
(?
,?
,?
,'Failure of Monthly PV Data Load'
,getdate()
,?)
In attached screenshot you can see that I am getting error log in multiple rows. My requirement is that I should get the above result in below format
Packagename=Package2
Sourcename=CG
SourceDescroption=ExecuteSQLTask
Event=Filure
Eventdate=date
Execuited by=username
Packagename=Pckage2
Sourcename=CG
SourceDescroption=ExecuteSQLTask
Event=Filure
Eventdate=date
Execuited by=username
I want to pass above format using script task to sMessage variable and I will call the In My SSIS Package I am using errorlog table for capturing the error in table.
Have a table called SSISErrorLog
Create an Event Handler for OnError (click on the event handler tab, should be self-explanatory from there)
Create an Execute SQL task:
I also create a variable called 'sMessage
CREATE TABLE [SSISErrorLog]
([PackageName] VARCHAR(100)
,[SourceName] VARCHAR(100)
,[SourceDescription] VARCHAR(5000)
,[Event] VARCHAR(100)
,[EventDate] DATETIME
,[ExecutedBy] VARCHAR(100)
)
INSERT INTO [BS_PROD].[dbo].[SSISErrorLog]
([PackageName]
,[SourceName]
,[SourceDescription]
,[Event]
,[EventDate]
,[ExecutedBy])
VALUES
(?
,?
,?
,'Failure of Monthly PV Data Load'
,getdate()
,?)
In attached screenshot you can see that I am getting error log in multiple rows. My requirement is that I should get the above result in below format
Packagename=Package2
Sourcename=CG
SourceDescroption=ExecuteSQLTask
Event=Filure
Eventdate=date
Execuited by=username
Packagename=Pckage2
Sourcename=CG
SourceDescroption=ExecuteSQLTask
Event=Filure
Eventdate=date
Execuited by=username
Can you help me to get code for script task .
I want to pass above format using script task to sMessage variable and I will call the sMessage variable using Send mail task to send error detail to user
Regards,
Vipin Jha
August 15, 2015 at 8:09 am
these articles provide the basics for writing SSIS Script Task -- C#.Net & VB.Net
includes executing sql statement with multiple results
http://qa.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/76439/
http://qa.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/76438/
August 15, 2015 at 8:36 am
If all you are going to do is pull the dataset from SQL Server and then populate a variable, to turn around and just email it to a user, why make it more complicated by using a Script Task? You can just as easily do this in T-SQL, formatting the output in HTML or plain text and email it using sp_send_dbmail. Just execute that code in a Execute SQL Task. I don't see the advantage of doing this with a Script Task to just send data to a user, even with multiple data sets.
If you are dead set on using a Script Task, you could simply use an Execute SQL Task to pull the dataset from your error log table and populate that data into an object variable. You then use that in your Script Task to reformat using C# into a multi-line string that you could then populate another variable with to pass on to your email task.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply