If you use SQL Server Templates,  you probable will want to backup up your scripts or copy the files to a new computer so you don’t lose all of your work.  The problem is the location of the files can vary depending on your version of SQL Server and no one can memorize the folder location.   However there is a quick way.

Right click on SQL Server Templates folder in the Template Browser

Select Search

Type “a” into the search window

Notice the search goes out to the location of the templates. In my case it is

C:\Users\rileyt\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\SQL

Now I can go out to the folder,copy and backup my templates.

Since SQL Server 2012 there is a feature of SQL server that helps make copying databases easier.  Its called contained database.  This will allow a database to be copied along with the user logins.

Normally user login information is stored in the Master Database on SQL Server.  That is why when you copy a database to another server the logins are missing and have to be scripted separately.   With the Contained Database this is no longer necessary.

First the feature has to be enabled on the server

sp_configure 'contained database authentication', 1;
RECONFIGURE;

Next the individual database has to be set to support containment.

Alter DATABASE demodb
Set CONTAINMENT = PARTIAL;

Now we can add a user to the database with the following;

USE demodb

CREATE USER TotallyContained

WITH PASSWORD = 'A)$*LaJSFS';

The TotallyContained user only exists in the demodb database.  If I were to copy the database to another server, the TotalContained user will show up with sames access rights simplifing the process of moving or copying the database.

The fastest way to script all of the SQL jobs for SQL Server is to go to VIEW menu and select the Object Explorer Details.  Now navigate to SQL Server Agent in the Object Explorer and select Jobs.  In the Object Explorer Details window, select all of the jobs and right click.  Select Script Job as,  Create To, New Query Editor Window.  This will return a script that creates all of the jobs.j

Sometimes you receive data from other sources that are suppose to be dates but are not entered correctly.  The roman classic I was born in ’12/15/0010′, or the time traveler ’03/23/99999′.  Sometimes its just junk like ‘tbd’ etc.  This is where try_cast or try_convert is essential.

Try_Cast or Try_Convert has been available since SQL 2008.  These will prevent an error to stop your query.

select TRY_CAST('1/1/2016' as date)

select TRY_CONVERT(date,'1/1/2016')

These will both successfully return a date of 1/1/2016.  But if you try to process the following:

select try_Convert(date,'1/41/2016')

This will return a null instead of an error.  You can wrap the try with a coalesce and return a default value

select coalesce(try_Convert(date,'1/41/2016'),'1/1/1900')

This will return 1/1/1900 and your query completes.

Often I find myself wanting to choose dynamically between different steps in  a package or being able to turn off a step without having to actually modify the procedure.

This can be accomplished dynamically by disabling a step via a variable or parameter.  In this example I have a loop that processes a set of college clubs.  The query loads a list of clubs.  I would like to have a different process for The Physics and Astronomy club.  Maybe eventually each club will have a different process of its own.  One way to make this happen is by controlling what is enabled.  Below is the package.

The SQL Load List of Files loads a table of the clubs to a source object that is used in the for each loop.   The Name of the club is assigned to  a variable called ClubName.

I then modify the step called Physics and Astronomy.  Open the properties of the Physics and Astronomy step and add an expression for the Disable feature.

I test to see if the ClubName is not equal to Physics and Astronomy Club. If it isn’t then Disable is set to true and this step will not run.  If the ClubName is set to Physics and Astronomy Club then the step will continue.   Since I only have one other step for all other clubs.  I set the Disable for this step when ClubName is equal to Physics and Astronomy Club.  

Now as I loop through the clubs,  the Physics and Astronomy Club step will only run for that club and All Other Clubs step will run for everything else.

The YYYYMMDD format is very useful in organizing data for filenames, sorting, etc.

Here is one way to generate that  format.

SELECT CONVERT(VARCHAR(10), getdate, 112)

This is the method that works well in sql.  If you want to do the equivalent in an expression in SSIS then you can use the Year,Month,Day functions.

Year(getdate()) *10000 + MONTH(getdate())*100+ DAY(getdate())

This will result in a integer value in the form of YYYYMMDD.   You can then convert to a varchar.