SQL Server Archives - John McCormack DBA

How to easily upload, download and migrate SSIS Packages with DTUTIL

9th June 2023 By John McCormack Leave a Comment

SSIS and DTUTIL background

Woman writing code with reflection in glasses

SQL Server Integration Services (SSIS) is a powerful tool, but migrating packages across SQL Servers can be a slow and thankless task if you don’t use automation. The single best way to do this is by using DTUTIL, a command-line utility provided directly by Microsoft. When it comes to moving 1000s of packages, I would only use DTUTIL because it can achieve in minutes what it would takes days to achieve using point and click.

–

Migrating a single package

Migrating individual packages from one SQL Server to another directly is also possible with DTUTIL. This is an example command below:

dtutil /SQL "SourceFolder/SourcePackage" /SOURCESERVER "SourceServer" /COPY SQL;"DestinationFolder/DestinationPackage" /DESTSERVER "DestinationServer"

Specifies the source package location and server with /SQL “SourceFolder/SourcePackage” and /SOURCESERVER “SourceServer”.
Copies the package to the destination SQL Server with /COPY SQL;”DestinationFolder/DestinationPackage”.
Identifies the target SQL Server with /DESTSERVER “DestinationServer”.

–

Migrating multiple packages

This process can be scripted similarly for multiple package migrations. The easiest way is to generate the commands using T-SQL, print them to screen then copy and paste in to a command window.

If you run the command below on the source server, it will list out a command to migrate every package.

DECLARE 
	@sourceserver sysname = N'DBSERVER1'
	,@destserver sysname = N'DBSERVER2'
	,@foldername sysname = N'DATAWAREHOUSE'
	
SELECT 
'DTUTIL /SQL "'+sspf.foldername+'\'+ssp.[name]+'" /SOURCESERVER "'+@sourceserver+'" /COPY SQL;"'+sspf.foldername+'\'+ssp.[name]+'" /DESTSERVER "'+@destserver+'"' as cmd
FROM msdb.dbo.sysssispackages ssp
JOIN msdb.dbo.sysssispackagefolders sspf
ON ssp.folderid = sspf.folderid
-- WHERE sspf.foldername = @foldername -- Uncomment line to only use specific folder

–

Upload SSIS packages from disk

Uploading an SSIS package from your disk to SQL Server using DTUTIL is quite straightforward. The command line syntax you will use looks like this:

dtutil /FILE c:\mypackage.dtsx /DestServer DBSERVER2 /COPY SQL;"foldername/mypackage"

–

Download SSIS packages to disk (Great for backup)

Downloading an SSIS package to your disk from SQL Server using DTUTIL is also straightforward. The command line syntax you will use looks like this:

dtutil /SQL mypackage /COPY FILE; c:\destPackage.dtsx

Again, to download multiple packages, a script that lists out and provides a command would be the most efficient way to proceed. Try adapting the ‘migrate’ script above.

–

Summary

In conclusion, DTUTIL is a highly useful tool for managing SSIS package migration. While it might take a bit of practice to get comfortable with the syntax, its flexibility and efficiency make it worth the effort. Remember to always check your commands before executing them to prevent any unwanted actions. A full list of command is available from Microsoft Learn.

And that’s it, folks! I don’t have much other SSIS content, only this one which is more of a hack. With these simple and effective DTUTIL commands, you can now manage your SSIS packages with ease. Feel free to share this blog post with anyone who might find it helpful, and don’t hesitate to leave a comment below if you have any questions or want to share your own experiences with DTUTIL.

T-SQL STUFF – Taking it to the limit

11th April 2023 By John McCormack 3 Comments

I don’t often go down code based rabbit holes, its just not what I do day to day but a while back, someone asked on twitter’s #sqlhelp hashtag if there was a character length limit to the STUFF function. The documentation didn’t have an answer. For that reason only, I decided to take a look. By some coincidence this month on behalf of T-SQL Tuesday, Reitse Eskens (b|t) asks:

