1

I have created a web site using VS 2013 that uses T-SQL queries to execute SSIS packages (created in VS2012). This seems to work however a couple of issues have come up. I must confess this is the first time my organization has attempted to use these 3 "system" together. So, we may be making some architectural mistakes.

1) The ETL package reports success, but doesn't logically complete. For example a lookup may error may occur so all the records are shuffled off to a text file. However, the because the ETL completes the SQL query believes it completes successfully and so doesn't throw an error to the web page.

2) The ETL package has an error but the web page reports successful completion.

A concern is that once the SQL query calls the SSIS package, the SQL query and SSIS package executions become parallel instead of sequential so the SQL query completes and reports success whether or not the SSIS package has actually completed execution. So, then question becomes how can I determine if the SSIS package has successfully complete exiting from the web page?

Regards, Tom

Improve this question
2
  • Are you using the project deployment model or package deployment model? Since you're on SQL 2012+ the default SSIS model will be project deployment so unless you did something special, you should be in the former.
    – billinkc
    Commented Feb 26, 2016 at 20:48
  • Is the Tsql query calling xp_cmdshell and DTSExec? Or is it calling a job that is setup to run the SSIS package?
    – paulbarbin
    Commented Feb 26, 2016 at 21:16

1 Answer 1

Reset to default
1

Try SSIS Logging:

You can use SSIS Logging for your SSIS package to push your errors to places you can monitor them. For example, we use SSIS logging to push our SQL maintenance errors to a local database/server, to a remote database/server, and also to a CSV file on a file share as shown in Figure A.

The SSIS logging that get's piped to SQL Server goes to a system table it creates in the database of your choosing called dbo.sysssislog. Once errors are piped to the sysssislog SQL table you can query it directly to check for errors using VS. (sysssislog is too raw a format for me personally. I parse this table to another table that has less and yet more meaningful information via a SQL Server job).

Or alternately, you can use SQL Server mail to continually check the sysssislog table and e-mail or text you if there's an error. If it's relatively low priority but needs to be reviewed, you could create an SSRS report to periodically review the data in the table. This won't fix your error, but at least you'll become more aware of what's going on and maybe give you more information you can use to find the actual underlying issue.

Figure A: SSIS Logging Example--SSIS execution information gets piped to a local database, remote database, and a CSV file.

enter image description here

Note:

From the testing I've done, I've noticed SQL/SSIS has issues if I actually delete the dbo.sysssislog table. So, instead I truncate that table instead of deleting it. Before you accidentally delete this table, make a copy of the schema first--in case you have to manually recreate it.

p.s.

Not knowing your situation, you may need to wrap your multi-point transaction within the distributed transaction coordinator (MSDTC or DTC). That way, if one part of your transaction fails between SQL Server, The web server and SSIS, the whole transaction is rolled back. Here is an entertaining (and hopefully useful) example(s) using DTC.

https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/

http://blog.arjanfraaij.com/2010/11/enabling-distributed-transaction.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.