Logging information when executing remotely SSIS from ASP.NET

I have a web server (no Integration Services installed) and a seperate SQL server. I have created an ASP page to remotely 开发者_StackOverflow中文版execute a SSIS package by firing up an Agent job on the SQL server where the package lives. The package reads an excell file,chosen by the web page user, process the data and stores the results in the database.

My question is: what would be a good approach to pass back to the ASP page logging data from the SSIS during runtime, so the user has some sensible information about the outcome of his request?

As a first cut, I'd suggest turning on the native SSIS logging and dumping that to SQL Server destination. It's about an 8 click process so really no reason not to enable it. Grab OnTaskFailed, OnError for sure, maybe OnInformation and OnWarning, depending on what sort of information you'd like to return to the user.

Once that's done, when the package runs if a table does not already exist in the database the you selected, SSIS is smart enough to make a copy of the msdb.dbo.sysdtslog90 / msdb.dbo.sysssislog (2005 / 2008+) in that database and begin logging to it.

At this point, your job has run, the execution history is automagically dumped to that log table and you can pull that information out. When the job runs, a new GUID is generated and that ExecutionId is how you can tie all the activities of a particular run together.

I'm not sure how you are passing the information to the job about which Excel file to process, perhaps you don't allow for concurrent execution and only one file will ever be run at a time. If that's the case, then you can always query for the most recent execution in the log and assume it ties to the user on the site.

Otherwise, I'd probably look at doing something like emitting the unique filename in the log [Script task, ~ Dts.Events.FireInformation(String.Format("FileName:{0}", Dts.Variables[MyExcelCM].Name.ToString()),...);] and then do some voodoo to parse the log, assuming you can't just create a filename to execution id run table, that'd be the cleaner means of tying a run to a particular file which would allow you to link back to the syssisslog.

Edit

CREATE TABLE
    dbo.ExecutionToFileMapping
(
    mapping_id int identity(1,1) NOT NULL PRIMARY KEY
,   executionid uniqueidentifier NOT NULL
,   file_name varchar(250) NOT NULL
)

Within the package, drag an Execute SQL Task on the control flow and point your connection manager to the database with the above table. Your query would look like INSERT INTO dbo.ExecutionToFileMapping (executionid, file_name) SELECT ?, ? On the Parameter Mapping tab, wire up the local variable that corresponds to the Excel filename and then use the system variable ExecutionInstanceGUID. When that statement fires, it will make an entry into the table with the execution GUID and the file name.

You can then link the logging results to a particular file(s)

SELECT
    E.file_name
,   L.*
FROM
    dbo.sysssislog L
    INNER JOIN
        dbo.ExecutionToFileMapping E
        ON E.executionid = L.executionid

end edit

Finally, as much as I enjoy SSIS, using Excel as a data source is a recipe for great frustration especially when user generated spreadsheets are involved. I can't count the number of instances of "the spreadsheets look the same" yet SSIS went tits up indicating the format was different. I have had a much better experience just querying Excel via OLEDB like this SO question discusses Query excel sheet in c# Depending on your SQL Server version, I'd then took advantage of table valued parameters in 2008+ and basically dumped things straight into tables.