Agile BI Community

A common question on the SSIS forums is how to continue a loop after an error occurs. For example, you might have a For Each Loop container that iterates through a set of files in a folder. If one file is invalid, you want to continue processing the remaining files. Fortunately, there is a simple pattern that allows this to be accomplished. I ...

To finish up the topic that I started in my previous post, I wanted to post a more complex example of running total calculations. This is based off a request on the MSDN forums, for a running total calculation that would “slide” along as additional rows were processed. In the poster’s example, the running total needed to reflect the total for the ...

Running totals are a common calculation for reporting, and are occasionally needed in ETL processes to populate aggregate tables. One way to accomplish this in SSIS, if you are retrieving data from a relational database, is to do the running total in the SELECT statement. There are a number of resources on the Internet that illustrate how to ...

A common question with SSIS is how to handle adding headers and footers to a flat file when the format is different that the data rows. This gets more difficult when the header or footer needs to contain data based on the actual data rows, such as a total or a record count. Basically, this can be accomplished by creating the header or footer in a ...

Previously, I posted about how multiple errors in SSIS can be combined into a single unit for emailing or logging. Recently, a thread on the MSDN forums pointed out that the example wasn't quite complete. While it does show how to collect all the error messages and get them into a single string, it doesn't show how to email or log that string only ...

The OLE DB Destination supports using a variable to get the name of the target table. This allows you to switch the destination table dynamically, but there is a caveat. The columns in each table that you will use must match, or the OLE DB Destination will give a NEEDS_NEW_METADATA error. What's a potential way that you might use this ...

A recent post on the MSDN forums brought up an interesting problem. The poster was attempting to process a folder of files, all of which had the same format, but some were encoded as ASCII (ANSI, DT_STR, or varchar, if you like the SQL Server data types) and some were encoded as Unicode (DT_WSTR, nvarchar). The initial issue was determining ...

I recently had a need to scan through an SSIS package to find all instances of data flow tasks within the package. This is to support a future enhancement in BIDSHelper. However, I ended up making the code a little more general, so it can be used to get a list of all instances of any type of tasks within a package. Before looking at the code, ...

When writing script components in SSIS, it's often useful to be able to process the columns without knowing exactly what they are. For example, you may want to loop through all the columns to check for a conditional, like NULL values in the columns. Or you may want to take a set of columns and concatenate them into a single string for output, or ...

There's been a few questions on the MSDN Forums recently about using a list of keys in an IN condition in a WHERE clause. There are a few scenarios were this can be a bit tricky in SSIS, particularly if you need to dynamically create the list of keys. As an example, let's look at the AdventureWorksDW database. Suppose that you need to extract rows ...