Time for a new, simple WordSearch – this time focused on SQL Server development!
Have fun!
TSQL
Integration Services Catalog package errors
Short post today…
My client makes extensive use of SSIS and deploys the packages to the Integration Services Catalog (ISC), and runs them via hundreds of jobs.
When one of the jobs fail, I have to go get the details.
Job History doesn’t have it.
So my process was:
- Get the package path out of the job step
- Expand the ISC
- Expand SSISDB
- Expand the Folder (get this from the job step)
- Expand the Projects Node
- Expand the correct Project (get this from the job step)
- Expand Packages
- Right-click the relevant package (get this from the job step)
- Go to Reports, Standard Reports, All Executions
- Filter for Failed Executions
- Cross your fingers that you can get useful info from the result.
Now, repeat for each job step that failed.
The SSIDB database has all of this in the Tables (schema – Internal) and or the Catalog Views.
It took a while, but I created this to run around those tables and views to get ALL of the errors for the last day:
/* Created by Kevin Hill, Dallas DBAs LLC, 12/28/2018 Inspired by work from Jules Behrens Index tested by Peter Schott This queries multiple SSISDB tables to return a clear path from Top to bottom related to errors in an Integration Services Catalog based SSIS package. It has not yet been tied back to job execution, nor is it set to email info out. Use this as a backup to your normal job failure checks to tie it all together instead of spending a full cup of coffee clicking and drilling into the cumbersome All Executions report. Free to use and modify, please leave this header as a courtesy. */ Use SSISDB go Select Distinct fold.[name] as Folder_name ,proj.[name] as Project_Name ,pack.[name] as Package_Name ,ops.[message_time] ,mess.[message_source_name] ,ops.[message] --,mess.[execution_path] -- this is pretty long path if you are pasting into an email or Excel From [internal].[projects] proj Join [internal].[packages] pack on proj.project_id = pack.project_id Join [internal].[folders] fold on fold.folder_id = proj.folder_id Join [internal].[executions] execs on execs.folder_name = fold.[name] and execs.project_name = proj.[name] and execs.package_name = pack.[name] Join [internal].[operation_messages] ops on execs.execution_id = ops.operation_id join [internal].[event_messages] mess on ops.[operation_id] = mess.[operation_id] and mess.event_message_id = ops.operation_message_id and mess.package_name = pack.[name] Where 1=1 and ops.message_type = 120 -- errors only --and mess.message_type in (120,130) -- errors and warnings and ops.message_time > getdate() - 1 -- adjust as necessary /* If you have a very large SSISDB due to activity or long retention, please consider this index: CREATE NONCLUSTERED INDEX [NC_OpsID_MessageType_MessgeTime] ON [internal].[operation_messages] ( [operation_id] ASC, [message_time] ASC, [message_type] ASC ) INCLUDE ([message]) */
If this is useful for you, please rate it on the TechNet Gallery where I posted it.
If you have a very active server, this can take awhile as the indexes on the underlying tables are for the cascading delete cleanup process. I’m working on a set of good indexes that will likely be unsupported by Microsoft, but useful.
Adjust as you see fit. Please leave the header if you use it.
Thanks for reading!
—————————————————————-
T-SQL Tuesday: Brick walls
T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t). This month’s edition is hosted by Wayne Sheffield (b|t) who has asked us to tell a story about a time we ran into a Brick Wall.
So many walls, so few Bulldozers…
The largest brick walls I’ve ever run up against were not technical issues as you might expect…but people and processes.
Processes:
I was working for a company that bought a company that bought the company I had hired on with. The newest owner was growing their managed hosting business by buying others out. Not a bad approach when you have deep pockets. BUT, they never integrated any of them. Everything was still in its original silo. So, anytime I got a call or a ticket, I had to search through 4-5 knowledge base/Sharepoints, 2-3 password repositories and 4 ticketing systems. I could easily spend 2 hours to research and close a ticket that took 3 minutes to resolve on the technical side.
This place broke my flipper. As in, I no longer gave a flip after it took a full year to get all the access I needed to every environment. The Bulldozer here is that I got laid off. They gave me a big bag of money to leave and go do cool things with modern SQL versions for other places. YES!!
People:
I was asked to bill a crazy high rate for a full-time contract, to watch over a very small environment of 25 servers. I was asked to put in best practices, be the unofficial team lead, make things go faster, secure them, etc.
BUT…JimBob the Manager (clearly not his real name) gave me the brick wall at every turn. It took 6 weeks to get rid of NetBackup in favor of SQL Server Maintenance Plans. (I know…lets not go there.). Compressed backups? Made me prove it. Index maintenance? OK, but he wouldn’t let me schedule it. New indexes that actually made sense? No chance, as that was a code change to the application our Very Big Vendor had written for us. Within 3 months, I was down to 15 minutes of real work a day, and the rest spent blogging, Tweeting and answering questions on DBA.StackExchange.
The Bulldozer on this one was me walking out the door into cooler things. 33% pay cut was worth it, to save my sanity and go independent again!
Technology:
I don’t run into technical Brick Walls, because I know how to Tweet using #sqlhelp, as well as read blogs from people I trust in the #sqlCommunity, and Vendor docs.
Thanks for reading!
TIL: Joining across databases with different collations
Quick post today…
Yesterday I was approached by a developer with something it has never occurred to me to try in 18 years of being a DBA.
“Can I write a query across two databases with different collations?”
My first thought took me back to SQL 7, when you had to reinstall SQL Server to change collation settings (IIRC). My second and immediate thought was “Quit being so old and look it up, Kevin.”
So I bounced over to The Google, then to an answer on StackExchange that I can’t find right now (of course, but I did upvote it yesterday) and found what I needed.
I have two test databases on my local SQL 2014 Dev instance, both with an Orders and OrderDetails table in them with no data. Both were the default collation so I changed one of them to CS instead of CI:
ALTER DATABASE [test2] COLLATE SQL_Latin1_General_CP1_CS_AS
I entered a test order in each DB manually, and in the “Note” column I changed the case of the word Five. Why Five? No idea…its just text, and that’s what my developer (Jared) was having to join on.
Select * from Test.dbo.orderdetails where note = 'five' Select * from test2.dbo.OrderDetails where Note = 'FIve'
Results:
OrderDetailID OrderID ItemQty Note ------------- ----------- ----------- --------- 1 1 5 Five (1 row(s) affected) OrderDetailID OrderID ItemQty Note ------------- ----------- ----------- --------- 1 2 5 FIve (1 row(s) affected)
Next up, write the Select statement the normal way to duplicate the error Jared was getting:
--fails Select * from Test.dbo.orderdetails A join test2.dbo.OrderDetails B on A.[note] = B.[Note]
Result:
Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
So…now to bring it in the correct way (and there is probably more than one correct syntax for this…I didn’t look):
--works Select * From Test.dbo.orderdetails A Join test2.dbo.OrderDetails B on A.[note] = Cast(B.[Note] as varchar(50)) COLLATE SQL_Latin1_General_CP1_CI_AS
Result:
OrderDetailID OrderID ItemQty Note OrderDetailID OrderID ItemQty Note --------------------------------------------------------------------- 1 1 5 Five 1 2 5 FIve (1 row(s) affected)
All I did here was tell the query to treat the Test2 Note field as Case Insensitive for this one query. Nothing changed in the source data itself, or the database settings/DDL.
It took me five times longer to write this up than to test it, since I already had some generic test databases ready to go. Same ones I use for testing my answers on MSDN, SSC and DBA Stack questions.
If this helped you, please share, like, re-post, etc. as you see fit. Or a thumbs up in a comment if you like.
Thanks for reading!