Test of Embedding Power BI

https://app.powerbi.com/view?r=eyJrIjoiMWQzZTIwYzgtNTViYS00NmVjLTk3ZDMtOWM0MGMxMzViZjY2IiwidCI6IjgzZmYxNGE2LWViNmItNDI1MC1iNzQ1LTIxYmVhNjYyNTNkYyIsImMiOjN9

Advertisements

SSIS Connection Not Found Error but Package still runs

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.

 

Quickest way to find the SQL Server Templates Location?

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 BrowserSQL Server Templates

Select Search

Search Templates

Type “a” into the search window

search

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.

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”

I ran into this message when attempting to delete a SQL Job.  Somehow a  subplan was orphaned and not allowing me to delete the job.

First I got the Job_id by right clicking on the job and scripting the drop.

1-30-2017-8-43-58-am

The script will contain the job_id

1-30-2017-8-47-01-am

Now I searched for the Job_id in the System Maintenance sub plans.

1-30-2017-8-51-56-am

I then used the delete statement above to remove the sub plan.  Then I was able to go back to the jobs and delete the job.

Thanks to Moshin’s DBA Blog for help on this.   Moshin’s DBA BLog

Using Contained Database to allow users to be copied with SQL Database

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.

 

 

How to Script All SQL Jobs IN SSMS

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

Removing or Replacing Bad Dates in Microsoft SQL

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.