Formatting Shortcuts in SSDT for SSIS Packages

When developing SSIS packages the tasks and transformations tend to be misaligned after dragging them onto the designer from the SSIS toolbox. You can correct this by selecting Format > Auto Layout > Diagram.
Diagram1
When you have multiple packages in your SSIS project this can become quite tedious. There is a quicker way by setting up a keyboard shortcut to align everything up. I have found this to be the most useful shortcut I have come across while developing SSIS projects.

To set up the shortcut you can follow the steps below.

Step 1:
Select Tools > Options
Step1

Step 2:
Select Keyboard under Environment on the left in the pop-up. Then select Format.Diagram on the right hand side of the pop-up.
Step2

Step 3:
Click in the “Press shortcut keys” dialogue block and push the keys on your keyboard you would like to use for this option and click Ok. I recommend using CTRL + F, CTRL + D as it is easier to remember.
Step3

Now your shortcut is set up for you to use to align everything perfectly in each package!
TheWisestMind

Retrieving Data Types of All Columns in a Database

Thought I would share a quick post about retrieving all the data types of all columns in a database. This can be quite a handy script when you are comparing development and production databases to make sure all the data types match in each table.

The script displays the order in which the columns appear in the table, the schema the column in the table belongs to, whether is is null or not, the maximum length and the precision of the column.

SELECT  [Schema]			= SCH.name 
       ,TableName			= TBS.name
       ,column_name			= COL.name 
       ,COL.column_id
       ,DatatypeSchema		= SCHEMA_NAME(TYP.[schema_id])
       ,Datatypename		= TYP.name
       ,TYP.is_user_defined
       ,TYP.is_assembly_type
       ,COL.is_nullable
       ,COL.max_length
       ,COL.[Precision]
       ,COL.scale
FROM sys.columns    COL
JOIN sys.types      TYP ON COL.user_type_id   = TYP.user_type_id
JOIN sys.tables     TBS ON TBS.[object_id]    = COL.[object_id]
JOIN sys.schemas    SCH ON SCH.[schema_id]    = TBS.[schema_id]
ORDER BY SCH.name, TBS.name, COL.column_id

Hope this can be of great use to you!
StayYoungKeepLearning

Management Data Warehouse (MDW) Reports

Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. The descriptions in the reports are hopefully intuitively obvious enough for the casual observer to interpret the reports.

Server Activity History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Server Activity History
ActivityHistory1 ActivityHistory2

System CPU Usage
You can launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.
CPUUsage1 CPUUsage2

System Disk Usage
You can launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.
DiskUsage1 DiskUsage2

SQL Server Memory Usage
You can launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.
MemoryUsage1 MemoryUsage2

System Memory Usage
You can launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.
SystemMemoryUsage

SQL Server Waits
You can launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.
ServerWaits1 ServerWaits2

Query Statistics History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Query Statistics History or by clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.

Only 10 queries are listed here. I would recommend clicking on the biggest one first to check the Query Details in order to optimize it better.
QueryStatistics

Query Details
You can launch this report by clicking on any hyperlink text in any of the reports that has them. For example, SQL Server Observed Waits, SQL Server Active Requests, and the Query Statistics reports all have links to Transact SQL queries.

You can also click on the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.
QueryDetails1 QueryDetails2

Query Plan Detail
You can launch this report by clicking on the Query Plan line in the chart or the Plan # in the list in the Query Details report. The top part of the report is the same as the Query Details report, but the awesome demo portion is shown below.
QUeryPlanDetail
Hope this post was useful for you.

Choice to learn

Creating a “Run As” Account for Scheduled ETL Jobs

When working in large SQL production environments that have multiple jobs doing different things you might want the ETL jobs to run as another account other than SQL Server Agent.

This can help with security by limiting the amount of access the account has to the databases the packages in the ETLs are connecting to.

This is quite simple to set up. I recommend using a Windows AD account. In my example below of the setup I shall be demonstrating how to create an account to run the ETL jobs.

Step 1:
You will need to add the account to the Logins under Security on the server where the jobs and are executing and the ETL projects are deployed to.
Step 1

Step 2:
You will need to create a Credential under Security that links to the account you created. Right click on Credential and select New Credential…
Step 2

Step 3:
You will give the Credential a name and select the ellipse just below it so that you can select the Windows account you added under Logins in step 1 and click Ok. You will then type in the same password for the Windows AD account and click Ok. Now your credential is set up.
Step 3
Step 3a

Step 4:
Next you will need to add the Credential to the SSIS Package Execution Proxy so that it can execute SSIS packages on the server. Expand the SQL Server Agent node, expand the Proxies node and right click on SSIS Package Execution and select New Proxy…
Step 4

Step 5:
Give your Proxy a name. I choose to use the same name as the Windows AD account throughout the entire setup. Then select the ellipse next to Credential so that you can add the Credential you created in step 3 and click Ok.
Step 5

You will notice that SQL Server Integration Services Package is checked under Active to the following subsystems. This allows the account to execute SSIS packages. You can click Ok and the setup is complete.
Step 5a

You will now see the Proxy you created under SSIS Package Execution.
Step 5b

Step 6:
When you create the new job and in the steps section set the type to SQL Server Integration Services Package, you will then be able to click on the drop down below Run as and you shall see the Proxy account you set up. You can select it and then your job will run as that account.
Step 6

An important note to remember is that the Windows AD account you are using will need Read/Write access on the databases that are used as connections in the ETL packages.

LearnWithPleasure