SQL Server PDW Destination shows advanced editor only

Case
I'm using PDW Destination adapter for SQL 2012 (V10.0.6186) from Control Node AU3 patch v10.62.14 and it only shows the Advanced Editor.

Advanced Editor PDW Destination

Solution
A know cause is that the package is generated with BIML (not MIST) by using the customcomponent tag. Messing up a single property can cause not showing the correct editor. If it's not generated with BIML, then download the latest version from the APS AU3 download page: Analytics Platform System Appliance Update 3 Documentation and Client Tools. At the time of writing V10.0.6205 was the latest version to download. This solved the problem:

The correct editor for the PDW Destination

Thanks to Greg Galloway and James Anthony Rowland-Jones.

Azure HDInsight Create / Delete Cluster Tasks

Case
Microsoft released the SSIS Feature Pack for Microsoft Azure (2012, 2014), but how do the Azure HDInsight Cluster Task work?

Create / Delete HDInsight Cluster

Solution
With these tasks you can create an HDInsight cluster (and then do some Hive or Pig tasks) and then delete it when you're ready with the cluster.

1) Storage Account
First make sure you have an Azure account that has an storage account in it. Besides the name the location is also important. You need to use the same location in the Azure HDInsight Create Cluster Task. Mine is called SSISJoost.

Storage Account

2) Access Keys
Click on the Manage Access Keys icon to get the primary access key. We need this for the Azure Storage Connection Manager.

Storage Account Access Keys

3) Connection Manager for Azure Storage
If you haven't already installed the SSIS Feature Pack for Microsoft Azure then now it's time to do that. Create a new Connection Manager for Azure Storage by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureStorage. Fill in the Storage account name from step 1 and the Access key from step 2. Test the connection and click OK to save the new Connection Manager.

SSIS Connection Manager for Azure Storage

4) Makecert.exe (Certificate Creation Tool)
Open the Visual Studio Command prompt to create a new certificate with Makecert.exe. The command is as follows (but replace SSISJoostCertificate by your own name, twice):
makecert -sky exchange -r -n "CN=SSISJoostCertificate" -pe -a sha1 -len 2048 -ss My "SSISJoostCertificate.cer" 

Makecert.exe

5) Azure Subscription ID
Go to manage.windowsazure.com and locate your Subscription ID under settings. You need this in  one of the next steps.

Locate Subscription Id

6) Upload Certificate
Go to manage.windowsazure.com and then to Settings (1) and then to Management certificates (2). Upload (3) the .cer file created in step 4 and notice the thumbprint (4).

Management certificates

7) Azure Subscription Connection Manager
Create a new Azure Subscription Connection Manager by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureSubscription. Fill in the Azure Subscription ID from step 5 and browse to find your certificate, The thumbprint should be the same as in step 6. Test the connection and click OK to save the new Connection Manager.

Azure Subcription

8) Azure HDInsight Create Cluster Task
Add the Azure HDInsight Create Cluster Task to the surface of the control flow. Give it a suitable name and then edit the task. Under connections you must select the two newly created connection managers (step 3 and 7). And then change the General properties:
ClusterName: the name of your cluster
ClusterSizeInNodes: the number of nodes (be careful or you get a high invoice if you choose a high number)
StorageContainerName: specify a container from your storage account to store the data in
UserName: specify a new username
Password: specify a new password
Location: choose the same location as your storage account (see step 1)
FailIfExists: Specify whether the task should fail if it already exists

Azure HDInsight Create Cluster Task

9) Test
Run the task and check whether a HDInsight cluster is created. If could take a while (single node in North Europe took ± 20 minutes). When it is ready you can performe a Hyve or Pig Task on this cluster.

HDInsight Cluster

10) Delete cluster
When your PIG and/or Hive Tasks are ready you can delete the cluster with the Azure HDInsight Cluster Task. Drag it to the surface and give it a suitable name. Edit it and select the Azure Subscription Connection Manager from step 7. The ClusterName is the name of the cluster you want to delete (same name as in step 8). And the FailIfNotExists indicates whether the task should fail if the cluster is already deleted. Now run the task to delete the cluster. Should be a lot faster than creating a new cluster.

Azure HDInsight Cluster Task

Azure Blob Enumerator

