Get familiar with these reports. Make sure you understand what’s available in them. Spend some time on this. Run the report, run some tests that you think will affect the report, run the report again. Move on to the next report.

Why do I think you need to spend some time here? There is a lot of valuable information, and it’s laid out in a very easy to read manner. You can waste a lot of time searching for some of this information (or not even realize it’s available) if you don’t spend a bit of time getting familiar with what’s available.

Have fun!

• The BULK INSERT command. This is a T-SQL command so can easily be added to other T-SQL code to perform some pretty impressive tasks.
• The OPENROWSET/OPENDATASOURCE/OPENQUERY commands. These are also T-SQL commands and can easily be added to other T-SQL code to perform some pretty impressive tasks. They also handy for linking data from other data sources into a T-SQL Query. Spend some time with these. Note: They are similar but different commands. Pay attention to the differences so you have a better idea when to use each.
• BCP – A command-line tool used to import and export data. Great for automation.
• Import/Export wizard – Creates an SSIS package that can import or export data. This can be used as a one-time import/export or the package can be saved for later use/editing.
• SSIS – Create your own SSIS package manually. This is more work than using the wizard but you have an amazing amount of control. This is also great for automation.
• Powershell – PoSH can do anything. Really. I’ve used it to wash my dishes on a couple of occasions. Create a script to get the data from a table and export it. Another great tool for automation.
• The coding language of your choice. – Same as Powershell really. If you have some development background, pick a language and write yourself a script to export a table.
• Other methods – Any other methods you can think of.

• BCP – A command line tool used to import and export data. Great for automation.
• Import/Export wizard – Creates an SSIS package that can import or export data. This can be used as a one time import/export or the package can be saved for later use/editing.
• SSIS – Create your own SSIS package manually. This is more work than using the wizard but you have an amazing amount of control. This is also great for automation.
• Save or copy and paste the data from the output in SSMS. – This is a nice quick and dirty way to grab data.
• Powershell – PoSH can do anything. Really. I’ve used it to wash my dishes on a couple of occasions. Create a script to get the data from a table and export it. Another great tool for automation.
• The coding language of your choice. – Same as Powershell really. If you have some development background, pick a language and write yourself a script to export a table.
• Other methods – Any other methods you can think of.

• The database Master Key (yes encryption is involved)
• The password used to create the catalog
• The database/catalog itself (duh)
• The server principal ##MS_SSISServerCleanupJobLogin##
• The stored procedure sp_ssis_startup in the master database
• The SQL Agent job SSIS Server Maintenance Job

Now, to be fair a lot of this doesn’t have to be backed up/scripted out ahead of time. In my personal opinion the easiest way to do this is:

1. Create an SSIS catalog on the new server.
2. Restore the SSIS catalog database from the old server over the newly created catalog.
3. Restore the master key either with the backup of the master key or the original password use to create the catalog on the old server.

If you work in a large company with multiple versions of SQL running, and multiple teams creating SSIS packages for those different versions it can be a real mess.

I have a table I try to maintain the different version of packages etc to help out with the confusion.

Fortunately though, one of the big changes (in my mind) of SSDT is the ability to change SSIS versions. Prior to this, you would have to have scripted the package out in BIML and re-created it in the correct version.

So to start with I installed SSDT 17, created a project, and practiced deploying to the SSIS catalog on a 2016 instance. (Yes I wrote 3 posts for no purpose other than to lead up to this one. In fact I’ll probably do one more in a week or two because of this also.)

So starting with the project I created for SQL 2016:

Right click on the project and select properties.

Under Configuration Properties -> TargetServerVersion you’ll see a drop down allowing you to set the version to SQL 2012 to SQL vNext. I’m going to select 2012.

We do get a warning at this point. If anything in our project doesn’t exist in 2012 we will have problems (not surprising, but thanks for the warning).

Next, we build the project.

Now if you take a look at the dtsx file (remember that the ispac file is just a zip file) you’ll see that the packageformatversion is now 6.

Just to be certain let’s import and run it. I know I already created the ispac file but this time I’m going to do the deployment wizard directly from SSDT. Warning: if you are using an ispac file and hand it off to someone using anything lower than SSDT 17 but higher than the version you exported the deployment wizard will upgrade the package when they deploy it which can be a rather unpleasant surprise.

