Tuesday 21 October 2014

Querying SSIS Parameter information from SSISDB


On your SSIS SQL Server you can run the following code against the SSISDB database to investigate parameter information stored in the SSIS catalog

There are multiple versions or the parameter stored in the interal.object_parameters table so this will get only the latest version of the each parameter/connection

The Query


/*
    Check package parameters and connection string settings
    for a project
*/
WITH projectVersions AS
(
    SELECT p2.name,p2.project_id, MAX(p1.project_version_lsn) versionlsn
    FROM internal.object_parameters p1
    INNER JOIN internal.projects p2 ON  p1.project_id = p2.project_id
    GROUP BY p2.name,p2.project_id
)
SELECT  CASE LEFT(params.parameter_name,3) 
        WHEN 'CM.' THEN
            'Connection Manager'
        ELSE
            'Package Parameter'     
        END as Area ,  
        params.object_name ,
        params.parameter_name ,
        params.parameter_data_type ,
        params.design_default_value ,
        params.default_value
FROM    SSISDB.internal.object_parameters params
        INNER JOIN projectVersions proj ON params.project_id = proj.project_id
WHERE   proj.versionlsn = params.project_version_lsn
--AND proj.name = 'EDI' -- change for another ssis project
ORDER BY 1


Example of the output

Areaobject_nameparameter_nameparameter_data_typedesign_default_valuedefault_value
 Package ParameterEDICommandStringStringC:\SSIS\C:\SSIS\EDI
 Package ParameterEDIServerNameStringStockSourceNULL
Connection ManagerEDICM.EDI.ConnectionStringStringData Source=x;Initial Catalog=x;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;NULL

Friday 25 July 2014

Setting up your own Hadoop Cluster using Azure HDInsights

How to get started with Hadoop and Hive

Install prerequisites to manage your cluster

Log into Windows Azure account

Sign up using http://azure.microsoft.com/en-us/  free trail link
Then click the portal link to manage your Azure services. You should end up with something like this menu on the side

Create a new Storage account

  1. Click on the storage link in the Azure left side menu
  2. Then click the new link at the bottom. This will prompt you with the below options to create a new storage account. 
  3. Choose a unique name for your URL. If the tick box turns green it means your account name is unique
  4. Choose create storage account at the bottom
  5. This will then start creating your storage account, you may need to wait 5 mins for it to complete

Create new HDInsights cluster


  1. Click on the HDInsight link on the Azure left side menu
  2. Then click the new link at the bottom. This will prompt you with the options below to create a new Hadoop cluster
  3. Choose a unique name for your URL
  4. Choose 1 data node for the cluster size (unless you want to go crazy then be my guest)
  5. Select the storage you created in the above section
  6. Click Create HDInsight Cluster. This takes a while, especially first time. Between 5min-40min 

Connecting to your Cluster


  1. When you click All Items in the top left menu, you should see something like this. Confirm your HDSight Cluster is running
  2. Open Powershell ISE
  3. Run the following
    Get-AzureSubscription Get-AzureHDInsightCluster
  4. Download the publish settings file to your local computer and keep note of the path
  5. Click on your HDInsight cluster Right arrow
  6. Then choose Dashboard
  7. Take note of your subscription name and your cluster name

Running Hive Queries against your Cluster

  1. Run a new script in powershell and replace configurations where nessasary
    Import-AzurePublishSettingsFile "<FULL_PATH_TO_PUBLISH_SETTINGS_FILE>"
    $subscriptionName = "<SUBSCRIPTION_NAME>"
    $clusterName = "<CLUSTER_NAME>"            
    $querystring = "select country, state, count(*) as records from hivesampletable group by country, state order by records desc limit 5"
    Select-AzureSubscription -SubscriptionName $subscriptionName
    Use-AzureHDInsightCluster $clusterName
    Invoke-Hive -Query $queryString

    Here is an example i have used
    Import-AzurePublishSettingsFile "C:\Powershell\Hadoop\jeremyking77Azure.publishsettings"
    $subscriptionName = "Visual Studio Professional with MSDN"
    $clusterName = "jeremyking77"            
    $querystring = "select country, state, count(*) as records from hivesampletable group by country, state order by records desc limit 5"
    Select-AzureSubscription -SubscriptionName $subscriptionName
    Use-AzureHDInsightCluster $clusterName
    Invoke-Hive -Query $queryString
  2. You should get output like the following
    Successfully connected to cluster jeremyking77
    Submitting Hive query..
    Started Hive query with jobDetails Id : job_1405933745625_0003
    Hive query completed Successfully
    United States   California  6881
    United States   Texas   6539
    United States   Illinois    5120
    United States   Georgia 4801
    United States   Massachusetts   4450