Search results matching tag 'Integration Services'

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 Service Pack 1 released

manowar — Mon, 25 Jul 2011 19:16:39 GMT

Today I’ve released the first Service Pack of DTLoggedExec (for those who doesn’t know what it is: DTLoggedExec is a DTExec replacement to run Integration Services packages):

http://dtloggedexec.codeplex.com

This Service Pack fixes some little problems with the .bat and .sql files that comes with DTLoggedExec. All the fixes were already published as single changesets (86188, 86299, 87778, 88124 and 91054) and the Service Pack put them all togheter for users convenience.

You can download the full DTLoggedExec package with the SP1 already integrated or the single Service Pack 1 that you can integrate manually in your existing installations (all you have to do is to overwrite the existing files):

http://dtloggedexec.codeplex.com/releases/view/70641

SQL Server v.Next (Denali) : Breaking change to system databases / database_id / DB_ID()

AaronBertrand — Fri, 08 Jul 2011 13:09:00 GMT

Currently you may have code that makes decisions based on sys.databases.database_id or the built-in DB_ID() function. I have seen a lot of code out there that checks the database_id, and behaves differently based on the assumption that a value between 1 and 4 (as well as 32,767 - the resource database) means it is a system database, and anything in between (from 5 to 32,766) means it is a user database. I also see code that checks the name of the database (IN ('master', 'model', 'msdb', 'tempdb')).

In Denali, if you install SQL Server Integration Services, all of the packages and other metadata, as well as operational metrics, are stored in an "SSIS Catalog" now. The catalog is called SSISDB but, other than the fact that it also exists under the Integration Services node in Management Studio's Object Explorer, it is just another database and adopts the next available database_id. On my system, with 11 user databases, the database_id for SSISDB was 16. As you probably already know, there is nothing in sys.databases to indicate that a database is shipped with SQL Server or is otherwise designated as a "system" database. And there is no revealing property exposed by DATABASEPROPERTYEX(), either (nor by DATABASEPROPERTY(), which of course is deprecated in Denali anyway).

What this means is that you *may* want to consider updating any such code to also check for the name SSISDB. Why do I say *may*? Well, I don't know all of the scenarios you're currently using that cause you to differentiate between system and user databases. It could be for backup purposes, index maintenance, making snapshots, who knows... depending on the task, you may want to conditionally consider SSISDB a system database or a user database. What I do know is that if you want to consider SSISDB a system database, you won't be able to lump it in with other system databases relying solely on database_id / DB_ID().

As another note, currently it seems there is no way to customize the name of the SSIS catalog when you add Integration Services (whether that is during initial SQL Server setup, or after the fact). What this means for folks who already have a user database named SSISDB before they install Integration Services and create the catalog? Well, unless your database magically has the schema that the UI expects, when you try to expand Integration Services in Object Explorer in SSMS, you get this exception (click to embiggen):

Here are the interesting and search-friendly parts of the error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'SSISDB.catalog.catalog_property'. (Microsoft SQL Server, Error: 208)

If you drop or rename the existing SSISDB database, then Management Studio works as expected - you can right-click Integration Services and choose "Create Catalog" and it will create the SSISDB database for you.

Since we can't test upgrades using the CTPs (as they only ship in Evaluation Edition), I am not sure what is going to happen if you upgrade in place when you have SSIS installed *and* you have your own user database called SSISDB. It's possible, I guess, that SSIS will just take over that DB and inject its own schema, but then what if there are conflicts (e.g. you already have a schema named catalog).

Also not sure what happens to all of the surrounding functionality if you decide to manually rename the database, I'm not sure. I'm not an SSIS guy, so I don't really have the means to test this, and I don't see any official documentation or blogs that talk about this scenario.

For some of this, I guess there's no way to know until RTM - but to play it safe, if you currently happen to have a database called SSISDB, you might consider changing it now if you use SSIS and are planning to move to Denali.

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.

Auto-Configuring SSIS Packages

manowar — Wed, 16 Mar 2011 13:32:39 GMT

SSIS Package Configurations are very useful to make packages flexible so that you can change objects properties at run-time and thus make the package configurable without having to open and edit it.

In a complex scenario where you have dozen of packages (even in in the smallest BI project I worked on I had 50 packages), each package may have its own configuration needs. This means that each time you have to run the package you have to pass the correct Package Configuration. I usually use XML configuration files and I also force everyone that works with me to make sure that an object that is used in several packages has the same name in all package where it is used, in order to simplify configurations usage. Connection Managers are a good example of one of those objects. For example, all the packages that needs to access to the Data Warehouse database must have a Connection Manager named DWH.

Basically we define a set of “global” objects so that we can have a configuration file for them, so that it can be used by all packages.

If a package as some specific configuration needs, we create a specific – or “local” – XML configuration file or we set the value that needs to be configured at runtime using DTLoggedExec’s Package Parameters:

http://dtloggedexec.davidemauri.it/Package%20Parameters.ashx

Now, how we can improve this even more? I’d like to have a package that, when it’s run, automatically goes “somewhere” and search for global or local configuration, loads it and applies it to itself.

That’s the basic idea of Auto-Configuring Packages.

The “somewhere” is a SQL Server table, defined in this way

In this table you’ll put the values that you want to be used at runtime by your package:

The ConfigurationFilter column specify to which package that configuration line has to be applied. A package will use that line only if the value specified in the ConfigurationFilter column is equal to its name. In the above sample. only the package named “simple-package” will use the line number two.

There is an exception here: the $$Global value indicate a configuration row that has to be applied to any package. With this simple behavior it’s possible to replicate the “global” and the “local” configuration approach I’ve described before.