Case
Microsoft released the SSIS Feature Pack for Microsoft Azure (2012, 2014), but how does the Foreach Azure Blob Enumerator work?

Foreach Azure Blob Enumerator




Solution
The Azure Blob Enumerator works just like any other enumerator. The best combination is with a Data Flow Task and an Azure Blob Source. It returns the blob filename on Azure including the blobpath: MyFolder\Weather.csv. Perfect for the Azure Blob Source where it is also one value.

1) Storage Account
First make sure you have an Azure account that has an storage account in it. Besides the name the location is also important. Especially for the Azure HDInsight Create Cluster Task. Mine is called SSISJoost.

Storage Account

2) Access Keys
Click on the Manage Access Keys icon to get the primary access key. We need this for the Azure Storage Connection Manager.

Storage Account Access Keys

3) Connection Manager for Azure Storage
If you haven't already installed the SSIS Feature Pack for Microsoft Azure then now it's time to do that. Create a new Connection Manager for Azure Storage by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureStorage. Fill in the Storage account name from step 1 and the Access key from step 2. Test the connection and click OK to save the new Connection Manager.

SSIS Connection Manager for Azure Storage

4) Variable
Add a string variable to store the azure filepath in. Mine is called AzureBlobPath

String variable

5) Foreach Azure Blob Enumerator
Add the Foreach Azure Blob Enumerator to the surface of your control flow and edit it. On the collection page choose Foreach Azure Blob Enumerator.
Azure storage connection: use the connection manager from step 3 or create a new one.
Blob container name: specify the containername of Azure where the files are stored.
Blob path: The optional 'subfolder'. Add / if you didn't use 'subfolders'.
Blob file name filter: specify the wildcard to select the files.
Blob file modified after and Blob file modified before: Optional date filters.

Foreach Azure Blob Enumerator



Blob container name, Blob path & Blob file name filter

5) Expression
Now go to the properties of the data flow task that contains the Azure Blob Source Component. Just like the XML source the expressions are not on the source component itself. Now you can add an expression on the Blob Name to replace it with the value of the string variable @[User::AzureBlobPath] from step 4.

Expressions on Data Flow Task properties

Azure Blob Source and Destination

Case
Microsoft released the SSIS Feature Pack for Microsoft Azure (2012, 2014), but how do the Azure Blob Source and Destination components work?


Azure Blob Source and Destination

Solution

1) Storage Account
First make sure you have an Azure account that has an storage account in it. Besides the name the location is also important. Especially for the Azure HDInsight Create Cluster Task. Mine is called SSISJoost.

Storage Account

2) Access Keys
Click on the Manage Access Keys icon to get the primary access key. We need this for the Azure Storage Connection Manager.

Storage Account Access Keys

3) Connection Manager for Azure Storage
If you haven't already installed the SSIS Feature Pack for Microsoft Azure then now it's time to do that. Create a new Connection Manager for Azure Storage by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureStorage. Fill in the Storage account name from step 1 and the Access key from step 2. Test the connection and click OK to save the new Connection Manager.

SSIS Connection Manager for Azure Storage

4) Azure Blob Destination
Add the Azure Blob Destination to the surface of your data flow and connect it to the preceding transformations. The properties are very limited. You don't have to specify things like datatypes and qualifiers. If you want to specify those you first use a Flat File Destination to store the file locally and then use the Azure Blob Upload Task to upload the file to Azure.
Azure storage connection manager: use the connection manager from step 3 or create a new one.
Blob container name: specify the containername of Azure where you want to store the file. A new container will be created if it doesn't exist.
Blob name: The name of the file optionally with a 'subfolder'. In the Azure Blob Upload Task these are two separate fields.
Blob file Format: specify the format of the file - CSV or AVRO.
CSV file column delimiter: if you chose CSV you can specify the column delimiter.
First row as column names: if you chose CSV you can specify whether you need a header row.

Azure Blob Destination

5) Result blob destination
Run the data flow and go to Azure to see the result. In mycontainer you should see the newly added file.

Result of Azure Blob Destination

