Processing SSAS Cubes with SSIS –Q & A

For those of you who attended my webinar on Processing SSAS cubes with SSIS, thank you! For those of you who didn’t the video is still available here: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/1714.

Below are a few of the questions I received in the webinar. There will be a couple of follow up blogs for the more detailed questions.

Q: Can you please share the script used to generate the partitions?

A: I will post a follow up blog in the next couple of days with step by step instructions. Sharing my exact script would not be helpful. I will update this post with the additional blog.

Q: How are you stopping through your SSIS Steps within package execution?

A: Breakpoints! I used breakpoints to pause the package during execution so I could explain exactly what was going on. To enable breakpoints on a task inside SSIS you simply right click on the task and then select edit breakpoints. I showed this at the end of the webinar during Q&A.

Q: What are our best logging options if we do all of our cube processing with script tasks instead of the native components?

A: Your best logging options are going to be setting up a Profiler Trace or Extended events. I briefly explained setting up a trace at the end of my webinar.

Q: Does the C# script task need the connection string information to query SSAS for the partition information?

A: Yes. In my webinar I showed how to dynamically create partitions for your cube inside of SSIS. Before creating them I used a script task to see if the partition already existed. I have posted a follow up blog below:

https://mitchellsql.wordpress.com/2014/11/18/does-cube-partition-exists-script-task-in-ssis-explained/

Q: How are the aggregation designs handled on the dynamic partitions?

A: This is handled in the XMLA by specifying the Aggregation ID. I showed this at the end of my webinar during the Q&A section and I will also include this in a follow up blog on creating the XMLA for dynamic partitions.

Q: Is this applicable to tabular cube processing?

A: Yes. The analysis services processing task processes tabular models, cubes, dimensions, and mining models.

Once again thank you for attending our free webinar. Until next time!

Advertisements

Quickly encrypt and decrypt files with the TF PGP task for SSIS!

Business Problem

I recently had to help a client with encrypting a file before uploading that file to an FTP. Thanks to the PGP task in Task Factory we were able to fulfill this requirement in a matter of minutes.

Walkthrough

In this example I will walk you through quickly creating public and private keys and then using those keys to encrypt the file.

    1. Create two variables. One for the source location and one for the destination:
    2. image
    3. From the SSIS Toolbox drag and drop the PGP Task into the control flow and then open it for editing.
    4. For “What action will this perform?” choose Encrypt File.
    5. Click Generate Key to create your own public and private keys. Note: Public keys are used for encryption and private keys are used for decryption.
    6. image
    7. Now to fill out some basic information. The private key created will require a password, please remember to store this password somewhere safe, i.e. some kind of password vault. This will be required to decrypt files.
    8. image
    9. Now that we have generated PGP keys we can now encrypt our files in just a couple steps. A screenshot of all steps below is listed at the bottom.
    10. For Section 1:
      1. Select “File location is stored in a variable”
      2. Select “strFileName” for the variable
    11. Section 2:
      1. Select “Destination location is stored in a variable”
      2. Select “strFileName_Encrypt” for the variable.
      3. Select the checkbox for Overwrite the destination file if it already exists
    12. Section 3:
      1. Select connection manager: Select “Public”.
        1. Note: The connection manager “Public” was added to the package automatically when we generated new keys. Public is the name I gave to my public key ring.
      2. Finally select the Public Key you wish to use for encryption. We only have one key on our key ring.

I have provided three screenshots below.

    1. Screenshot 1 – Final Configuration
    2. Screenshot 2 – File before PGP Encryption
    3. Screenshot 3 – File after PGP Encryption

Screenshot of final configuration:

image

 

image

 

As always thanks for looking and your feedback is appreciated!

Get a List of Files on an FTP Server and then process with Foreach Loop Container

Today we are going to address a very common FTP question: “How can I get a list of the files that are on my FTP Server?”

This is a task that can be accomplished very easily with the Task Factory Secure FTP component and this walk through will provide step by step directions. If you are interested in Task Factory and the more than 40 tasks you can find the free trial here:

Here we will use the “Get list of files” option and store all the files for a given directory into an object variable. We will use a Foreach loop container to iterate through the object variable and then an Execute SQL task to write each file name to a table in our database. This walkthrough is broken down into three sections.

  • Part 1 – Setting up the Secure FTP component to get List of Files
  • Part 2 – Setting up the For Each Loop to read the the object variable
  • Part 3 – (Optional) Setting up the Execute SQL Task to write the file names to a table.

Part 1 – Configuring the Secure FTP site to Get List of Files from server.

  1. Start a new package and drag the Secure FTP task into your control flow from the toolbox.
  2. Next right click on the Secure FTP Task and click edit.
  3. Steps 4-7 below are in screenshot 1
  4. What will this task do? Select “Get list of files” from the drop down menu.
  5. For SFTP Connection Manager either choose an existing connection manager or create a new one if don’t have one set up.
  6. For your Directory location on server you have two options:
    1. First you can choose to click the ellipsis button and hard code the directory location.
    2. Second you can choose to store the directory location in a variable
  7. Next create an object variable to store the list of file names in from the server. (objFilesList)

