SSIS and distinct union

 

How to join data from several sources knowing that there are or might be duplicates in both sources?

In a SQL query one can use UNION (instead of UNION ALL) to merge several sources and to remove duplicates.

e.g. (knowing that both sources have same columns)

SELECT * FROM SourceA UNION SELECT * FROM SourceB

In SSIS there’s no such component to accomplish this task immediately.

To accomplish the same behavior in SSIS as in a SQL query, one should combine a “UNION ALL”-component with a “SORT”-component.

The “SORT”-component provides an option to remove the duplicate rows.

In our example above, edit the “SORT”-component to specify the sorting order based on the column or columns that uniquely identifies a record (for example the record-ID column).

In the SORT-editor (the normal editor or the advanced editor) one can select the option to remove the rows with duplicate sort values as shown below.

Normal Editor	Advanced Editor

Once this property is set to true, the combination of the “UNION ALL”-component and the “SORT”-component achieves the same thing as our “UNION” query, so your output from the “SORT”-component will no longer contain duplicate rows.

Exceeded storage allocation. The server response was …

 

I was trying to send a mail via SSIS using the smtp server of windows server 2008, when I got the following error:

“Exceeded storage allocation. The server response was: 4.3.1 Message size exceeds fixed maximum message size.”

To fix this problem, you have to do the following:

Open IIS 6.0 Manager, expand your computer’s name, scroll down to the SMTP server, right-click it and select “Properties” from the context menu.

Click the “Messages” tab and set a maximum message size. Alternatively you could allow any size messages by deselecting the “Limit message size to (KB):” checkbox.

SSAS port for named instance

 

Here is what I have learnt today while configuring the firewall to allow remote access to a named instance of SSAS.

You have to allow access through the firewall over port 2382. Subsequently you have to allow access over the specific port used by your named instance of Analysis Services.

But how can you determine on which port SSAS is running?

For that you have to open a command prompt and type in the following:

netstat /ao >>c:\output.txt

In the SQL Server Configuration Manager you have to look up the Process ID of your named instance of SSAS (for example: PID=2236).

Then you have to look for the PID in the output file and find the corresponding TCP IP:port information.

Once you know the port used by your named instance of SSAS, you have to make an inbound rule in the firewall to allow remote access to your named instance of SSAS.

Information about the SSAS ports can be found here. Also see my previous post about the firewall settings.

More information about these ports can also be found in “SQL Server 2008 Books Online” – “Configuring the Windows Firewall to Allow SQL Server Access”.

Ports used by Analysis Services

Deploy a SSAS project

 

When you create an SSAS project using BIDS / VS there are a number of files that are created in the bin folder of the project:

• [project name].asdatabase - contains the declarative definitions for all SSAS objects
• [project name].deploymenttargets - contains the name of the target SSAS instance and database
• [project name].configsettings - contains environment specific settings such as data source connections and object storage locations.  These settings override what's in [project name].asdatabase.
• [project name].deploymentoptions - contains options such as whether deployment is transactional and whether objects should be processed.

In order to deploy a SQL Server Analysis Services project to a server, you can use the Deployment Wizard included in SSAS as a tool to install the project.

Navigate to the [project name].asdatabase file in the bin folder of your BIDS /VS project or to the folder where you copied the files to:

Specify the target deployment server and the name of the SSAS database on that server. 

The best approach is to retain the settings made on the Analysis Server (SSAS deployment and Role-based security).

The best approach is to retain the configuration properties made by administrators. If necessary, you can overwrite them with the settings in the project.

Specify the connection details for the source database:

You can specify whether to process the SSAS objects after deployment. 

• Default processing: let SSAS decide what needs to be done.
• Full processing: to process all objects. 
• None: to not process at all. 

Use the ‘Confirm Deployment’ dialog to optionally specify whether to generate an XMLA script.

Progress of deployment:

Deployment result:

Configure Database Mail

 

In order to send mail using Database Mail in SQL Server, there are 3 steps that need to be carried out.

1. Create Profile and Account
2. Enable DatabaseMail on SQL Server Agent
3. Restart SQL Server Agent

1) Create Profile and Account

2) Enable DatabaseMail on SQL Server Agent

3) Restart SQL Server Agent

SQL Server installation and Firewall Settings

 

After installation of a SQL Server instance we need to make some changes to the firewall settings to make the SQL Server Database Engine externally accessible.

The following inbound rules should be made in the Windows Firewall:

• Port 1433, Protocol TCP (Database Engine default instance)
• Port 1434, Protocol UDP (SQL Server Brower service for a Database Engine named instance)

Now we should be able to connect to the SQL Server Database Engine.

To make the Analysis Services externally accessible, you need to open the following ports:

• Port 2382, Protocol TCP (SQL Server Browser service for an Analysis Services named instance)
• Port 2383, Protocol TCP (Analysis Services default instance)

More information about these ports can be found in “SQL Server 2008 Books Online” – “Configuring the Windows Firewall to Allow SQL Server Access”.

Ports used by the Database Engine

Ports used by Analysis Services