I created a package with 3 DataFlowTasks using ForEach Loop Containers. DataFlowTask '1' will push the data of 5 different .txt files into table 'A'. DataFlowTask '2' will push the data of 7 different .txt files into table 'B'. DataFlowTask '3' will push the data of 8 different .txt files into table 'C'. So, Finally the complete package should load the data of 20 different .txt files into three different tables in SQL Server 2008.I scheduled this package to run daily at 7am. Now what I have to do is, I have to build a SSRS report which should take a Date as a parameter and should show whether all the 20 .txt files data is successfully loaded into the table as follows? Rundate FileName Success/fail 2011-07-01 ABC Success 2011-07-01 DEF Success 2011-07-01 XYZ Success .... .... .... What is the easiest way to do this. Pleae help me. I know how to create a SSRS report but for that the data should be in a table. How can I create the table with this details ? Is there anyting that I can do in the package to push this data into some new table.

The easiest way is to add custom logging: http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspxArthur My Blog

you can use event handlers for each data flow task, add an event handler OnPostExecute, and write the datetime and any other things you want as log to a table with execute sql task. sample of event handlers for logging is here: http://beyondrelational.com/blogs/vishalpawar/archive/2011/05/25/ssis-custom-logging-using-event-handlers-and-sql-server-job-scheduling.aspxhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

How can I get the filename of .txt file? The one which is picked up by the ForEach Loop in the DataFlow task. Also, I need the Success or Fail value for each .txt file.

you probably fetched the file name into a string user variable, you can use this variable in event handlers tab as input of your logging taskhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Thanks. How can I get the Success/Fail value of each .txt file if I add an event handler OnPostExecute ?

Thanks. How can I get the Success/Fail value of each .txt file if I add an event handler OnPostExecute ? when you add a task at OnPostExecute , this will raise when task successed, but for more reliable solution, you can add a task after each data flow task and connect SUCCESS precedence constraint to it and in this way you don't need event handlershttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Want to make sure couple of things... 1. OnPostExecute will raise when execution is completed and it doesn't matter whether the task is succeded or failed right ? 2. What Task I have to add after each data flow task ? 3. What is SUCCESS precedence constraint and where I can locate it? How does it work? Sorry if I'm asking stupid ques. I recently started working on SSIS.

I answered them as your numbers: 1- yes 2- any task you want to do logging with this, if you want to log on sql server table, use execute sql task and write an insert statement which is parametrized and get input parameters from variables. or if you want to log other place use script task or send mail task or any other task you like 3- when you click on a task in control flow, there will appear a green arrow under it, drag and drop it on the task of logging ( number 2 ), this means success precedence constraint, if you use red arrow, means failure constraint. success constraint will happen only if first task done successfully.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

If the task executed successfully then only log will insert into a table right? ortherwise not. But the reason I'm creating a log is to monitor the success/failure. I have to do the same thing for all the work flow tasks right ?

if you want to create log for all package tasks, it is better to use SSIS Logging, go to menu->SSIS->Logging, here you can add a log provider ( where you want to create log ), and select objects which you want to enable logging on them (selecting the package will enable logging on whole hierarchy), and then in details tab, you can select what events you want to monitor( for example on variablevaluechanged,onerror,..) this will create general logging on the package and objects, but if you want to create custom logging ( for example you want to log the filename variable ) it is better to create custom logging, like the sample I described above, or use any event handler you want to log, and add execute sql task there, and add system and user variables as input parameters .http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Thanks for your help. I don't need SSIS logging. I'll go with what you said and will get back to you if I have any questions.

In the sample you described above will insert a log into a table Only when the task executed successfully right? I tried to connect Failure Constraint to the another Execute SQL Task. In this way the package will insert a log for both success and failure as well. But I didn't see any Red Arrow coming out from the Data Flow Task.

first yes, second, you can connect the green arrow to second log task,and then right click on green arrow , and select failure, this will cause failure constrainthttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com