Search results matching tags 'SSIS' and '2008 R2'

Forcing “Custom Properties” of a Data Flow Transformation to support DTS Expression

manowar — Wed, 30 Jan 2013 16:48:22 GMT

Today I was using a 3rd Party Data Flow component that has several Custom Properties for which I need to change some of their values at runtime using a DTS Expression.

To mimic the situation let’s use the “Percentage Sampling” that has two Custom Properties:

Of the two Custom Properties only some (one in this case) are available also outside that dataflow, so that they can be targeted by a DTS Expression. Such properties are listed under the “Misc.” section of Data Flow properties

and also in the Property Expression Editor window:

Now, what if you need to make also the “hidden” custom properties available for DTS Expression usage? As you may have noticed, the SamplingSeed is not exposed outside the Data Flow. I tried to search the web, but after several minutes I gave up since I wasn’t able to find anything that could help. I then started to look into the SSIS object model and I found a nice property named expressionType in the IDTSCustomProperty interface that tells to the engine if the property value can be specified using DTS Expression or not:

http://msdn.microsoft.com/en-us/library/bb510794.aspx

If the value is set to Notify than the usage of DTS Expression is possible otherwise, if the property is set to None, as the name implies, DTS Expression cannot be used.

So all you need to do is to open the .dtsx file, look for the component you want to touch and its properties

and add (if not exists) or change the expressionType attribute to Notify:

Save the file and voilà, the property is now available for DTS Expression usage!

Now, just be aware that is a sort of an hack, so double check it works for you. On the 3rd party components we’re using it works like a charm, and it just saved that day since without the ability to change some properties at run time, the deployment of our package in production could have been a nightmare.

I tested this approach both on SQL Server 2012 and SQL Server 2008 and in both cases I hadn’t had any problems.

Hope this helps someone , enjoy!

DTLoggedExec 1.1.2008.4 SP2 released!

manowar — Wed, 01 Feb 2012 17:15:44 GMT

I’ve released couple of hours ago the SP2 of my DTLoggedExec tool.

For those who don’t know it, it’s a DTEXEC replacement, useful to execute SSIS and having logging provided right from the engine and not from the package itself.

More info can be found here:

http://dtloggedexec.codeplex.com/

This SP2 release add an important feature to the CSV Log Provider. It's now possible to store a personalized label into each log, in order to make it easy to identify or group logs.

Let's say, for example, that you have 10 packages in your ETL solution, and each time you have to load your data, you need to execute all those 10 packages. In other words , you have a batch made of 10 packages. It would be nice if all logs - one for each package - can be identified as a whole and grouped together, in order to quickly identify all the log of a articular batch.

This will make things easier when you want to know the overal time consumed by each batch execution.

The new "ExecutionLabel" attribute will help to achieve this. A useful ExecutionLabel can be obtained using SQL Server Agents Token. For example:

LABEL=$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))

ChangeLog

Updated the CSV Log Provider in order to write the status of the log file in the header.
- An OPEN status means that the log file is being written.
- A CLOSED status means that the log files has been written correctly.
- A file can be loaded into the database log only if is in the CLOSED state.
CSV Log files have 2 additional rows in the header: one for the FileStatus and one for the ExecutionLabel values.
- File format has been update to 4 from 3. (Only the header section of the file has been changed).
CSV Log Provider will now display EndTime value to the Console
Updated the import-log-data.sql to correctly load file with format 3 (the old one) and 4 (the new one).
Updated database schema to version 19 in order to store the new ExecutionLabel value
Update samples in order to show how to use the new ExecuteLabel option

As usual the download is available for free here:

http://dtloggedexec.codeplex.com/releases/81526/download/336490

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

manowar — Sun, 29 May 2011 13:44:59 GMT

I’m very happy to announce that my proposal for the Spotlight session I’ve been invited to deliver at PASS 2011 has been accepted!

Temporal Snapshot Fact Table

You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day.

If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.

In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.

The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.

The topic is very interesting and – I bet – very intriguing for many people working in BI and specially with Analysis Services, since it lacks the support of “time intervals” to define the validity period of a fact row. With my SolidQ Italian collegues we’ve been able to find a way to overcome this limitation, allowing the storage of daily snapshots of data with a very high efficency and performance.

In this session I’m going to share everything we discovered with you. It will be really interesting, I can tell you! Probably one of the most advanced – yes simple - usage of SSAS and Many-To-Many relationship you’re going to see.

PASS Summit 2010 – BI Workshop

manowar — Wed, 30 Jun 2010 12:01:10 GMT

PASS Summit 2010 Pre & Post conference are out! This year I’ll deliver the “Creating BI Solution from A to Z” seminar in which, as the title implies, attendees will see how to create a BI solution starting from scratch. Going through the dimensional modeling and the creation of the Datawarehouse, the implementation of the ETL process with SSIS, the creation of cube with Analysis Services and reports with Reporting Services with, if time permits, also a glance at PowerPivot, attendees will get a solid ground on the whole process that drives the creation of a BI solution.

The workshop will also show all the best practices and the best methodological approach matured in more than 5 years of working in the BI field.

If you’ll be working on BI or you already have a created an initial BI solution and you want to be sure you’re following the right path, this is workshop is for you!

Don’t miss it!

DTLoggedExec 1.0 Stable Released!

manowar — Sun, 09 May 2010 08:51:43 GMT

After serveral years of development I’ve finally released the first non-beta version of DTLoggedExec! I’m now very confident that the product is stable and solid and has all the feature that are important to have (at least for me).

DTLoggedExec 1.0
http://dtloggedexec.codeplex.com/releases/view/44689

Here’s the release notes:

FIRST NON-BETA RELEASE! :)
Code cleaned up
Added SetPackageInfo method to ILogProvider interface to make easier future improvements
Deprecated the arguments 'ProfileDataFlow', 'ProfilePath', 'ProfileFileName'
Added the new argument 'ProfileDataFlowFileName' that replaces the old 'ProfileDataFlow', 'ProfilePath', 'ProfileFileName' arguments
Updated database scripts to support new reports
Split releases in three different packages for easier maintenance and updates: DTLoggedExec Executable, Samples & Reports
Fixed Issue #25738 (http://dtloggedexec.codeplex.com/WorkItem/View.aspx?WorkItemId=25738)
Fixed Issue #26479 (http://dtloggedexec.codeplex.com/WorkItem/View.aspx?WorkItemId=26479)

To make things easier to maintain I’ve divided the original package in three different releases. One is the DTLoggedExec executable; samples and reports are now available in separate packages so that I can update them more frequently without having to touch the engine.

Source code of everything is available through Source Code Control:

http://dtloggedexec.codeplex.com/SourceControl/list/changesets

As usual, comments and feebacks are more than welcome! (Just use Codeplex, please, so it will be easier for me to keep track of requests and issues)