Showing posts with label SSMS. Show all posts
Showing posts with label SSMS. Show all posts
(2018-Jan-13) You never know how the weather will behave itself, especially when the temperature gets dramatically changed from +10 to -10 within a day. During this Christmass and New Year holidays, it was very cold here in Canada. So I thought to explore some open data set that may contain historical temperature records and if possible geo-coordinates to locate those thermal data points to.

About two years ago I already had created a blog post “Excel Power Map vs. Power BI Globe Map visualization” based on the Berkley Earth datasets - http://berkeleyearth.org/data/. At that time I was probing the 3D Globe Power BI visualization, the one that you can use and rotate it right within your report.

This time I thought to check and compare heat map visualizations in Power BI. Currently, there are two ways to see variously heated or cooled geo areas in Power BI, either using a standard ArcGIS Map visualization or a custom Power BI Heatmap visualization from the Office store - https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381072?src=office&tab=Overview

The Berkley Earth dataset for this data case exercise came in a form of multiple flat files:


I only used the following fields from the data.txt file:
- Station ID, 
- Date, 
- Temperature (C)
  
And from the site_detail.txt file I extracted the following fields to provide more geo details for each of the weather stations that there was temperature data point available:
- Station ID, 
- Station Name, 
- Latitude, 
- Longitude, 
- Country

And with some minor data transformation, I was able to build my Power BI data model with two tables:



Just a side note on a data extract technique, SQL Server Management Studio (SSMS) version 17.3 or later now has a new feature to Import Flat File along with the common Import and Export Data, you can read more about it here - https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard



So, after populating the data: temperature ~15.5 million records for the date time range from the year 1763 till 2017 and close to 43 thousand of various weather stations around the globe, I was able to place it into my Power BI report and test both types of the Heat Map visualizations. I additionally filtered my dataset then to show the Canadian weather stations landscape only and chose the year 2017 for the most recent data.



I could also change colors for both visualizations, either by manual selection or by specifying a color palette in the edit mode:







ArgGIS Map visualization has a lot of detailed settings that you can choose and apply for your various geo-analytics scenarios (and not only your heat map ones), however, the custom Heatmap visualization brings even more flexibility for adjusting your visualization that ArcGIS Map doesn't have.

So, there are no winners or loosers and I can easily use them both!
Case
My company uses different Windows users for the various DWH environments (Development, Test, Acceptance & Production). How can I connect to a SQL Server instance in SQL Server Management Studio (SSMS) with a different user so that I can still use Windows Authentication?
Windows Authentication














Solution
The quick solution is to hold the Shift-key while right clicking the SSMS shortcut in the start menu. Then the 'Run as different user' option appears, which allows you to enter different credentials. After that the user name field for the Windows Authentication changes.
Right click the shortcut and choose Run as different user



Now you can run SSMS with a different account































Now the User name changes















Runas shortcut
A more permanent solution is to create a new shortcut with a runas command in it. Instead of the standard SSMS command (see Target):
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe" 

you use:
RUNAS /user:myDomain\myUserName /savecred "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
(110 = SQL 2012 / 120 = SQL 2014 / 130 = SQL 2016 / 140 = SQL 2017)

  • The /user: option allows you to use a different user.
  • The /savecred option will save the password after the first time (not in  Windows 7 Home)
  • For more options check out this site or execute "runas /?" in the command prompt to show all options. Some forums/blogs recommend the /netonly option to only use the provided user for remote access, but that often doesn't work causing SSMS not to start.

Change Target field and optionally the Comment






















To finish this off: click on the Change Icon button and browse to SSMS.exe to select the familiar icon.
Change icon to finish off the shortcut






















The first time you will see a command prompt where you have to enter your password. If you added the /savecred option then the second time you will only see a short 'flash' of the command prompt and then SSMS will start. You could get rid of it by changing the Run property to minimized (after the first execution).
Enter password









SSMS commandline options
You can even extend this solution with some extra options for SSMS itself. Like providing the instance and database name.
SSMS command line options
















