Sorting project items under SSIS projects

For some reason, SSIS projects in VS.NET 2005 will not sort project files, such as dtsx in alphabetical order. As far as I know, there are no built-in function in VS.NET 2005 to sort the items.

Therefore, the workaround is to open the SSIS project file and sort it manually. The SSIS project file is a valid xml document file. This means I can manipulate it in Excel, such as sorting it. However, I’ve opened the project file in notepad and copied part of the xml to a separate xml file to open in Excel, just in case Excel did more than what it should. Make sure the created xml document conform to xml standard.

After opening the xml file in Excel as xml table, perform sorting of the document in whichever order desired (usually A to Z). Once done, save the file, open the file in notepad and copy the changes and replace the project item section of the project file. Now the project items should be sorted accordingly. Do make sure backup is done before attempting this change.

Detecting long running SQL Agent jobs

Typically, you would like SQL Agent jobs to send error notification if it failed for some reason. If there’s no email, the job “should” be running ok, right?

 

Actually, one of the SQL Agent job that I’ve observed actually seems to be stucked half way through in one of the step that executes multiple SSIS packages. One of the package are extracting data from another SQL server via link server. For some reason, the job will not fail even if there’s some connection issue with the link server. The job does not fail in this case, thus no email notification will be sent.

 

Therefore, I have to check which SQL Agent job has run for too long. Fortunately, this has been done by many others. The trick is to make use of some SQL server built-in stored proc to query for the job status and its start time. Compare the start time with current time, coupled with the execution status yields you the long running jobs. The following are the reference that I used to built my own long running job detector.

 

http://www.databasejournal.com/features/mssql/article.php/3500276

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

 

To test out the stroed proc, I’ve created a job that executes the sql statement “waitfor delay '1:00:00'” to simulate a job that runs forever.