Screenshot 1

Part 2 – Configuring the For Each Loop to iterate through the object variable

  1. Pull a Foreach Loop container into the Control Flow and drag the precedence constraint from the Secure FTP task to the Foreach loop container.
  2. Steps 3-6 can be found in Screenshot 2
  3. Open the For Each container and click on the Collection tab.
  4. For “Enumerator” choose “Foreach From Variable Enumerator” from the drop down.
  5. Next select the object variable you specified in the Secure FTP component. (objFilesList)
  6. Finally click the Variable Mappings tab.

Screenshot 2

  1. On the Variable Mappings tab select a string variable to store the file name in. (strFileName)
  2. Make sure Index is set to 0. (Screenshot 3)
  3. Click Ok.

Screenshot 3

Optional: (This section simply shows how to map variables in an Execute SQL Task and helps with visualization of each iteration of the Foreach Loop through the list of files on our FTP.)

Part 3 – Setting up the Execute SQL Task to read the string variable

(This section has been added for completeness and is fully optional.)

Note: For this example I have created a simple table with four columns:

CREATE TABLE [dbo].[FilesOnSftp](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Directory] [varchar](50) NULL,

[FileName] [varchar](50) NULL,

[DateLoaded] [date] NULL)

  1. Pull an Execute SQL Task from the toolbox into the Foreach Loop container.
  2. Create a new variable called strSftpDirectory and give it a value of /

  3. Right click on the Execute SQL Task and click Edit to bring up the Editor
  4. For “Connection” choose the connection manager to your database.
  5. Click and highlight “SQLStatement” and then click the Ellipsis button that appears on the right.

Screenshot 4

  1. Clicking the Ellipsis button will give you a pop up window, this is where we enter our Insert SQL Statement. Here is the syntax I used if you are following along with the tutorial:

    INSERT INTO dbo.FilesOnSftp

([Directory], [FileName], [DateLoaded])

VALUES(?, ?,GETDATE())

  1. Now its time to map the parameters. Click on the Parameter Mapping tab. (Screenshot 5)
  2. Click the Add button and then select “strSftpDirectory” for Variable Name.
  3. For data type choose Varchar and change the Parameter Name to 0.
  4. Click the Add button a second time and select strFileName for the Variable Name.
  5. For data type choose Varchar and change the Parameter Name to 1. Click ok to save changes.

    Screenshot 5

How to flatten data using the Pivot Transform in SSIS.

The Pivot Transform is a very powerful tool in SSIS. It’s a component that is overlooked and isn’t the first thing to come to mind when you need it most.

Imagine you have a table that has an employee listing for each type of phone number. For example, you have one row for business phone, personal phone, and cell phone. We want to clean this data up and have only one row per employee. Well this is the situation I was presented with recently. The great news is with the Pivot Transform in SSIS this can be accomplished fairly easy. The table below is an simplified example of the data we had to work with.

EmployeeID PhoneType PhoneNumber
1 Business 123-987-6543
1 Personal 231-789-3456
1 Cell 312-897-5643
2 Business 321-978-6534
2 Personal 132-798-5634
2 Cell 213-879-3564

 

Our end product will look like this:

EmployeeID BusinessPhone PersonalPhone CellPhone
1 123-987-6543 231-789-3456 312-897-5643
2 321-978-6534 132-798-5634 213-879-3564

 

Step 1) Set up a data flow task in the control flow.

Step 2) Open up the data flow and pull in your source component. For this example I created a table in SQL Server.

Step 3) Drag in a Pivot Transform and link it to the source component.

Step 4) Configure the Pivot Transform

I was going to go through step by step instructions on how to configure the Pivot Transform. However, there is already an awesome blog out there by Devin Knight. Please click on the link below to view his blog post.

STEP 3 in Devin’s post explains how to configure the Pivot Transform.

https://devinknightsql.com/2009/06/15/better-know-a-ssis-transform-the-pivot-transform/http://bidn.com/blogs/DevinKnight/ssis/85/better-know-a-ssis-transform-%E2%80%93-the-pivot-transform

 

Thanks for Looking!

Quickly remove invalid XML characters! (Dimension Processing Error: Illegal XML Character)

Recently I was using the Dimension Processing task inside of SSIS to perform a process add to one of my dimensions. I then found out that I had some bad data, more specifically, I had some invalid XML characters in my source data and this was causing the dimension processing task to fail. Below is a screenshot that includes the invalid character, after Mitchell before =.

image

Error Message:

image

Business Problem:

I needed to remove invalid XML characters from source data so that I could use the dimension processing task to perform a process add on the dimension.

Solution:

