Power BI Visualizations Follow Up

First, let me give a thank you to everyone who attended my webinar and all of your positive comments. For those of you coming to my blog and did not get a chance to attend my webinar, you can see it here. There were quite a few submitted questions and I have chosen a couple really good ones to share in this follow up video blog. There were some other questions asked that were a bit more specific to the individuals and I will be working on those and reaching out personally. I hope you enjoy the video below and I have included links afterwards in regards to the items you will see in this video. Enjoy!

-Link for Color Bling Friendly site here

– Link for creating a JSON file for a color palette here

– Blog for using UNICHAR in Power BI here

– Pragmatic Works On Demand Training here

Advertisements

Introduction to SSIS – Building Your First Package

Greetings everyone. I recently delivered a live webinar covering some introductory items within SSIS. In this meeting I walked through some of the common items that are used in packages at the Junior level but as well are commonly used in any level of package. I do talk briefly about the design process and a little on performance related items. If you missed the live webinar it can be viewed here. You will need to create an account to view this video but then you will have access to all the other free content we have available. Any feedback would be appreciated and if you have any question do not hesitate to ask. I do recommend reading my blog on the sort transform after you watch the webinar which takes a focused look at the component and its performance on packages. You can read that blog here.

The topic of Introductory to SSIS was a bit difficult for me to present only because there is so much in SSIS. As well some of the most basic components can be used in more advanced ways. I am as well learning that there is usually more than one way to achieve a goal in SSIS, initially you need to find the way that is easiest for you. As you increase in ability though you will have to leave you comfort zone and try new methods to ensure that you are developing packages in the most efficient way possible, this is known as performance tuning. If you have not already done so, I do recommend looking at Mitchell Pearson’s webinar on performance tuning here.

Task Factory Secure FTP – Using the Get List of Files Option

Greetings everyone. Today I will be doing a blog for a specific component and specific action with the Secure FTP. The “Get List of Files” action for this component utilizes the object variable. If you did not see my blog on object variables please go here. Quite often we have clients that would like to use this option for the Secure FTP component in Task Factory but they are unaccustomed to using object variables. In the video associated with the blog I will show you how to properly setup this component using a couple different options. The main item of this blog is that the object variable must be parsed out and after that is accomplished there are a number of ways you can view or write this information to a table. Just underneath the video you will see the script used for the two script tasks seen in the package. Shortly after this blog I will be doing a small write up on how to leverage our File Filter option correctly which is essential if you are using the “Download Directory from Server” option and you need to just pull certain items. This File Filter option is also available for the “Get List of Files” option.

**It should be known that all these scripts are written in C#. Also any items italicized and in bold represent items that will change depending on the user**

First Script which creates a message box to detect how many rows exist within the arraylist housed in the object variable:

MessageBoxScript

var files = (ArrayList)Dts.Variables[“User::objFilesList“].Value;

