Thursday, March 31, 2016

Analysis Services connectivity issue from Remote Server

Problem: I was recently working with one of my colleagues to setup a new server in Azure. He installed all the SQL server components. When we tried to connect OLAP cubes from another local or remote server, we were facing connectivity issue with the following error message:
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond"

First we checked if SQL Server Browser service was running (Log On As) under “Local Service”. We made the changes and tried to connect SSAS through SQL Server Management Studio (SSMS) but no luck.

Then we guessed that may be our Windows Firewall was denying the access. To solve this issue, we added a new inbound rule in Windows Firewall for TCP port 2383. Please note that SSAS port is 2383 (instead of SQL port 1433). After adding new port, we were able to connect SSAS successfully from remote server.

I hope this will help you in case you encounter similar problem.

Wednesday, March 5, 2014

T-SQL code to parse Numbers from a String

Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.

Let’s take example to understand the problem.

Hari12 Sharma34
My 1st code is D$34A and 2nd code is E#078!!!
Hari# Sharma
$1#0?0 !?@1

Here is the code to get the desired output:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character
WHILE (@Position > 0)
    -- Replace alphabet with empty string.
    SET @Str = STUFF(@Str, @Position, 1, '')
    -- Find next alphabet
    SET @Position = PATINDEX('%[^0-9]%', @Str)
SELECT @Str Result


We can also write function to encapsulate this code and use wherever required.

This above code can be optimized by using Tally table mentioned below:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
SELECT  @Result = COALESCE(@Result, '') + Digit
FROM    (
        SELECT  SUBSTRING(@Str, t.ID, 1) AS Digit
        FROM    dbo.Tally t
        WHERE   t.ID <= LEN(@Str)
        AND     PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1
        ) stg
SELECT @Result Result


Tuesday, January 14, 2014

SSIS 2012 - Package Configurations

When I started working on SSIS 2012 using SQL Server Data Tools (SSDT), I noticed that SSIS package configuration option was missing. It was available in previous version of SSIS under Control Flow. We could see package configuration by right click on Control Flow surface area. Unfortunately, that option is missing now.

So I am writing this blog to provide help in case you are running into the same issue. I will explain SSIS 2012 Package Configuration in case you are wondering how to implement Package Configuration in SSIS 2012. 

PROBLEM: Package Configuration option is missing as shown in below screenshot:

Here is what MSDN describes: Create Package Configuration. Unfortunately, I could not find “Package Configuration” option there as well. However, I got a hint from the Notes on the same MSDN link which says:
You can also access the Package Configuration Organizer, by clicking the ellipsis button next to the Configuration property. The Configuration property appears in the properties window for the package.

Follow these steps to create a package configuration:

1.      In SQL Server Data Tools (SSDT), open the Integration Services project.
2.      In Solution Explorer, double-click the package to open it.
3.      To open Package Configuration Organizer, click on the ellipse button next to the Configurations property.

4.      In the Package Configuration Organizer dialog box, select Enable package configurations, and then click Add button.

5.      On the welcome page of the Package Configuration Wizard page, click Next button.
6.      On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.
I am using “XML configuration file” in Configuration type. Now specify configuration file path in configuration. Click Next button.

7.      On the Select Properties to Export page, select the properties of package objects to include in the configuration. If the configuration type supports only one property, the title of this wizard page is Select Target Property.

Only the XML Configuration File and SQL Server configuration types support including multiple properties in a configuration.

8.      On the Completing the Wizard page, type the name of the configuration, and then click Finish.

9.      View the configuration in the Package Configuration Organizer dialog box and click Close button.

I hope this post is helpful!