Scribd
Upload
352 views

Custom Logging in SQL Server Integration Services SSIS

The document discusses implementing custom logging in SQL Server Integration Services (SSIS) packages to capture information about package execution including start and end times, success status, and row counts extracted and loaded. It proposes a database schema with tables to log package, extract, and load activity and stored procedures to populate the tables. A sample SSIS package is described that extracts customer data from AdventureWorks, loads a staging table, and logs the activity to the custom tables using the stored procedures.

Uploaded by

jokoarie
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
352 views

Custom Logging in SQL Server Integration Services SSIS

The document discusses implementing custom logging in SQL Server Integration Services (SSIS) packages to capture information about package execution including start and end times, success status, and row counts extracted and loaded. It proposes a database schema with tables to log package, extract, and load activity and stored procedures to populate the tables. A sample SSIS package is described that extracts customer data from AdventureWorks, loads a staging table, and logs the activity to the custom tables using the stored procedures.

Uploaded by

jokoarie
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

5/16/2016

CustomLogginginSQLServerIntegrationServicesSSIS

CustomLogginginSQLServerIntegrationServicesSSIS
Problem
WehavequiteafewSSISpackagesthatwerunonaregularbasistoextractdatafromourOLTPsystemsandtransform/loadintoourdata
warehouse.WewouldliketocapturesomebasicinformationabouttheseSSISpackagessuchaswhatpackageswererun,when,howlongdidthey
taketorun,andhowmanyrowswereextractedandloaded.WehavelookedintothebuiltinloggingthatcomeswithSSISbutwejustdon'tseea
straightforwardwaytogetrowcounts.Arewemissingsomething?
Solution
SSISprovidesaflexibleloggingmechanismthatistightlyintegratedwiththevariouseventsraisedduringpackageexecution.However,theissuethat
youraiseisaccurateandthebestapproachistousethebuiltinSSISloggingwhereappropriateandaugmentitwithyourowncustomlogging.A
simple,straightforwardcustomloggingschemacanprovidetheinformationyouneed.Let'stakealookatasampleschemathatcanbeusedto
capturethebasiclogginginformationthatyouhaverequested:
SampleSSISLoggingDataModel

Mainpointsabouttheaboveschema:
Allofthetablesareintheetlschemait'sagoodideatoisolatethesekindsoftablesfromothertablesinyourdatabase.
ArowisinsertedintothePackageLogtablebyeverySSISpackagethatexecutes.
ArowisinsertedintotheExtractLogwhenanSSISpackageextractsrowsfromatableintheOLTPdatabase.
ArowisinsertedintotheLoadLogtablewhenanSSISpackageinserts,updatesordeletesrowsinadimensionorfacttableinthedata
warehouse.
TheEndTimeandSuccesscolumnsareupdatedifthepackagerunstocompletion.
TheaboveschemadoesnotprovideforerrorloggingwhichisprobablybesthandledusingthebuiltinSSISloggingcapabilities.
Thepatternforpopulatingtheloggingtablesistoimplementan"Init"andan"End"storedprocedureforeachtable.Thestoredproceduresare
executedintheSSISpackageusingtheExecuteSQLtask.ThePackageLogstoredproceduresareasfollows:
stp_InitPackageLogiscalledatthebeginningoftheSSISpackagetoinsertarowintothePackageLogtablewiththeStartTimeandPackage
NameitreturnsthePackageLogID(identityvalue).ThePackageLogIDissavedinapackagevariabletoupdatetherowwhenthepackageis
done.
stp_EndPackageLogiscalledattheendoftheSSISpackagetoupdatetherowinsertedinthePackageLogwiththeEndTimeandsettheSuccess
columnto1.
PackagesthatextractdatafromatableinanOLTPsystemwillupdatetheExtractLogtableasfollows:
stp_InitExtractLogiscalledtoinsertarowintotheExtractLogtablewiththeStartTimeandOLTPTableNamethatisbeingprocessedandreturn
theExtractLogID(identityvalue).TheExtractLogIDissavedinapackagevariabletoupdatetherowwhentheextractisdone.Inadditionthe
storedprocedurereturnsthemaximumLastExtractDateTimeforthetablethisvalueisstoredinapackagevariableandusedintheDataFlowto
onlygettherowsthathavechangedsincethelastextract.
https://www.mssqltips.com/sqlservertip/1417/customlogginginsqlserverintegrationservicesssis/