6) Azure Blob Source
Add the Azure Blob Source to the surface of your data flow and edit it. Again the properties are very limited. You can't specify things like datatypes and qualifiers just like with an Excel Source. If you want to specify datatypes, qualifiers, etc. you first use the Azure Blob Download Task to download the file and then use a regular Flat File Source component.
Azure storage connection manager: use the connection manager from step 3 or create a new one.
Blob container name: specify the containername of Azure where the file is stored.
Blob name: The name of the file optionally with a 'subfolder'. In the Azure Blob Download Task these are two separate fields.
Blob file Format: specify the format of the file - CSV or AVRO.
CSV file column delimiter: if you chose CSV you can specify the column delimiter.
First row as column names: if you chose CSV you can specify whether there is a header row.

Azure Blob Source

Tip: You can also use the Foreach Azure Blob Enumerator in combination with the Azure Blob Source

Azure Upload and Download Tasks

Case
Microsoft released the SSIS Feature Pack for Microsoft Azure (2012, 2014), but how do the upload and download tasks work?

Azure Blob Upload and Download Tasks

Solution

1) Storage Account
First make sure you have an Azure account that has an storage account in it. Besides the name the location is also important. Especially for the Azure HDInsight Create Cluster Task. Mine is called SSISJoost.

Storage Account

2) Access Keys
Click on the Manage Access Keys icon to get the primary access key. We need this for the Azure Storage Connection Manager.

Storage Account Access Keys

3) Connection Manager for Azure Storage
If you haven't already installed the SSIS Feature Pack for Microsoft Azure then now it's time to do that. Create a new Connection Manager for Azure Storage by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureStorage. Fill in the Storage account name from step 1 and the Access key from step 2. Test the connection and click OK to save the new Connection Manager.

SSIS Connection Manager for Azure Storage

4) Azure Blob Upload Task
Then add the Azure Blob Upload Task to the surface of the Control Flow and give it a descriptive name (and description).
AzureStorageConnection: select the Connection Manager from step 3 or create a new one.
BlobContainer: specify the name of the container where you want to store the files. It creates a new container if it doesn't exist. The name should be in lower case.
BlobPath: specify an optional 'subfolder'. Use / if you don't need 'subfolders'.
FileName: specify a filename or wildcard filter to select the file(s) that needs to be uploaded.
ModifiedAfter and ModifiedBefore: specify data filters or leave them unchanged
LocalPath: specify the folder on your server/computer where the files are stored.

Azure Blob Upload Task

5) Result Upload Task
Now execute the Azure Blob Upload Task and go to azure to see the result.

Upload result

6) Azure Blob Upload Task
Then add the Azure Blob Download Task to the surface of the Control Flow and give it a descriptive name (and description).
AzureStorageConnection: select the Connection Manager from step 3 or create a new one.
LocalPath: specify the folder on your server/computer where the files will be stored.
FileName: specify a filename or wildcard filter to select the file(s) that needs to be downloaded.
ModifiedAfter and ModifiedBefore: specify data filters or leave them unchanged
BlobContainer: specify the name of the container where the files are stored on Azure
BlobPath: specify an optional 'subfolder'. Use / if you didn't used 'subfolders'.

Azure Blob Download Task

Tip: You can also use the Azure Blob Source and Destination to upload and download files.
Note: Unfortunately an Azure File System Task (to for example delete files) is still missing...

SSIS Feature Pack for Azure

Microsoft just released the SSIS Feature Pack for Microsoft Azure (2012, 2014) with new tasks, an azure blog enumerator, an azure blob source and an azure blob destination. Click on the link below the images for more details or check MSDN.


Azure Blob Upload and Download Tasks









Azure Blob Source and Destination









Foreach Azure Blob Enumerator









Azure Create / Delete HDInsight Cluster Tasks

SSIS 2016 CTP2 - Incremental Deployment

Case
Incremental Deployment was announced in CTP2. How do I test this without a new SSDT-BI?

Solution
You need to start ISDeploymentWizard.exe from [Drive]:\Program Files\Microsoft SQL Server\130\DTS\Binn

ISDeploymentWizard.exe from the 130 folder

In the second screen have to select Package Deployment. After that you can browse to your Visual Studio (SSDT-BI) folder where your SSIS 2014 project is located. You can also copy and paste the folderpath and then hit the Refresh button. Now you are able to uncheck some of the packages.

Incremental Deployment

*Update: in CTP 2.3 you can deploy a single package in the context menu of the solution explorer:

Deploy single package