“What are your most fun script, procedures or statements that you’ve written”

T-SQL Tuesday #161 Invitation. Having fun with T-SQL

So, I thought I’d share my code. Spoiler alert I ran it up to 50 million characters on my laptop and it kept working but it started to take a long time (4H, 28 minutes). Going up to these limits is not your day to day typical use of the function, it’s quite unusual and not really a production scenario. I’m not sure this code serves any useful purpose but by sharing, it saves it sitting smugly in my “SillyStuff” folder doing nothing.

What is the point of the STUFF function

There’s no point in me paraphrasing, here is Microsoft’s own explanation: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

So in my example where I am trying to stuff text into 50 million character long strings, it’s a mostly pointless exercise in terms of reality. A good use case might include obfuscating something sensitive like PII data, formatting dates and times with non standard characters or simply inserting a string at a specific place within another string.

The Code

/*
	This came about after @PamelaMooney tweeted: #SQLHelp Is anyone aware of a character length on the STUFF function?
	I still don't have a definitive answer but I can get it working up to 50 million characters. If you are stuffing beyond that, good luck to you.
*/

DECLARE @var NVARCHAR(MAX) = N''
DECLARE @counter int = 0
WHILE @counter < 200001
BEGIN
SET @var += '500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv500 characters pqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv'

SET @counter +=1
END
SELECT STUFF(@var, 5, 1, ' Still works! ')
SELECT LEN(@var)

I’m going to leave it there for now, its a waste of CPU cycles if I keep trying to break it. If anyone wants to push this to the limit, please feel free to let me know the results in the comments. I hope it was a fun read, back when I hosted T-SQL Tuesday, I asked about snippets of useful code. Although this is not so useful, it’s the type of topic I like to see offered.

DataGrillen 2022 – Photos and notes

8th June 2022 By John McCormack Leave a Comment

I’m just back from DataGrillen 2022. As ever, I wanted to put together a short post and use the opportunity to share some things I learned, and share some photos of the event. First of all, thanks for such a hospitable welcome from William Durkin and Ben Weissman and congratulations for hosting a very successful event.

On top of everything else that goes into planning a complex event like this, they also had an added responsibility this time to keep people free of covid as far as was reasonably possible. This meant mandatory testing, vaccine passports and masking wearing (inside only). It was a small price to pay after two years of online conferences only.

The barbecue event was on the middle night after the sessions. There was a great choice of food and everything was cooked really well, and of course there were complimentary beers to wash it down.

Following on from the main event which comprised of two days of technical sessions, the host town of Lingen was celebrating the historic Kivelingsfest, a medieval festival dating back hundreds of years. As a result, many of the conference attendees stayed on an extra day to attend the festival which was great fun.

It was home on Sunday but not before we got the chance to celebrate André Kamman’s by taking a boat trip along Amsterdam’s canals.

Datagrillen 2022 photos

Andrew Pruski DeepDive into Docker presentation

DataGrillen 2022 Sessions

These are the sessions I attended. I tried to fit in as much as possible from the two day event.

John Martin – Performing successful cloud migrations – Chalk & talk

It was a very informal delivery by John who guided us through a timeline and the phases of a successful cloud migration. It was a cloud agnostic session so the key information could be used with any major cloud provider.

Andrew Pruski – A deep dive into Docker

I wanted to attend this as I haven’t used Docker a great deal and I wanted to learn more. Andrew provided a great overview and worked through some very well prepared demos. I am keen to try to follow these up by visiting his github repo for the content.

Gianluca Sartori – Time Series for relational people

Gianluca gave a great overview of the differences between relational and time series databases and went on to review a few. He settled on influxdb and demonstrated in detail how it could be used along with Grafana and Telegraf agent (TIG stack) to provide really useful metrics. I do use the TIG stack in my work but I learned about some great settings for dealing with or discarding older data that I plan to take forward.

Monica Rathbun – Performance tuning Azure SQL Database