MessageBox.Show(files.count.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

Here is the second script that was used in the the For Each Loop container to also use a message box to display the file name:

MessageBoxScript2

MessageBox.Show(Dts.Variables[“strFileName”].Value.ToString());

Dts. TaskResult = (int)ScriptResults.Success;

SSIS Performance Tuning Webinar–Q & A

A great webinar to follow up my Intro to SSIS webinar

Mitchellsql

Thank you everyone for attending my webinar on SSIS Performance Tuning, if you missed that presentation you can watch it here: http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=683

Below are some of the questions that I received from the Webinar:

Q: Can you give some performance tips in using script tasks too?

A: Yea, don’t use them! Script tasks can be really bad for performance and many times I have been able to replace someone else’s code with native SSIS components. For example, I had situation where the client was using some very complicated .net code to parse out columns based on logic. The package was taking over an hour to process 1 million records. I replaced this with some conditional splits and derived columns and it now runs in 3 minutes.

Q: I am assuming that the file formats must be the same for all files when using the MultiFlatFile transform, correct?

A: You are…

View original post 272 more words

SSIS Execute SQL Task…How it’s used

A great blog on a frequently used component in SSIS

kwoodstechtalk

pic1

Recently, I received a request from a user to explain the purpose of the Execute SQL Task and how it’s used.  Though this is not a Task Factory component it is still very useful! Task Factory has proven time and time again to be a valuable source for business solutions, there are times when the standard components of SSIS are needed in concordance with Task Factory to address your business solution needs.

There will be times when a user will need to utilize a Foreach Loop Container to run a series of events inside of Task Factory components (as is the case in my Video Blog on the Advanced Email and SMS Task from Task Factory).  A user may need to use a Merge Join to join tables together within a project when using a TF Data Flow Nugget.  Task Factory offers an unbelievable amount functionality and adds an incredible…

View original post 319 more words

Just because its called the Sort Transform doesn’t mean you need it to sort! Just say no to this blocking transform!

Hello and good day to everyone! Today I wanted to take the time to talk about the fully-blocking, asynchronous Sort Transform in SSIS. The purpose of this transformation is to ensure that your data is flowing through the constraints with a certain order determined by you. Here is what it looks like:

SortSettings

Why is this a problem you ask? What the transform is accomplishing is not bad but how it does this is the problem. Because this component is asynchronous it uses a new buffer for the data coming into it. This means that it will wait until all the data coming from the components preceding the Sort Transform and then release it, to move onto other parts of the Data Flow. So if you are pulling 1 million records from a SQL Server table and then have a Sort Transform, it will wait until all 1 million rows are present and then release them in batches (usually 10,000 rows) down the rest of the package, as shown below:

SortTransform

This only occurs with asynchronous components (yes, there are others!), normally the records would process through whatever transform in batches. Do not worry though, this is not a blog to merely gripe about blocking transforms, I will show you how to accomplish this same goal without using the Sort Transform. This solution should help out with performance tuning on packages of any size. Next comes the easy steps to get it done:

1. IMMEDIATELY DELETE THE SORT TRANSFORM!

2. Open the OLE DB source and switch the Data access mode to “SQL commad”:OLEDBsource

3. Once this is done, you will need to use T-SQL to select which columns you would like from the appropriate table. The important item in this step is that you will want to setup an ORDER statement by the necessary column (the identity column most likely)

**Here is an example from the AdventureWorks 2012 Database using the Production.Product table**

SQLCommand

4. Once this SQL statement is set the data is now sorted but we need to mark it as such for SSIS to recognize this sorting. To do this, right-click that same OLE DB source and select the Show Advanced Editor option

ShowAdvancedEditor

5. You will now be presented with a window that has four tabs and we will be selecting the Input and Output Properties tab. There should only be two option available to you and you will want to highlight the OLE DB Source Output choice and its properties will be populated in the window to the right. The property we are going to manipulate is the IsSorted option and you will want to set this option to True, this is how we tell SSIS about the sorting.

SourceOutput

6. Now we need to indicate by which column we are sorting by. Inside the same window as the previous step expand the OLE DB Source Output item to reveal two options ; External Columns and Output Columns. Here we will be expanding the Output Columns options and be presented with the entire list of columns inside of our source. Simply locate the column that you selected in your ORDER BY clause and highlight it, its properties will appear in the window to the right. Locate the field labeled SortKeyPosition and input the value of 1. It should be known that there may be times when you ORDER BY more than one column. In this advanced editor you will just want to go to each column you Ordered by and place a value according to its priority with 1 being the highest moving downwards to 2 and so forth. So if the SQL statement had an order statement as follows: ORDER BY ProductID, SalesID. The ProductID would have a value of 1 placed in the SortKeyPosition field while the SalesID column would have the value of 2.

SortKeyPosition

Once this is done you can hit okay and everything is set! Although this method is a bit longer than simply adding a component and checking a box this solution will provide a huge boost to package performance when dealing with larger data sets. If you are pulling down less the 100,000 rows you can really use either of the two options for sorting. I do hope that this walk-though was helpful and you see good performance gains for your SSIS packages!

My fellow developers, ask not what you can do for your object variable, ask what your object variable can do for you!

Greetings everyone, and thank you for taking the time to look at this blog post. In this post I will be showing you how to use the object variable to make packages dynamic. Specifically I will be incorporating this with a 3rd party component that we here at Pragmatic Works do offer, Task Factory. The core of what makes this example work is how the object variable stores data which is known as a result set. When working in SSIS you will notice that when you create an Object Varibale there is no way to input a value, it simply states “System.Object” as seen below:

ObjectVariable

**Click to Enlarge**

No need to worry this is perfectly normal. What happens is that you can load up rows of data inside this variable using the native Result Set Destination component, and they will all be available in the variable. The trick is how to get this data out and in the format you would like, that’s where the For Each Loop container comes into play. Once you have an object variable populated you can use this component to parse through each row in the Result set and send those values to user defined variables to be used in your SSIS package. The important thing to note is that the object variable is only populated at run time, which means when that package is done it does not retain that data. Also how the object variable holds the data doesn’t allow you to directly use the object variable itself in the development process. Now that we have a brief description of what the object variable can do, let’s see it in action in the short video below. I hope you enjoy. For more blogs related to SSIS and Task factory you can visit me here.