And here it is. FYI You can confirm the package format version by querying SSISDB.internal.packages. Now time to run the package!

Since I had this pointing to my 2016 instance and AdventureWorks2014 I am going to modify those properties of the connection manager.

And Success! We created the package for SQL 2016 then changed the version and deployed and ran it on a SQL 2012 instance!

The first thing to do is create the SSIS catalog on your instance (if it doesn’t already exist) and of course, open up your project.

I’ve created a very simple package that just exports the employee table from AdventureWorks2014 to a CSV just so I have something in my project.

Build -> Build Solution

And an ispac file is built. Assuming everything went well you’ll see the path at the bottom of the Ouput window.

Take that path and find the file.

Now the interesting thing is that an ispac file is just a zip file. If you rename it to .zip and open it you can see what’s inside it. Including the .dtsx file(s) for the packages.

Now try double clicking on the ispac file.

In my case ispac wasn’t mapped to the correct program so I had to manually associate the extension with the correct program. In this case that is ISDeploymentWizard.exe. It was located in C:\Program Files\Microsoft SQL Server\130\DTS\Binn on my machine. Of course, in my case for some reason the mapping didn’t work properly and I kept getting the command line (yes it appears you can run this via command line) help screen instead of the GUI. So I have to open the deployment wizard another way. I just did a quick search on Deployment Wizard and opened it manually. Note: You can open it directly through an SSMS connection to integration services, or SSDT in the project (Build -> Deploy) which would avoid the whole need to create the ispac file at all. But in this case, I’m assuming we were just given an ispac file to deploy.

Now we fill out the wizard.