The ConfigurationValue contains the value you want to be applied at runtime and the PackagePath contains the object to which that value will be applied. The ConfiguredValueType column defined the data type of the value and the Checksum column is contains a calculated value that is simply the hash value of ConfigurationFilter plus PackagePath so that it can be used as a Primary Key to guarantee uniqueness of configuration rows.

As you may have noticed the table is very similar to the table originally used by SSIS in order to put DTS Configuration into SQL Server tables:

SQL Server SSIS Configuration Type: http://msdn.microsoft.com/en-us/library/ms141682.aspx

Now, how it works?

It’s very easy: you just have to call DTLoggedExec with the /AC option:

DTLoggedExec.exe /FILE:”mypackage.dtsx” /AC:"localhost;ssis_auto_configuration;ssiscfg.configuration"

the AC option expects a string with the following format:

<database_server>;<database_name>;<table_name>;

only Windows Authentication is supported.

When DTLoggedExec finds an Auto-Configuration request, it injects a new connection manager in the loaded package. The injected connection manager is named $$DTLoggedExec_AutoConfigure and is used by the two SQL Server DTS Configuration ($$DTLoggedExec_Global and $$DTLoggedExec_Local) also injected by DTLoggedExec, used to load “local” and “global” configuration.

Now, you may start to wonder why this approach cannot be used without having all this stuff going around, but just passing to a package always two XML DTS Configuration files, (to have to “local” and the “global” configurations) doing something like this:

DTLoggedExec.exe /FILE:”mypackage.dtsx” /CONF:”global.dtsConfig” /CONF:”mypackage.dtsConfig”

The problem is that this approach doesn’t work if you have, in one of the two configuration file, a value that has to be applied to an object that doesn’t exists in the loaded package. This situation will raise an error that will halt package execution.

To solve this problem, you may want to create a configuration file for each package. Unfortunately this will make deployment and management harder, since you’ll have to deal with a great number of configuration files.

The Auto-Configuration approach solve all these problems at once!

We’re using it in a project where we have hundreds of packages and I can tell you that deployment of packages and their configuration for the pre-production and production environment has never been so easy!

To use the Auto-Configuration option you have to download the latest DTLoggedExec release:

http://dtloggedexec.codeplex.com/releases/view/62218

Feedback, as usual, are very welcome!

DTLoggedExec 1.1.2008.4 Released!

manowar — Tue, 15 Mar 2011 11:42:00 GMT

Today I've relased the latest version of my DTExec replacement tool, DTLoggedExec.

The main changes are the following:

Used a new strategy for version numbers. Now it will follow the following pattern Major.Minor.TargetSQLServerVersion.Revision
Added support for Auto Configurations
Fixed a bug that reported incorrect number of errors and warnings to Log Providers
Fixed a buf that prevented correct casting of values when using /Set and /Param options
Errors and Warnings are now counted more precisely.
Updated database and log import scripts to categorize logs by projects and sections. E.g.: Project: MyBIProject; Sections: Staging, Datawarehouse
Removed unused report stored procedures from database
Updated Samples: 12 samples are now available to show ALL DTLoggedExec features
From this version only SSIS 2008 will be supported

http://dtloggedexec.codeplex.com/releases/view/62218

It useful to say something more on a couple of specific points:

From this version only SSIS 2008 will be supported
Yes, Integration Services 2005 are not supported anymore. The latest version capable of running SSIS 2005 Packages is the 1.0.0.2.

Updated database and log import scripts to categorize logs by projects and sections
When you import a log file, you can now assign it to a Project and to a Section of that project. In this way it's easier to gather statistical information for an entire project or a subsection of it. This also allows to store logged data of package belonging to different projects in the same database. For example:

Updated Samples
A complete set of samples that shows how to use all DTLoggedExec features are now shipped with the product. Enjoy!

Added support for Auto Configurations
This point will have a post on its own, since it's quite important and is by far the biggest new feature introduced in this release. To explain it in a few words, I can just say that you don't need to waste time with complex DTS configuration files or options, since a package will configure itself automatically. You just need to write a single statement as a parameter for DTLoggedExec. This feature can simplify deployment *a lot* :)

I the next days I'll write the mentioned post on Auto-Configurations and i'll update the documentation available on theDTLoggedExec website:

http://dtloggedexec.davidemauri.it/MainPage.ashx

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!

SSIS Expression Tester Tool

manowar — Sun, 13 Jun 2010 08:56:05 GMT

Thanks to my friend's Doug blog I’ve found a very nice tool made by fellow MVP Darren Green which really helps to make SSIS develoepers life easier:

http://expressioneditor.codeplex.com/Wikipage?ProjectName=expressioneditor

In brief the tool allow the testing of SSIS Expression so that one can evaluate and test them before using in SSIS packages. Cool and useful!

SSIS packages incompatibilities between SSIS 2008 and SSIS 2008 R2

sqlbi — Thu, 13 May 2010 22:46:00 GMT

When you install SQL 2008 R2 workstation components you get a newer version of BIDS (BI Developer Studio, included in the workstation components) that replaces BIDS 2008 version (BIDS 2005 still live side-by-side). Everything would be good if you can use the newer version to edit any 2008 AND 2008R2 project.

SSIS editor doesn't offer a way to set the "compatibility level" of the package, becuase it is almost all unchanged. However, if a package has an ADO.NET Destination Adapter, there is a difference in XML format that requires some manual changes to move a SSIS project back and forth between BIDS 2008 and BIDS 2008R2.

John Welch wrote a very good post about this issue, explaining how to make the changes if you need to.

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)