Summary
In this post you saw how you can start up SSMS with a different domein user so you can still use Windows Authentication. This not only works for SSMS, but for other programs like Visual Studio as well:
RUNAS /user:myDomain\myUserName /savecred "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe"
Case
What can you do with the Data Streaming Destination in SSIS and how does it work? It only has an 'Advanced' editor with very little explanation.
Editor Data Streaming Destination
























Solution
You probably never used or even saw this destination component before because it was a separate download in SSIS 2012 and for SSIS 2014 I couldn't even find the download page. But now for SSIS 2016 it's one of the standard toolbox items. And even now it's in the wrong toolbox section: you will find it under Common instead of Other Destinations. That can be easily solved by moving it the appropriate section of the SSIS Toolbox.
Moving Data Streaming Destination


























The Data Streaming Destination allows you to query its output via a linked server connection on the SSISDB. If certain sources or transformations are very hard with standard TSQL then you could solve it in an SSIS package. After that you can query its output with TSQL. Yes I know almost every thing is possible with TSQL when you for example use CLR stored procedures, but SSIS is just a visual alternative.


1) Data Flow Task
For this example I will use a package with one very basic Data Flow Task with a Flat File Source Component, a Derived Column and the Data Streaming Destination.
Simple Data Flow with Data Streaming Destination






















2) Data Streaming Destination
When you add the Data Streaming Destination and edit it, you will get the Advanced Editor. It only allows you to choose the input columns and you can change the name of an automatically generated identity column (see previous screenshot). For this example I pass through all columns and leave the id column name unchanged.
Pass through all columns

























3) Saving and deploying
Now I need to deploy the package(s) to the SSIS Catalog. You could add parameters and create an SSIS Catalog environment to fill them on package start. I will skip that for this basic example.
Deploying packages to SSIS Catalog

























4) Data Feed Publishing Wizard
Now start the SQL Server Integration Service Data Feed Publishing Wizard from the start menu and choose which package to execute in your view. You also need to provide the name and location of your view. For Linked Server validation errors go to step 4b.
(C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDataFeedPublishingWizard.exe)
SSIS Data Feed Publishing Wizard


























4b) Error: The Allow Inprocess option for the OLE DB Provider is not enabled.
When you get a Linked Server error during validation you need to enable "Allow inprocess".
The Allow Inprocess option for
the OLE DB Provider is not enabled.






















Go to SSMS and connect to your server where the SSISDB is running. Expand Server Objects, Linked Servers, Providers and then right click SSISOLEDB and choose Properties. In the Provider Options enable "Allow inprocess" and click OK. After that Rerun volition in the Wizard.
Provider Options, Enable "Allow inprocess"


















4c) Error: The are more than one Data Streaming Destination components in the package and only one of them can pass data to a SQL Server database
The error says it all: You can only have one Data Streaming Destination! Remove the others first and rerun validation.
The are more than one Data Streaming Destination components
in the package and only one of them can pass data
to a SQL Server database

























5) Testing the view
Go to SSMS and execute your newly created view. You probably have to test you patience a little bit because it's not very fast. It first has to execute the package which takes a couple of seconds. I haven't found a good purpose for a real-life situation yet, but may be you can use it to create a (nearly) real time data feed from a webservice for your PowerBI report. Or..... let me know in the comments where you used it for.
Querying the new view 
















If you don't want to use the wizard you could just do it with TSQL:

USE [SSISJoost]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[MyDataStream] AS SELECT * FROM OPENQUERY([Default Linked Server for Integration Services], N'Folder=DataStream;Project=DataStream;Package=DataStream.dtsx')
GO
Case
Is there an easy way to create and populate an age dimension with age groups?

Solution
Creating an age dimension is usually done once and probably not in SSIS, but with a TSQL script.
For each new assignment I use a script similar to this and adjust it to the requirements for that particular assignment.

-- Drop dimension table if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_age]') AND TYPE IN (N'U'))
BEGIN
DROP TABLE [dbo].[dim_age]
END