This session was packed to the rafters and extremely hot, yet Monica’s enthusiastic and engaging style made it so easy to stay focused on the message. The message was delivered well and easy to understand. I took away some key information here regarding query store and readable secondary databases. Overall, it was my favourite session of DataGrillen 2022.

Argenis Fernandez – Ransomware sucks. Beef up your databases against it.

This had to be close contender for my top session. Argenis knew what he was talking about and it showed. He gave very good advice and I have returned home with a checklist that I will need to turn into an action plan.

André Kamman – Looking under the hood of the parquet format

I’m familiar with parquet as I use AWS Athena a lot however I’ve never really looked under the hood. I honestly expected this session to be a bit above me but André’s communication style made it very accessible for attendees of all knowledge levels and easy to follow along.

Jess Pomfret and Sander Stad – Deploying Azure Resources with PowerShell Azure Functions

Jess and I were fellow newcomers at Data Grillen in 2019. I always enjoy her sessions so this was not to be missed. It was my first time seeing Sander and the two presenters worked well to deliver this session. I left convinced that I need to change at least one part of my automation routine to include Azure functions, so I will hopefully be checking them out this week. (If time allows, the trouble with conferences is you return home with SO many ideas)

Heini Ilmarinen – Less Clicking, More Coding! Azure Data Platform Development Using Infrastructure as Code

I’ve dipped my toe into Terraform but I’ve used BICEP and Cloud formation a bit more. After Heini’s session, I get the benefits of Terraform and I’ve added it to my ever increasing list of things to review. A great session in which Heini did well to keep us engaged all the way through.

Robert French – Goldilocks and the three business bears; story telling for business

This was the last session of a very busy two days. They broke the mould when they made Robert and as expected, his session also broke the mould. Robert delivered the session with unmatchable energy, kind of at a blistering pace but the pace was just right at the same time. He got through a huge amount of visual aids. He gave great advice on where you eyes are drawn to in report, how to make key data pop out and how to usual colours and fonts carefully.

Falling back in love with Data Community events

14th March 2022 By John McCormack Leave a Comment

Data Community Events

Last week, I had hoped to go to SQLBits conference in London but a variety of factors meant I could really only attend the Saturday morning sessions (and virtually at that). I’ve really missed Data Community Events like SQLBits and others.

I wanted to go because I’ve been slightly disengaged from the data community for a year or so, probably due a combination of factors such as lockdowns, zoom fatigue and a few speaking knock backs in the last year which dented my confidence a bit. However I wanted to at least attend SQLBits in part, in the hope it would inspire me and kick start some new blogging and possibly presenting opportunities. I’ve had some great times attending and presenting at data community events in the past such as at DataGrillen, DataScotland, and SQLBits and I knew it would be worth the effort to get back into the swing of things.

Just attend some sessions and take it from there

Attending sessions where you know little or nothing of the subject matter can be extremely rewarding.

I’m glad I did. The sessions I attended were all extremely enlightening and I enjoyed following along. One thing that immediately came back to me is that attending sessions where you know little or nothing of the subject matter can be extremely rewarding. These sessions serve to keep you informed of the overall technology trends and who is doing what. For example, I won’t have much opportunity to use Azure Arc in the near future but it’s been around long enough that I can’t ignore it completely. Attending Ben Weissman’s 20 minute taster session was just enough and it gave me some ideas about how it could be used in co-ordination with our on premises environment. I also really enjoyed learning about the developments to SQL Managed Instance since I last used them over a year ago. Some of the improvements released during the previous 12 months could actually make it a far more viable product for my company.

Keep it going

Thursday night (17th March 2022) sees the the latest meeting of the Glasgow Data User Group. I will make a point of attending, even although the speaker is discussing ETL in the cloud which is not a big area of professional interest for me, I know I will learn something and I hope it will entertaining as well as informative. Plus it will be good to see some old faces, albeit we are still remote.

12 blog posts

I committed to 12 blog posts this year, this one can serve as #1 and it gets me started. Hopefully by attending many other events, I can find the inspiration needed to get back to creating my own content and keeping up with developments in the data community. As well as keeping up with old friends.