Sadly, I originally began this process by trying to write my own script transform to remove the invalid XML characters. Unfortunately the script I was using (Found on the internet) apparently did not account for all characters as my processing task continued to fail with the same error.

So what did I do? I turned to Task Factory of course! To solve this problem I used the Task Factory replace unwanted characters transform.

Walkthrough:

    1. Bring the Task Factory Replace Unwanted Characters Transform into your data flow and place it between your source and destination:
    2. image
    3. Next open up the Task Factory component and you will see a list of your columns on the left.
    4. On the right you will see the column “Choose Action”. Click this drop down menu for the column you wish to cleanse. This menu will provide you with 4 different cleansing actions! (List below)
      1. image
    5. Select “Replace invalid characters that cannot be part of an XML Document.

image

Conclusion:

Now when I execute my package you will notice the illegal XML character is gone!

image

Thank you Task Factory Team!

For Loop Container in SSIS–Does File Exist (Part 2)

In this post I am going to explain how to set up and use the For Loop container inside SSIS in a real world scenario. In a previous post I showed how to set up a script task inside SSIS to check if a File Exist. Well if the file doesn’t exists I want to keep checking over and over again until that file does exists. The For Loop container is just what I need. Let’s get started.

This post assumes you are building on my previous post, found here: Does File Exist

Walkthrough

Open the package created in Part 1 and pull a For Loop container task into the control flow.

  1. Drag the script task created in part 1 into the For Loop container.
  2. Right click on the For Loop Container and click “edit”.
  3. Under For Loop properties you will see three options.
    1. InitExpression – This value is optional. Here you can assign an initial value. (More on this shortly)
    2. EvalExpression – This value is required. As long as this expression evaluates to true the loop will continue to loop. Once it evaluates to false the loop will stop.
    3. AssignExpression – This value is optional. This value is used to change the value of the variable that you use in the EvalExpression. For example you can add 1 to each iteration of the loop.
  4. For InitExpression enter the following: @bolDoesFileExist = False.
    1. Notice I used one (=) sign here. In SSIS you use one = for assigning values.
  5. For EvalExpression enter the following: @bolDoesFileExist == False.
    1. As long as this expressions evaluates to true the loop will continue. Once the variable no longer is false (meaning that the file does exists) the loop will stop.
  6. For Assign Expression, leave this value empty. Click ok to close out the For Loop Editor.
  7. image
  8. That’s it!

You might want to also add a delay between loops, maybe 5 seconds maybe 5 minutes, completely up to you. But here is an easy way you can add that functionality.

Pull an Execute SQL Task into your For Loop container.

  1. Choose OLEDB for connection type
  2. Select any OLEDB connection, doesn’t matter which one you choose.
  3. Select Direct Input for SQLSourceType and type the following code for your delay statement:
      • WAITFOR DELAY ‘00:00:05’

image

Here is how the final package will look:

image

Thanks for looking! Enjoy.

SSIS Script Task–Does File Exists (Part 1)

So in this blog I am going to show you how you can write a quick Script Task inside of SSIS to see if a file exists. In my next blog in this two part series I will show you how you can then loop over this script task until the file does exist.

You can find Part 2 in this series here:

Business Problem:

So you know that you are going to receive a file that you need to process, and you want to process and load that file the moment it “exists”. If you run your package before the file exists the package will fail, so you want to first check to see if the file exist and then run your data flow task that will subsequently load the file.

Walkthrough

Open up your Visual Studios environment and create a new package.

  1. Pull the Script Task component into the data flow.
  2. First let’s create two variables, we will use these in our script task.
  3. image
    1. The value for the strFileName variable is the location of the file, please change yours as needed.
  4. Open the script task and make sure the Script Language is set to Visual Basic.
  5. Select “strFileName” for the read only variables.
  6. Select ‘”bolDoesFileExist” for read/write variables.
  7. image
  8. Click “Edit Script…” to open visual studios.
  9. Ok. Inside the VB project we first need to add a new class of code.
  10. In the Imports section add the following code: Imports System.IO
  11. image
  12. Now we enter our VB code. Find the section titled “Public Sub Main” and type the following code where it says ‘Add Code Here’.
  13. Dts.Variables(“bolDoesFileExist”).Value = File.Exists(Dts.Variables(“strFileName”).Value)
  14. Here is a screenshot of the final result:
  15. image
  16. Now simply click the x at the top right to close the visual studio project to close it out, you don’t have to click save.
  17. Back in the script task editor main screen click ok to compile the code. All Done!

 

Explanation of Visual Basic Code

The code inside the script task is in two parts.

  • The first part is the function “File.Exists”. The file exists function is reading the value of the strFileName variable and checking to see if that file exists. It will return a value of either True or False.
  • The second part is writing the result of the File Exists function to the variable bolDoesFileExist. So everytime this package runs the value of that variable will change based on whether that file does or does not exist.

Thanks for looking and I hope this helps. In my next post I will explain how you can loop over this task over and over again until the file does exist.