(If the double click thing worked then the source path is already filled in.

If you just created your SSISDB catalog then you’re going to have to create a folder to store your project in.

Now review what you’ve done (yeah, I don’t usually pay much attention to this page either). One useful thing you’ll notice on this page is that at the top of the summary the command line parameters for ISDeploymentWizard.exe are displayed in case you want to automate the process. Finally we hit Deploy.

Yay! It’s deployed! And we can now see it in SSISDB!

Menu File -> New -> Project

Here we have a number of options. We want to select the template Integration Services Project. Pick a name for the project and solution (default for the solution name is the project name). Then depending on if you check Create directory for solution or not you will either find your sln file in one of the following two directories:

• Checked: C:\Users\kenne_000\Documents\Visual Studio 2015\Projects\FirstSSDTProject\
• Un-Checked: C:\Users\kenne_000\Documents\Visual Studio 2015\Projects\

And once the project is done being created (and it does take a few minutes) it is automatically opened and ready to have stuff added to it.

Step One and Post One!

Something that has changed over the last few years is that the install for the SQL Server Engine, SSMS (SQL Server Management Studio for writing queries and stuff) and SSDT (SQL Server Data Tools for building SSIS packages and stuff) have all become separate. The latest versions of SSMS and SSDT are v17 and are free to download, as is the SQL Server Developer edition.

• SQL Server Developers Edition (now free if you sign up for Visual Studio Dev Essentials)
• SQL Server Management Studio – Supports SQL 2008 – 2017 (although if you need to connect to 2000 or 2005 I’m betting you can connect a query window just fine).
• SQL Server Data Tools – Built on VS 2015 and compatable with SQL 2005 – 2017.

 
So for the purposes of this post, I’m going to download SSDT.

Once the download is done (it’s small) run it.

I’m unchecking the Analysis Services tools, and while I have no intention of work with Reporting Services I don’t have an option there. You uncheck SSRS it unchecks SSDT, you uncheck SSDT and it unchecks SSRS. Basically, they are the same thing. Hit next, go through the normal legal acceptance page, and we get to the install page.

And now we wait (I’m going to bed, but my guess is it will be done by the time I get back tomorrow afternoon).

Ok, I’m back. The install appeared to go pretty quickly in the few minutes I watched it before going to bed. Either way, it installed!

The install is asking that you restart your machine so go ahead and do that now.

Once you’ve rebooted (or if you decide to skip it) go ahead and try to run SSDT for 2015.

Here you can sign into your Microsoft account and associate it with Visual Studio.

And finally, we are in!

In case you are interested SSDT is basically a set of modules for Visual Studio. If you have Visual Studio installed then the modules are added. If you don’t have it installed then a minimal “Integrated Shell” version of Visual Studio 2015 will be installed.

Adding the original package to the new project.

Changes made to the old package in the new project.

But the changes just aren’t showing up. You back and look again and sure enough, the changes you made are there. You run your job (or whatever) and still the change aren’t taking effect. So what happened?

When you add an existing package to a project it makes a copy of the package.

The old package, in the old project.

Old package location in the new project.

In order for the changes to take effect you need to move the .dtsx file (the package) back to the original location. The easiest way I’ve found to do this is just a simple file copy.

Obviously this is only an issue with Package Deployment not Project Deployment but you would be amazed how often this has come up.

So what can you do? We took a three tiered approach.

1. First we keep enough licenses to cover the vast majority of our production instances. The high profile ones and the problem ones.
2. Then we added 2-3 additional floating licenses. We move these particular licenses around when we have an issue on a server. We don’t have the long term history but we can spotlight a server for a few days or weeks to get a feel for what’s going on.
3. Last but not least we have a light weight, home grown, monitoring system. This system is pretty simple and that’s by design. We aren’t trying to be perfect here, just better than nothing. Remember we have the professional system for anything really important.

 
In case anyone is interested we took an SSIS package that loops through a list of instances and used it to collect some information. Here is an example of one of the queries we use.

SELECT CAST(@@ServerName AS VarChar) AS Server_Name, 
	CAST(sysdatabases.name AS VarChar) AS Database_Name, CAST(sysdatabases.cmptlevel AS Int) CtmpLevel, 
	CAST(ISNULL(DatabasePropertyEx(sysdatabases.name,'Status'),'Removed') AS VarChar) AS Status,
	CAST(DatabasePropertyEx(sysdatabases.name,'Updateability') AS VarChar) AS Updateability,
	CAST(DatabasePropertyEx(sysdatabases.name,'UserAccess') AS VarChar) AS UserAccess,
	CAST(DatabasePropertyEx(sysdatabases.name,'Recovery') AS VarChar) AS Recovery,
	MAX(CASE WHEN type = 'D' THEN backup_finish_date ELSE NULL END) AS LastFull,
	MAX(CASE WHEN type = 'I' THEN backup_finish_date ELSE NULL END) AS LastDifferential,
	MAX(CASE WHEN type = 'L' THEN backup_finish_date ELSE NULL END) AS LastLog,
	-- I'm not worrying about the following types of backups although that could
	-- obviously be pulled just as easily.
	--	F = File or filegroup 
	--	G = Differential file 
	--	P = Partial 
	--	Q = Differential partial 
	SUSER_SNAME(sid) AS DBOwner
FROM master.dbo.sysdatabases sysdatabases
LEFT OUTER JOIN msdb.dbo.backupset backupset 
	ON backupset.database_name = sysdatabases.name
--		AND server_name = ?
WHERE sysdatabases.name <> 'tempdb'
GROUP BY sysdatabases.name, sysdatabases.cmptlevel, 
	DatabasePropertyEx(sysdatabases.name,'Status'),
	DatabasePropertyEx(sysdatabases.name,'Updateability'),
	DatabasePropertyEx(sysdatabases.name,'UserAccess'),
	DatabasePropertyEx(sysdatabases.name,'Recovery'),
	SUSER_SNAME(sid)

This returns a list of databases on the instance, some various settings and when the most recent backups were taken. We can then report on this information across all instances. This means that we get a single report with either an all clear, or a list of problems. Some of the reports we have include lists of any DBs that are marked READ_ONLY, RESTRICTED, OFFLINE, have no valid owner, have no FULL backup in the last day or week (depending on the backup schedule), no log backup in the last 12 hours (again depending on the schedule), etc. Some of the reporting can get a bit complicated and we collect quite a bit more information than this but this does give you a general overview of what’s we are doing. It takes a bit of effort to set up and build your report but it’s highly worth it. I think I spent about a month building the current system and it’s probably saved me 10 times that much over the last few years.

Thanks to Cathrine Wilhelmsen (b/t) for hosting this months T-SQL Tuesday with a subject of Monitoring!