T-SQL Tuesday #143 Wrap Up

19th October 2021 By John McCormack 2 Comments

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

T-SQL Tuesday #143 – Short code examples

I learned so much by hosting this and made sure I gave due care to reading every post. It was also a lot of fun and allowed me to interact with people in the community that I haven’t met before. If you haven’t hosted T-SQL Tuesday before, please contact Steve Jones as we are always looking for new hosts.

Wrap Up

Rob Farley – Short and to the point like I asked for, Rob details a quick way to find objects. And he was happy to clear up for the reader that I didn’t mean GOTO as in the old BASIC syntax you could run on your commodore 64. (For me it was an Amstrad CPC464)
http://blogs.lobsterpot.com.au/2021/10/12/go-to-scripts/

Koen Verbeek – Koen shows us numbers tables, tally tables and a dates table. These are really useful constructs for allowing your queries to go “set based”. Essential reading for anyone who cares about performance. https://sqlkover.com/t-sql-tuesday-143-short-code-examples/

Aaron Bertrand – Aaron shows us how he “bulletproofs” his answers for dba.stackexchange and Stack Overflow. db<>fiddle was new to me. I love some of Aaron’s demo database names like [master (Restoring…)]. I ran the create command on my test instance and had to drop the DB right away as it was giving me the chills.
https://sqlblog.org/2021/10/12/t-sql-tuesday-143-worst-metadata

Deborah Melkin – Deborah shows us a really useful debugging trick when creating stored procedures that use dynamic sql. Many of us have been lost in dynamic sql at some point, and this snippet is great at helping you see where you are.
https://debthedba.wordpress.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Kenneth Fisher – Kenneth shares a compendium of previous posts which all require some serious reading. My favourite was “all jobs that ran during a given time frame”
https://sqlstudies.com/2021/10/12/code-examples-t-sql-tuesday-143/

Jeff Hill – Jeff shared 4 great PowerShell snippets. True to this month’s request, they are short and incredibly useful. Want to know what version of Windows you are running on your Server or when it was last rebooted, look no further.
https://sqladm.in/posts/tsql-tuesday-143/

Chad Baldwin – Chad is a newcomer to T-SQL Tuesday and chipped in with a stellar first post. I must admit, I’ve never given much thought to how to format a result set as I’ll usually do it in the client, but when you need to; it is possible as Chad shows. But that’s only the start. There’s too much to discuss in this digest as he also covers tally tables, random numbers and overcoming the divide by 0 problem. Did I mention he also covers docker, monitoring/filtering log files and setting aliases. Cap doffed.
https://chadbaldwin.net/2021/10/12/tsql-tuesday-short-code.html

Andy Yun – Random numbers, random delays (I wonder if Scotrail use this script) and random strings. Thanks Andy for a great post. There are great scripts on their own and for building into more complex ones.
https://sqlbek.wordpress.com/2021/10/12/t-sql-tuesday-143-random-fun/

Kevin Chant – Kevin discusses just how to get the most out Glenn Berry’s diagnostic scripts, specifically in relation to missing indexes. He also shows a create table syntax and highlights how effective it has been for him in his training sessions around Dev Ops.
https://www.kevinrchant.com/2021/10/12/t-sql-tuesday-143-two-of-my-personal-go-to-scripts/

Andy Mallon – Andy stores all of his useful scripts in a DBA database. It’s a popular approach and I was hoping someone would mention this. Andy goes beyond this though and has converted a lot of scripts into Stored Procedures. Whilst having a local scripts folder is great; if you can put your code into a stored procedure in a database which you deploy to all the servers you manage, there is no need to panic and find the scripts when the pressure is on. I must admit I love this approach and I’ll be downloading Andy’s database to look further into it.
https://am2.co/2021/10/t-sql-tuesday-143-my-favorite-short-scripts/

