BursttoFileSystemwithPostProcessingscriptorETLThisexampleshowshowtobursttothefilesystem,andincludeapostprocessingETL.ItdoesnotusetheinbuiltCognospostprocessingscriptasweonlywantthescripttorunwhenthisonereportisbursttothefilesystem.

Challengesthatarehandledthiswayinclude:

Onlyrunpostprocessingscriptonasinglereportoutputtofilesystem,notallreports. RenamingofPDFfiletoeliminatelanguagespecificationaddedbyCognos(enus). Deletingthexmlfilethatisalsocreatedwhenburstingtothefilesystem.

Step1:ConfiguretheCognosinstanceInCognosConfigurationmakesuretheoptionforSavereportoutputstoafilesystem?issettotrue.Thiscanbefoundunder[LocalConfiguration].[DataAccess].[ContentManger].

Next,underActionsmenu,chooseGlobalConfiguration.ThenontheGeneraltabsettheArchiveLocationFileSystemRoot.Thiswillbetherootdirectorywhereyouwilllatercreatesubdirectoriestostorereportoutputs.Youcancreateasmanysubdirectoriesasyoulike,buttheywillallhavetobecontainedunderthisoneroot.ItshouldbecreatedasaURI.Thehelpdocumentsareprettyclearbuthereissyntax:file://\\\.

YouwillneedtorestarttheCognosServicefortheconfigurationchangestotakeeffect.

Step2:CreateafilesystemlocationinCognosConnectionInCognosConnectiongotoLaunchthenCognosAdministration.GototheConfigurationtabandchooseDispatchersandServices.ThenchoosetheiconforDefineFileSystemLocations.

ClickNew,andgivethefilesystemlocationaname.Also,makesurethatyourconfigurationofthefilesystemrootlocationtookeffect.IfthefilesystemrootlocationisnotfilledoutwiththelocationyoudefinedinCognosConfiguration,somethingdidnttake.IBMhasaKBarticleonthis,butIfoundthatsometimesitjusttakesacoupletriestomakeitwork.Itshouldlooksomethinglikethis:

Nowyoucandefinethesubdirectoryyouwanttostorethereportsto.Use\\inthebeginning.Idontknowwhyyouneedtouse\\,butthatswhatIdidtomakeitwork.Also,makesureyouhavecreatedthesubdirectoryinthefilesystem.

Step3:CreateaSSISpackagethatwillprocesseachreportfilecreatedbyCognosduringthefilesystemburstThisexampleusesSSIS2005butcanprobablybeportedtootherETLtoolsorjustscriptedintoa.batfile.ImnotgoingtogointodetailsabouthowtocreatetheSSISpackage,butwillbrieflyexplainonewaytodeletethe.xmlfilesCognoscreatesaspartofwritingtothefilesystemandalsohowtorenamethefiletoeliminatetheenuslanguagedesignationthatCognosaddstotheoutputfiles.

Todeletethexmlfilesstartbycreatingaforeachloop,toloopthrougheachXMLfileinthedirectorywhereCognosoutputthereports.Thenuseafilesystemdeletetasktodeletethefile.Makesuretodefinethesourceofthefilesystemtasktobeavariabledefinedbytheloop.

Toremovetheenuslanguagedesignationfromthereportoutput,againcreateaforeachloop.Butthistimeloopoverthereports,onlylookingforfilesthatarelike*enus*.PDF.Mightnotbepdf,thisdependsonhowyouhadCognosoutputthereports.Thendefineafilesystemtasktorenamethefiletoremovetheenus.

Onethingtonoteisthatyouwillnotbeabletorenameordeletefilesthatareopen,andlocked,byanotherprocess.Thiscancreatechallenges.Therearelotsofwaystodealwiththis,butthebestisprobablytokickeveryoneoffthefilesharebeforerunningtheseprocesses.

Step4:CreatestoredproceduretokickoffscriptCreateastoredprocedurethattakesthefullpathoftheSSISpackageasaparameter.Thenusexp_cmdshelltoruntheDTEXEC.execommandlineSSISexecutable.ThiswillcausetheservertoruntheSSISpackage.AsanalternativeyoucoulduseaSQLAgentjoband/orstoretheSSISpackageontheserver.

CREATEPROCEDURE[dbo].[ExecuteSSIS]packagepathparam.notethatthereisabuginxm_cmdShellknownbyMSthatonlyallowsonesetofdouplequotes.Thismeanswecannotexecutepackagesstoredatpathsthatincludespacesinthepath.Youcanfinddocumentationonthisbugifyou

googleit.@DtsxPathvarchar(2000)

ASBEGIN

SETNOCOUNTON

buildcommandlinestring.DECLARE@CmdStringvarchar(2500)SET@CmdString='"C:\ProgramFiles\MicrosoftSQLServer\90\DTS\Binn\DTEXEC.exe"

/FILE'+@DtsxPath

EXECxp_cmdshell@CmdStringEND

Step5:CreateareporttokickoffthestoredprocedureThisleveragesstoredprocedurequerysubjectsinFMtorunthesprocpassingtheparameter,whichisthefullpathtotheSSISpackage.Thesprocparameterwillbelistedasanargument.Inthevalueforthatargumentdefineaparameterusingthe?param?syntax.

Oneinterestingthingtonoteisthatyoursprocquerysubjectreturnsonedataitemcalledoutput.InSQLServerthiswillreceivetheresultofthesproc,whichinourcasewillbetheSSISexecutionlog.Thisisreallycool,becauseyoucanhaveCognosstorethatresultorevenemailtoyou.

Step6:CreateareporttoexecutethesprocanddisplaytheoutputInreportstudioauthorasimplelistreportthathasapromptpagewithatextboxpromptfortheparameterdefinedintheFMmodelsprocquerysubject.(?param?).

Theninthelist,displaytheoutputfieldfromtheFMsprocquerysubject.Iwouldformattoremoveanycellborders,etc.

YoucannowtestthereporttomakesureitkicksofftheSSISpackageandthatthepackagedoessomething.Whenyourunthereport,itwilloutputtheresultoftheSSISpackage.

Step4:SetyourjobtobursttothefilesystemImnotgoingtocoverbursting,butwillassumeyoualreadyhaveburstingsetupinareport.CreateanewjobinCognosConnectionthathastwostepsrunInsequence.Itsveryimportantthatyouruninsequencenotallatonce.Firststepwillbetorunyourburstreport,outputtothefilesystem.Itshouldbesetupsomethinglikethis:

MakesuretoedittheoptionsonSavetothefilesystem,choosingthelocationthatyouwanttosavetowhichyoudefinedinstep2ofthisdocument.

ThesecondstepwillbetorunthereportusedtoexecutethesprocwhichkicksofftheSSISpackage.Inthiscaseyouwilloverridethedefaultvalueontheprompts,passingthepathtotheSSISpackage.Youalsomustchoosehowyouwantittooutputandtowhere.Inthiscase,IstoreditasaPDFtothecontentstore,butyoumightwanttohaveitemailyouorsaveashtml.ThisoutputwillbetheexecutionlogoftheSSISpackage.

Yourjobshouldlooksomethinglikethis:

Atthispointyoushouldbeabletorunorschedulethejobanditwillexecutetheentireprocessofburstingthereport,outputtothefilesystem,deletingthexmlfiles,renamingthereportfiles,andstoringtheSSISresultlog.