blogs.conchango.com

I've said on numerous occasions, in slightly different ways, that making the move from DTS to SSIS...

I've said on numerous occasions, in slightly different ways, that making the move from DTS to SSIS

Hi ,

I am working on an ETL which have around 12-15 data flow tasks.Everything is working fine and I am able to fetch data properly.

Now I want to have an Audit table which captures all the transaction details during that package run.ie I want to have a table called Data_Load_Statistics in which I want to insert "tablename" "Date" "Starttime" "Endtime" "Total number of rows transfered" etc ..

I know SSIS stotes this information somewhere as we can see all this in "Execution Results" tab.

Can you tell me how to proceed on this.

Thanks in advance ..

Hey Jamie , Thanks alot .. its working for me ..

Hi jamie,

I am using the following query to insert custom log.

"Insert into  ACEODBC_Log (Company,PackageName,EventType,TaskName,Description,LoadDate,PackageDuration,ContainerDuration,InsertCount,UpdateCount,DeleteCount,Host) values

('"+@[User::Company]+"',

'"+ @[System::PackageName]+"',

'OnPostexecute',

'"+ @[System::SourceName]+"',

'',

'"+ (DT_STR,15, 1252) (DT_DBDATE) GETDATE() +"',

'"+ (DT_STR, 6,1252) DATEDIFF( "ss", @[System::StartTime] , GETDATE())+"',

'"+ (DT_STR, 6,1252) DATEDIFF( "ss",@[System::ContainerStartTime],GETDATE())+"',

"+ (DT_STR, 4,1252) @[User::RowCount]+",

0,

0,

'"+ @[System::MachineName]+"')"

I am getting all the details except Container Duration ... I t always shows 0 value ... ne idea y ??

I think I hav got where the problem is ...

Actually I have written this custom log code inside a Executesqltask on Postexecute event of my dataflow ..

when I do DATEDIFF( "ss",@[System::ContainerStartTime],GETDATE()) , Its takes containerstart time as @[System::ContainerStartTime] of ExecuteSQLtask and not the parent data flow and hence it is always equal to getdate() and hence I am getting 0 as my  ContainerDuration ...

Any solution to avoid this guys ...

Thanks in advance ...