A Fine Slice Of SQL Server
Tim Mitchell has a new GitHub repo:
The SSIS Catalog Dashboard is a simple collection of reports that provide insight into the activity within the SSIS catalog. The first of these is the Dashboard report. This report shows a summary of the number of packages that are running or have run in the recent past.
The dashboard repo, a Reporting Services project, is available on GitHub and is licensed under GPL version 3.
Comments closedEduardo Pivaral shows that you should clear out some of the bigger SSIS tables occasionally:
If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.
If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.
This kind of batched delete works for more than just the Integration Services tables; it’s a good plan wherever you have a large table and need to delete a fairly significant number of records from it.
Comments closedJoost van Rossum is excited about a new SSIS data source in preview:
First you need to download and install the latest SSDT (Version 15.9.0) and since it is still in preview, you can only use it within SSDT or on a Azure Integration Runtime in Azure Data Factory.
If you drag the new Power Query Source to your Data Flow canvas and edit it, you can paste your Power Query script from Power BI (or Excel) in the query textbox. So there is not yet(?) an editor, but this should simplify a lot of tasks that could were previously could only be solved with .Net scripting.
Read on for more details. Because it’s a preview, there’s still a lot of work yet to do it, it seems. But on the bright side, it’s new functionality in Integration Services.
Comments closedAndy Leonard troubleshoots an odd error in SSIS:
Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)
As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:
“Whut?!”
That is a reasonable first response. Fortunately, Andy also had a second response which was more helpful in finding the root cause.
Comments closedPeter Schott improves upon Kevin Hill’s script:
Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages. I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance. Kevin’s included that at the bottom of his query on the post above. (You probably don’t need the included “message” column, though.)
I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.
Click through for the script.
Comments closedMy 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.
I’d recommend the query route if you have to do this more than once or twice.
Comments closedTim Mitchell has a script which replaces [internal].[cleanup_server_retention_window] in the SSISDB database:
Earlier this week, I blogged about the automatic cleanup process that purges old data from the SSIS catalog logging tables. This nightly process removes data for operations that are older than 365 days. While this is useful, many SSIS admins have complained that this process is very slow and contentious on large or busy SSISDB databases.
In this post, I’ll show to you one of the main reasons this purge process is slow, and will share a more efficient way of performing this delete operation.
Click through for the script and explanation.
Comments closedTim Mitchell shows us how to use the foreach loop component to iterate over a set of files:
The SSIS foreach loop is configured to allow you to easily ingest multiple data files in a single data flow task. For this to work, all of the files would need to reside in the same directory structure (either locally or on the network), and they must all have the same structure and metadata.
In this design, the data flow is contained within the foreach container, which will execute the contents of that data flow task once for each file found in the specified directory.
This gives us a good pattern for loading a bunch of text files, such as monthly extracts from a different system.
Comments closedKenneth Fisher shares some thoughts on SSISDB:
We’ve been doing a lot of upgrading recently and at one point had to move an instance from one 2016 server to another. In the process, we found out (the hard way) that it’s not that easy to move SSISDB (the SSIS Catalog that may or may not be named SSISDB). I mean it’s not hard, but it’s definitely not a basic backup/restore. The full BOL instructions on how to do this are here. That said, here are the elements that are involved.
Read on for the list as well as an order of operations.
Comments closedThe command is simple. You can get or set the property using the value for given property path. As usual – when you get the value, you leave the value blank. The path – well – is the path to the element in the package or the project. You use backslashes to separate elements in the package tree, and at the end, you use
.Properties[PropertyName]to read the property. If you use the elements collection – like connection managers – you can pick a single element using square brackets and the name of this element.
Read on for more, including limitations and useful testing scenarios.
Comments closed