I had a package that I copied and updated to a new connection. The package runs successfully. But when I open it it shows the following error.

Error loading Master DatawarehouseETL.dtsx: The connection “{42E5886D-F897-4366-9794-02AF8E32198D}” is not found.

Everything runs. So what is the problem?

The problem is I have steps that are disabled. SSIS still checks the connections even though the step is disabled. The package runs since the step does not run. I fixed the connection and the error goes away.

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

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 demodbSet CONTAINMENT = PARTIAL;

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

USE demodbCREATE USER TotallyContainedWITH 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