Jason Brimhall – Jason talks about all things endpoints here and I found the code examples so handy. I’ve already used them. Not only can you use them to validate your endpoints, but you can also use them to fix some issues as well. On a personal note, just what I needed.
http s ://bit.ly/3lAOMmF

Tom Zika – Tom shares loads of useful snippets including regex and t-sql. Wow – one regex snippet shows us how to find table variable declarations and turn them into temp tables. You could make a killing selling this one trick to consultants. Tom also shares a mega handy way to check permissions using impersonation as well as a great method to find referencing objects.
https://straightforwardsql.com/posts/short-code-examples/

Mikey Bronowski – Mikey shows us a handy way to execute multiple queries including dynamic ones and also tells us about agent_datetime() function. I have to admit I’ve never used that function but it looks so useful for when you are interrogating those msdb agent job tables. I will definitely be adding it my list. Finally, he shares a useful query for pulling back table data with his added enhancement (however a nice little plug for DBATools hints he now has a better way of approaching this).
https://bronowski.it/t-sql-tuesday-143-short-code-examples/

Todd Kleinhans – Todd focuses on Python and he is the only person to do so. I won’t give away his one liner but it’s just the sort of thing I was looking for. Are you feeling Zen?
https://toddkleinhans.wordpress.com/2021/10/12/t-sql-tuesday-143-import-this/

Mala Mahadevan – Mala shares some top class queries for interrogating query store. Query store has so much useful data that knowing how to get started querying it will be a big win for some.
https://curiousaboutdata.com/2021/10/12/tsql-tuesday-143-short-code-examples/

Chad Callihan – Chad mentioned 3 handy t-sql snippets and then shared a gem for keeping Brent Ozar’s First Responder Kit up to date. (Hint he uses DBATools). DBATools and FRK are amongst the most essential free tools for any DBA and beyond. If you run anything like sp_blitz or sp_blitzcache, it’s worth keeping it up to date and this method shows how to do it in only a few lines of code.
https://callihandata.com/2021/10/12/t-sql-tuesday-143-handy-short-scripts/

Deepthi Goguri – Deepthi shares some of the best of the rest by highlighting some of her favourite community scripts. From help with migrations to troubleshooting replication, it just goes to show that there’s no need to reinvent the wheel when there’s a perfectly good script out there that meets your needs.
https://dbanuggets.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Steve Jones – Did you know that you could get a tally table with just 4 key strokes? Steve shows you how, leveraging on the power of SQL Prompt by Redgate. This is taking snippets to a new level.
https://voiceofthedba.com/2021/10/13/t-sql-tuesday-143-short-code/

Jess Pomfret – Aloha to Jess who squeezes her entry in on time due to the Hawaiian time loophole. Want to find out if certain accounts are local admins on remote servers? Jess shares a quick and efficient method for finding this out. Being Jess, of course she is using PowerShell to make her life easier. I for one will be stealing this.
https://jesspomfret.com/t-sql-tuesday-143/

Eitan Blumin – Eitan takes the opportunity to link to some of his past blog posts which are full of useful code however he doesn’t stop there. With a new entry for T-SQL Tuesday, Eitan shows us how to move database files to a new location in Always On Availability Groups without breaking HADR. Ok at 374 lines, it’s a bit more than a snippet but it’s really great code so we’ll let that one slide.
https://eitanblumin.com/2021/10/13/t-sql-tuesday-143-powershell-move-db-files-alwayson-availability-groups/

Shane O’Neill – Shane also mentions agent_datetime(). It’s a cool function for converting the very user unfriendly ms format that we see in msdb tables. Shane points out it might not be the most efficient function however when you don’t have much data to bring back, it’s much quicker than rewriting the thing. Shane being Shane (Big Powershell fan) also points out a few great PowerShell commands for formatting and sorting and shows how they can be used in conjunction with other commands that yield really useful results.
https://nocolumnname.blog/2021/10/12/t-sql-tuesday-143-short-code-examples/

P.S. I’ve taken every bit of care to check my comments and on twitter but if I have missed your post, please let me know and I’ll include it immediately.