Recently I was working on a project where we needed to process upward of 12,000 files per day. As most would expect we used the Foreach Loop container in SSIS to loop through all 12,000 files. The results? A whopping 77 minutes to run. WOW! (Incase you were wondering this is WOW that’s AWEFUL!, not wow that’s great)
I assume the excessive time is here is due to the time it takes to dynamically set the flat file connection manager and close/reopen the connection. I don’t know exactly how long this process takes but hypothetically let’s say it takes 1 second for every 3 iterations of the loop.
Now some simple math. 12,000 files / 3 (files per second) / 60 seconds = 67 minutes.
So how did we get around this problem? Well we tried two different design patterns. The first and the one I will be blogging about here is the “MultiFlatfile” connection manager. The second, more complicated to set up, but extremely efficient can be found here: Anthony Martin.
Let’s performance test the MultiFlatFile connection manager!
For this post I created three packages.
- The first package generates 1000 flat files for testing.
- The second package iterates through all 1000 files. Runtime (22 Minutes)
- The third package uses the multiflatfile connection to bring in all the files instantaneously. (13 Seconds)
The Foreach loop got utterly destroyed in this contest. The multiflatfile connection manager was able to process all 1000 files in only 13 seconds!! Let’s look at how to set this up.
- Right click in the connection manager window and select New Connection > MultiFlatFile > Click Add
- Set up the general screen like your typical Flat File connection, you will need to hardcode an existing file.
- Click ok to close the connection manager editor, verify the connection manager is selected and then open up the properties window.
- In the properties window, scroll down to Expressions > Click the Ellipsis “…” and select “Connection String” from the drop down menu.
- Next click the ellipsis to the right to open up the Expression editor. This part is critical because this is where we set an expression that will determine which files are extracted from the directory. For this simple example my expression was “C:\\Blogs\\1000_Flat_Files\\Test*” (Don’t miss the asterisk here!). The connection manager will now retrieve all files that start with “Test”. Screenshots for set up below.
This is an awesome alternative to the For Each Loop and it’s much easier to set up, however it has some obvious drawbacks with how flexible it is. If you discover this pattern does not perform as well as you hoped or is not flexible enough please review the blog post I linked above by Anthony Martin. It takes a little more set up but offers incredible flexibility and is the best performing solution!
As always, thanks for looking!
If you have spent anytime with SSIS you have heard that Asynchronous components also known as “Blocking” components are bad for performance and should be avoided where possible. You have also heard some analogy or another that explained how these components “block” the data in the data flow and slow down performance. But I’m here to tell you that it gets much worse.
The data flow sends data down in buffers. Each buffer in the data flow will first utilize memory if available, the memory that is captured for that buffer of records will be released once the data flow is done with that buffer. For example once those records in that buffer have been loaded to the destination the buffer and the corresponding memory is released. This memory can now be used for the next buffer of memory coming down the pipeline in the data flow task. This is where the true evil with asynchronous components come in.
What happens if the buffer is not released because it got caught up in the data flow at some blocking transform? In a worse case scenario your machine/server becomes starved for memory and then SSIS is forced use disk instead of memory. Too many buffers spooled to disk can exponentially increase your package run time. Let me demonstrate.
For this example I have created two packages. Each package uses a sorting transform, however in different locations.
Package One. This package takes an unknown amount of time to run, I cut it off after approximately 68 minutes. The first screenshot shows the package in it’s current state, the second and third screenshot give some insight into why this is taking so long to run.
The following screenshot is obtained from SSMS after I run the package from integration services and then run the following query against the SSISDB.
- “SELECT * FROM [catalog].[dm_execution_performance_counters] (NULL) “
Screenshot 3: (Here we see the execution performance from the SSISDB)
Unfortunately there are times when you just have no choice and must use asynchronous components in SSIS. One option to consider, can the blocking transform be placed in a more strategic location to minimize the negative impact to performance? For example. In package one the sorting transform was placed before the lookup, the lookup is only forwarding non matching records to the destination, so why do the matching records need to be sorted? If matching records don’t need to be sorted then we can move the blocking transform to after the lookup and keep everything in memory.
Package Two: (This package completes in 1 minute and 20 seconds)
Thanks for looking!
I know I got your attention with that title. If you speak to any experienced BI Developer you will always be informed that you should perform all of your aggregations in T-SQL where possible in the source and not using the SSIS components. To this point I agree, well most of the time anyway. There are however times when using SSIS will out perform T-SQL if all the stars align. Let me explain.
Recently I was using a CTE and the row_number function in T-SQL to bring back only distinct customers from a list of transactions. This result set was then used to insert inferred members into the customer dimension, if that customer did not yet exist of course. Once the result set returned the list of distinct customers we performed a lookup against the customer dimension and we loaded the non matching customers into the customer dimension.
Now let me note, the T-SQL query honestly wasn’t that bad, it was only taking around 3 minutes to run. The bigger problem here was that the query was using temp db and this was causing locking issues with a more advanced design pattern (Partition switch) that the SQL Server Engine could not resolve.
So, we turned to good ole SSIS to help us out.
- First, bring all records from the source exactly as they were, all 30 million transactions.
- Next, perform the lookup to the customer dimension and redirect non matching rows to aggregate transform.
- Finally, use the aggregate transform to group the new customers and then load the distinct values into the customer dimension.
This works because we filter out all the existing customers before doing the aggregation in SSIS and therefore only small percentage of the records require aggregation. The data flow task to load inferred members runs in approximately 80 seconds, where as the original T-SQL query alone was taking over 3 minutes.
Thanks for looking!
There are quite a few different scenarios for when cache transforms can be used to improve usability and performance inside SSIS. In this post I am going to quickly outline a scenario where cache connection managers can greatly improve performance when loading a fact table.
During the pre-execution phase of package execution SSIS “caches” the lookups inside your package into memory, unfortunately if you are using OLE DB connections in your Data Flow Task this caching happens in a serialized format (one after the other). Typically this isn’t a big deal because you may only have one or two large dimension lookups and all others are very small in size and very fast. But what if this isn’t the case? What if you have 5, 6, 7, or more dimensions all with millions of records that you must cache before the package begins execution? Here is how we can get around this little issue.
- Inside the data flow tasks we will set each source to point to our cache transforms. Each one of the Cache Transforms will require a connection manager to store the data.
- Below is a screenshot of the connection managers created by the Cache transforms:
- Now that the cache connection managers have been set up, you will need to point each of your lookup transforms to the “Cache Connection manager” instead of the default of OLE DB connection manager.
- Now to the last step. Now click on the connection tab and point to the cache connection manager that stores the information for your lookup:
- All other configuration steps are the same as normal. Thanks for looking, and hope this helped.
Note: This walkthrough is specific to the Secure FTP component available in TaskFactory. TaskFactory is a product offered by Pragmatic Works consisting of 40+ Custom SSIS components for development.
Recently I worked with a client who had an interesting business problem. This client processed documents daily for hundreds of clients and then uploaded these files to each client’s Secure FTP site. They needed to set up the Secure FTP component so that they could dynamically change the connection based on the file being processed and uploaded. In this walkthrough I will explain how the connection string for your FTP Site can be set dynamically.
- Part 1 – Building the connection string.
- Part 2 – Setting an expression on the TF Secure FTP Connection Manager
(Note: This tutorial is modifying the connection string of an existing Task Factory sFTP connection manager. If you want to follow along please set up a basic TF Secure FTP task and create a connection manager.)
Part 1 – Building the TF Secure FTP connection string.
There are 18 parts to the connection string for the Secure FTP connection. However for this tutorial we are only concerned with 5 of the 18 sections, we will leave the other sections set to the default settings. What are the 5 sections we are concerned with? I’m glad you asked!
- Connection Type
- 1 = SFTP – SSH FTP
- 2 = FTPS – FTP over implicit TLS or SSL
- 3 = FTPES – FTP over explicit TLS or SSL
- 4 = FTP
- User Name
Ok let’s get started. For this example I created 5 variables, all 5 variables have a data type of String. For demonstration purposes I hardcoded the values for each variable. For the case referenced above the client populated these variables from a SQL Table that stored the connection information.
- Create the five variables in the screenshot above.
- Add these variables to the connection string. (Connection String Text below)
- If you used the same variable names as this tutorial you can simply copy the connection string below. (Section A)
- Here is a screenshot of where we changed our connection string.
- I replaced the hard coded value in the connection string with ” + @[User::VariableName] + “
“ConnectionType=” + @[User::strConnectionType] + “;Host=” + @[User::strHost] + “;Port=” + @[User::strPort] + “;Username=” + @[User::strUserName] +”;Password=” + @[User::strPassword] + “;Timeout=60;DefaultRemoteDirectory=;UseProxy=False;ReuseConnection=False;ProxyType=0; ProxyHost=;ProxyPort=0;ProxyUsername=;UseBinaryTransfer=True;UsePassiveMode=True; IgnoreServerIP=True;TransferBufferSize=0;SSLVersion=2;”
Part 2 – Setting an expression on the TF Secure FTP Connection Manager
- Under Connection Managers highlight your FTP Connection and hit F4 to open the properties window.
- In the properties window highlight “Expressions” and click the ellipsis button on the right.
- In the Property Expressions Editor choose ConnectionString for the Property.
- Next click the Ellipsis on the left to open up the Expression Builder.
- Now copy and paste the connection string into the Expression box.
- Evaluate the expression to verify it works.
- Click ok to close the Expression Builder.
- Click ok again to close the Property Expressions Editor.
- All Done!
Conclusion: Whether you are uploading files to multiple FTP sites or just want to set the Password dynamically because it changes periodically this method will help. Thanks for looking!
Follow me on twitter! @MitchellSQL