1/3

5/16/2016

CustomLogginginSQLServerIntegrationServicesSSIS

stp_EndExtractLogiscalledwhentheextractiscompletetoupdatetheExtractCount(numberofrowsextracted),EndTime,LastExtractDateTime,
andSuccesscolumns.TheLastExtractDateTimecolumnisaDATETIMEtypethatissettothemaximumlastupdatedate/timeextractedfromthe
OLTPtable.TheOLTPtableneedstoprovideareliablelastupdatedate/timecolumninordertomakethiswork.Forexample,tablesinthe
AdventureWorksdatabasehaveaModifiedDatecolumnthatreflectsthelastdateandtimetherowwasupdated.
PackagesthatupdatedimensionorfacttablesinthedatawarehousewillupdatetheLoadLogtableusingthesamepatterni.e.stp_InitLoadLogand
stp_EndLoadLog.TheLoadLogtablerecordsthenumberofrowsthatwereinserted,updatedordeletedforagivendimensionorfacttableinthe
warehouse.
Nowlet'stakealookatasimpleSSISpackagethatimplementsthecustomlogging.We'llusetheAdventureWorkssampledatabaseasourOLTP
source,extractrowsfromtheSales.Customertable,andsavetherowsinastagingtable.ThefollowingistheControlFlowforthepackage:
SSISControlFlow

Nextlet'sdrillintotheDataFlow:
SSISDataFlow

TheGetUpdatedCustomerstaskselectstherowsfromtheSales.CustomertablewheretheModifiedDateisgreaterthanthemaximumModifiedDate
thelasttimethepackagewasrun.ThemaximumModifiedDateisstoredintheLastExtractDateTimecolumnintheExtractLogtableandreturnedin
thecalltothestp_InitExtractLogstoredprocedure(InitExtractLogtaskintheControlFlow).TheGetExtractRowCounttasksimplyassignsthe
numberofrowspassingthroughtoapackagevariabletherowcountispassedasaparametertothestp_EndExtractLogstoredprocedure(End
ExtractLogtaskinControlFlow)andstoredintheExtractLogtable.
NextSteps
DownloadthesampleSSISpackageanddatabasescriptshereandtryitouttoseehowyoumightcraftyourowncustomlogging.Notethatthe
sampleusestheAdventureWorkssampledatabaseandanotherdatabasecalledMSSQLTIPSbothmustbeavailableinthedefaultSQLServer
instanceoryoucanedittheConnectionManagersintheSSISpackageasappropriate.
Thesampledescribedinthistipisjustastartingpointinyourparticularcircumstancesyoumayfindadditionalinformationthatshouldbelogged.
ThecustomloggingthatyouimplementwilllikelyprovidefuturebenefitsaswellforinstanceinamajorupgradetoanexistingETLprocessthe
informationinthecustomloggingtablescanbeusedtocompareresultsbetweenthecurrentlydeployedETLprocessandyourdevelopment
environment.
CustomloggingisjustoneofanumberofthingsthatshouldbedefinedearlyinyourdevelopmenteffortandmadepartofanSSISpackage
"template"thatyouuseasthestartingpointforeachnewpackage.

LastUpdate:1/22/2008

https://www.mssqltips.com/sqlservertip/1417/customlogginginsqlserverintegrationservicesssis/

2/3

5/16/2016

CustomLogginginSQLServerIntegrationServicesSSIS

Abouttheauthor

RayBarleyisaPrincipalArchitectatRDACorporationandaMSSQLTips.comBIExpert.
Viewallmytips
RelatedResources

https://www.mssqltips.com/sqlservertip/1417/customlogginginsqlserverintegrationservicesssis/

3/3

You might also like