-- Create table dim_age
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dim_age](
[dim_age_id] [int] IDENTITY(-1,1) NOT NULL,
[Age] [smallint] NULL,
[AgeGroup1] [nvarchar](50) NULL,
[AgeGroup1Sort] [int] NULL,
[AgeGroup2] [nvarchar](50) NULL,
[AgeGroup2Sort] [int] NULL,
CONSTRAINT [PK_dim_age] PRIMARY KEY CLUSTERED
(
[dim_age_id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Enter unknown dimension value (in case a person's date of birth is unknown)
INSERT INTO [dbo].[dim_age]
([Age]
,[AgeGroup1]
,[AgeGroup1Sort]
,[AgeGroup2]
,[AgeGroup2Sort])
VALUES
(-1
,'Unknown'
,0
,'Unknown'
,0)
GO

-- Enter all ages
declare @age smallint;
set @age = 0;

-- Loop through ages 0 to 130
WHILE @age < 131
BEGIN
INSERT INTO [dbo].[dim_age]
([Age]
,[AgeGroup1]
,[AgeGroup1Sort]
,[AgeGroup2]
,[AgeGroup2Sort])
VALUES
(@age
-- Use the common age groups/categories of your region/branch/industry
-- This is just an example
, CASE
WHEN @age < 15 THEN '0 till 15 year'
WHEN @age < 25 THEN '15 till 25 year'
WHEN @age < 35 THEN '25 till 35 year'
WHEN @age < 45 THEN '35 till 45 year'
WHEN @age < 55 THEN '45 till 55 year'
WHEN @age < 65 THEN '55 till 65 year'
ELSE '65 year and older'
END
-- Add value to sort on in SSAS
, CASE
WHEN @age < 15 THEN 1
WHEN @age < 25 THEN 2
WHEN @age < 35 THEN 3
WHEN @age < 45 THEN 4
WHEN @age < 55 THEN 5
WHEN @age < 65 THEN 6
ELSE 7
END
, CASE
WHEN @age < 19 THEN 'Juvenile'
ELSE 'Mature'
END
-- Add value to sort on in SSAS
, CASE
WHEN @age < 19 THEN 1
ELSE 2
END
)

-- Goto next age
set @age = @age + 1
END

The result: filled age dimension
























How could you use this dimension?
A while ago I also posted an example to create and populate a date dimension. So now you can combine those in a datamart. I have an employee table and an absence table with a start- and enddate.
Employee table

Absence table



















I will use the date dimension to split the absence time periods in separate days and then calculate the employee's age of each day of absence. This will go in to a fact table and then I can use the age dimension to see absence per age group.
-- Split absence time periode in separate days, but go back 2 years max and 1 year forward if end date is unknown
SELECT Absence.AbsenceId
, Absence.EmployeeNumber
-- Date of absence
, dim_date.Date as AbsenceDate
, Absence.ReasonCode
-- Calculation of age at time of absence
, DATEDIFF(YEAR, Employee.DateOfBirth, dim_date.Date)
-
(CASE
WHEN DATEADD(YY, DATEDIFF(YEAR, Employee.DateOfBirth, dim_date.Date), Employee.DateOfBirth)
> dim_date.Date THEN 1
ELSE 0
END) as Age
FROM EmployeeApplication.dbo.Absence
INNER JOIN EmployeeApplication.dbo.Employee
on Absence.EmployeeNumber = Employee.EmployeeNumber
INNER JOIN DM_Staff.dbo.dim_date
on dim_date.Date
-- change start date to lower bound if it's below it
BETWEEN CASE WHEN YEAR(Absence.AbsenceStartDate) >= YEAR(GETDATE()) - 2 THEN Absence.AbsenceStartDate
ELSE DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 2, 0) END
-- change end date to upper bound if it's null
AND ISNULL(Absence.AbsenceEndDate, DATEADD(yy, DATEDIFF(yy, 0, getdate()) + 2, -1))
-- Filter absence record with an enddate below the lower bound (perhaps a bit superfluous with the inner join)
WHERE YEAR(ISNULL(Absence.AbsenceEndDate, GETDATE())) >= YEAR(GETDATE()) - 2


Result of query that can be used in a fact package





















fact absence


























Note: this is a simplified situation to keep things easy to explain.
Case
Parameters and environments are a handy new feature in SSIS 2012, but creating environments with variables and referencing the environment to the project and the variables to the parameters is a lot of effort. No problem if you have to do it once, but if you deploy your packages through the DTAP servers you have to redo it on every single server. Is there a way to deploy environments?

No export option for Environments















Solution
Although it is easy to deploy packages through DTAP servers. Deploying environments is not  possible. That could get a little tiresome especially if you have a lot of parameters in your project.

I have created a script/stored procedure that uses the project identifier as input, loops through the tables of the SSISDB and prints SQL-statements to execute SSISDB Stored Procedures.
How to get the project identifier
















For every environment, variable and reference to this project it will generate creation scripts. You can copy these stored procedure calls, adjust the values where needed and execute them on the next environment.
Execute script then copy and execute output

























Release notes
  1. There are no checks or validations in this version. So it doesn't check whether objects already exist before calling the SSISDB Stored Procedures.
  2. First deploy the SSIS project before executing the stored procedure calls on the next server.
  3. Make sure the folder name is equal on the new server or change it in the stored procedure calls.
  4. Make sure to check sensitive variables values (you can't get the value from the tables).


Here is how you call the stored procedure to generate the scripts. If you don't want to add the new stored procedure then you could just use the script inside the stored procedure.
exec catalog.deploy_environment 11

Add the following stored procedure to the SSISDB or use the TSQL code inside.
USE SSISDB;
GO

-- USE AT OWN RISK! This stored procedure was created on the SSISDB on SQL Server version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)


-- Drop any previous versions of this stored procedure
IF OBJECT_ID ( 'catalog.deploy_environment', 'P' ) IS NOT NULL
DROP PROCEDURE catalog.deploy_environment;
GO

-- project_id is the identifier in the properties of a project
CREATE PROCEDURE catalog.deploy_environment
@project_id bigint
AS

-- Internal variables used within the cursor
Declare @environment_name as nvarchar(128);
Declare @project_name as nvarchar(128);
Declare @folder_name as nvarchar(128);
Declare @environment_folder_name as nvarchar(128);
Declare @reference_type as char(1);
Declare @folder_id as bigint;
Declare @environment_description as nvarchar(1024);
Declare @environment_id as bigint;


DECLARE ref_environment_cursor CURSOR FOR
-- Loop through all in the project referenced Environments
SELECT r.environment_name
, p.name as project_name
, ISNULL(r.environment_folder_name, f.name) as folder_name
, ISNULL(r.environment_folder_name, f.name) as environment_folder_name -- for @reference_type = A
, r.reference_type as reference_type
, f.folder_id
, e.description as environment_description
, e.environment_id
FROM [SSISDB].[internal].environment_references as r
INNER JOIN [SSISDB].[internal].projects as p
on r.project_id = p.project_id
INNER JOIN [SSISDB].[internal].folders as f
on p.folder_id = f.folder_id
INNER JOIN [SSISDB].[internal].environments as e
on e.folder_id = f.folder_id
and e.environment_name = r.environment_name
WHERE r.project_id = @project_id

OPEN ref_environment_cursor

FETCH NEXT FROM ref_environment_cursor
INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;

Print '-- Create scripts for deploying enviroments'
Print '-- Project ID: ' + CAST(@project_id as varchar(5)) + ' - Project name: ' + @project_name
Print ''

WHILE @@FETCH_STATUS = 0
BEGIN
-- Create environment
Print '-- Create environment: ' + @environment_name
Print 'EXEC [SSISDB].[catalog].[create_environment]'
Print ' @environment_name=N''' + @environment_name + ''''
Print ', @environment_description=N''' + @environment_description + ''''
Print ', @folder_name=N''' + @folder_name + ''''
Print 'GO'
Print ''

-- Create reference from environment to project. Relative or Absolute
Print '-- Reference environment ' + @environment_name + ' to project ' + @project_name
IF @reference_type = 'R'
BEGIN
-- Reference Relative
Print 'Declare @reference_id bigint'
Print 'EXEC [SSISDB].[catalog].[create_environment_reference]'
Print ' @environment_name=N''' + @environment_name + ''''
Print ', @reference_id=@reference_id OUTPUT'
Print ', @project_name=N''' + @project_name + ''''
Print ', @folder_name=N''' + @folder_name + ''''
Print ', @reference_type=R'
Print 'GO'
Print ''
END
ELSE
BEGIN
-- Reference Absolute
Print 'Declare @reference_id bigint'
Print 'EXEC [SSISDB].[catalog].[create_environment_reference]'
Print ' @environment_name=N''' + @environment_name + ''''
Print ', @environment_folder_name=N''' + @environment_folder_name + ''''
Print ', @reference_id=@reference_id OUTPUT'
Print ', @project_name=N''' + @project_name + ''''
Print ', @folder_name=N''' + @folder_name + ''''
Print ', @reference_type=A'
Print 'GO'
Print ''
END


-- Internal variables used within the cursor
Declare @environment_value as sql_variant--nvarchar(max); -- SQL_VARIANT
Declare @variable_name as nvarchar(128);
Declare @sensitive as bit;
Declare @variable_description as nvarchar(1024);
Declare @variable_type as nvarchar(128);

DECLARE environment_var_cursor CURSOR FOR
-- Loop through all in the variables of the active environment
SELECT CAST(ev.value as varchar(255)) as environment_value
, ev.name as variable_name
, ev.sensitive
, ev.description as variable_description
, ev.type as variable_type
FROM [SSISDB].[catalog].environment_variables as ev
WHERE environment_id = @environment_id

OPEN environment_var_cursor

FETCH NEXT FROM environment_var_cursor
INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Environments variables
Print '-- Create variables for environment: ' + @environment_name + ' - ' + @variable_name

-- Variable declaration depending on the type within the environment
IF @variable_type = 'Boolean'
BEGIN
Print 'DECLARE @var bit = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Byte'
BEGIN
Print 'DECLARE @var tinyint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'DateTime'
BEGIN
Print 'DECLARE @var datetime = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Decimal'
BEGIN
Print 'DECLARE @var decimal(38,18) = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Double'
BEGIN
Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Int16'
BEGIN
Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Int32'
BEGIN
Print 'DECLARE @var int = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Int64'
BEGIN
Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'SByte'
BEGIN
Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'Single'
BEGIN
Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'String'
BEGIN
Print 'DECLARE @var sql_variant = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'UInt32'
BEGIN
Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END
ELSE IF @variable_type = 'UInt64'
BEGIN
Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
END

Print 'EXEC [SSISDB].[catalog].[create_environment_variable]'
Print ' @variable_name=N''' + @variable_name + ''''
IF @sensitive = 0
BEGIN
Print ', @sensitive=False'
END
ELSE
BEGIN
Print ', @sensitive=True'
END
Print ', @description=N''' + @variable_description + ''''
Print ', @environment_name=N''' + @environment_name + ''''
Print ', @folder_name=N''' + @folder_name + ''''
Print ', @value=@var'
Print ', @data_type=N''' + @variable_type + ''''
Print 'GO'
Print ''

FETCH NEXT FROM environment_var_cursor
INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;
END
CLOSE environment_var_cursor;
DEALLOCATE environment_var_cursor;
-- End Environments variables

-- Parameter - Variable mapping
Declare @object_type as smallint
Declare @parameter_name as nvarchar(128);
Declare @object_name as nvarchar(260);
Declare @folder_name2 as nvarchar(128);
Declare @project_name2 as nvarchar(128);
Declare @value_type as char(1)
Declare @parameter_value as nvarchar(128);

DECLARE parameter_var_cursor CURSOR FOR
-- Loop through variables referenced to a parameter
SELECT op.object_type
, parameter_name
, [object_name]
, f.name as folder_name
, p.name as project_name
, value_type
, referenced_variable_name as parameter_value
FROM [SSISDB].[internal].object_parameters as op
INNER JOIN [SSISDB].[internal].projects as p
on p.project_id = op.project_id
INNER JOIN [SSISDB].[internal].folders as f
on p.folder_id = f.folder_id
WHERE op.project_id = @project_id
AND referenced_variable_name is not null

OPEN parameter_var_cursor

FETCH NEXT FROM parameter_var_cursor
INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Reference variables
Print '-- Reference variable ' + @parameter_value + ' to parameter ' + @parameter_name
Print 'EXEC [SSISDB].[catalog].[set_object_parameter_value]'
Print ' @object_type=' + CAST(@object_type as varchar(5))
Print ', @parameter_name=N''' + @parameter_name + ''''
Print ', @object_name=N''' + @object_name + ''''
Print ', @folder_name=N''' + @folder_name2 + '''' ----
Print ', @project_name=N''' + @project_name2 + '''' ---
Print ', @value_type=' + @value_type
Print ', @parameter_value=N''' + @parameter_value + ''''
Print 'GO'
Print ''

FETCH NEXT FROM parameter_var_cursor
INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;
END
CLOSE parameter_var_cursor;
DEALLOCATE parameter_var_cursor;
-- End Parameter - Variable mapping

FETCH NEXT FROM ref_environment_cursor
INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;
END
CLOSE ref_environment_cursor;
DEALLOCATE ref_environment_cursor;
GO
Download as SQL file

NOTE: Please use at own risk and let me know it things could be improved!
Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions
There are a couple of solutions to mail within SSIS:
  1. Send Mail Task
  2. Script Task with SmtpClient Class
  3. Execute SQL Task with sp_send_dbmail
  4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.
Add these four variables to your package










C) Execute SQL Task
The Execute SQL Task solution uses a stored procedure from SQL Server. To use that you first have to configure database mail in SSMS.

1) Database Mail Wizard
Open SQL Server Management Studio (SSMS). Go to Management and then to Database Mail.
Database Mail


















2) Enable Database Mail
If Database Mail isn't available it will ask for it. Choose the first option to create a profile.
Enable Database Mail and create profile


















3) Create Profile
Enter a name and description for the mail profile. You will need the name in the stored procedure later on.
Create a mail profile


















4) New Database Mail Account
Click the Add button to create a new database mail account. This is where you configure the SMTP server and the FROM address.

Configure SMTP and FROM address

















Account ready, continue wizard


















5) Public / Private
Make your profile public (or private)
Public profile


















6) System Parameters
Configure the System Parameters like max attachment size.
Configure System Parameters


















7)  Finish wizard
Now finish the wizard and go back to SSIS / SSDT.
Finish

Close
































8) Add OLE DB Connection Manager
Add an OLE DB Connection Manager and connect to the server where you configured DatabaseMail.
OLE DB Connection Manager


























9) Add Execute SQL Task
Add an Execute SQL Task to the Control Flow or an Event Handler. Edit it and select the new connection manager. In the SQLStatement field we are executing the sp_send_dbmail stored procedure with some parameters to get the, subject, body and from address from the SSIS variables.

' Stored Procedure with parameters
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SSIS Joost Mail Profile',
@recipients = ?,
@subject = ?,
@body = ?,
@body_format = 'HTML' ;

sp_send_dbmail in SSIS




















10) Parameters
Go to the Parameter Mapping pane and add the SSIS string variables as parameters. We can't configure the FROM address because we did that already in SSMS (Step 4).
Parameters























11) The result
Now execute the Execute SQL Task and watch your mailbox.

An email with html formatting






















If you don't like this solution, check out the Script Task solution or the third party tasks.
Case
I have SSIS and SQL Server on the same machine and the SQL Server proces (SQLSERVR.EXE) is taking a lot of memory. How can I limit the memory usage of SQL Server so that there is more left for SSIS?

Solution
SQL Server is a bit greedy when it comes to memory usage. It will take what's there for optimal SQL Server performance, but sharing(releasing) it isn't it's best quality. Luckily you can limit SQL Server. The screenshots are from 2012, but it works the same with 2005 and 2008.

1) Open SSMS
Open SQL Server Management Studio (SSMS) and connect to your server. Right click the server and choose Properties. A new window will open with Server Properties.
SQL Server Properties



















2) Memory
Select the Memory page and check the value of the "Maximum server memory (in MB)" field. It has a has a gigantic limit of 2,147,483,647MB.
2,147,483,647MB























3) Limit Memory
Now you can limit this amount. My machine has 8GB, so I have limited SQL Server to 3GB: 3 * 1024 = 3072MB. Click OK and you will see that SQL Server will gradually release memory until it reaches your limit (or restart the SQL Server Service).
Limited to 3GB























Note: be a little careful changing settings on production servers. Misuse may cause damage...