Search results matching tag 'SSIS'

Announcing the 2013 Biml Workshop 15 Oct 2013 in Charlotte NC!

andyleonard — Sat, 18 May 2013 04:00:00 GMT

When:

October 15, 2013
8:45 am - 4:45 pm
Charlotte, NC

Where:

Wake Forest University Charlotte Center
200 North College Street
Charlotte, NC 28202

What:

Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. Come see why BI professionals around the world think Biml is the future of data integration and BI.

Registration is just $69. Register before July 15th and receive early bird discount of just $49. Breakfast, lunch, & refreshments are also included. Seating is limited. Register now to guarantee your spot.

:{>

Little Data Remains Important in Healthcare IT

andyleonard — Tue, 30 Apr 2013 14:31:24 GMT

In his article Healthcare's Big Problem With Little Data, author Dan Munro raises salient points about the state of health-related data. Electronic Health Records (EHR) were promoted as the end-all-be-all solution for the industry – a standardization that, I suppose, many thought would organically and naturally occur, stabilize, and be maintained.

It hasn’t. At least not yet.

My doctor and I speak about this almost each time I visit with him. The corporation that operates his practice nowadays seems endlessly locked in cycles of changing billing and EHR systems in search of low-cost compliance and integration. They’ve (literally) spent millions of dollars and my doctor hates the interfaces forced upon him and his patients (well, one, at least) hates the complexity of the billing and patient records systems. Can’t these systems all just get along?

The result? Higher medical data management costs. I’ll give you one guesses who pays these costs.

Munro posits the following from his article:

By at least one estimate (here) there are now about 500 independent EHR vendors. Out of that large group is a subset of about 400 with at least one customer that has applied for Federal stimulus dollars through the labyrinthine process of meaningful use attestation. That would suggest a “first-cut” of about 100 vendors who made some commitment around certification – but have no reported customers (at least to date). That’s a staggering number of single-purpose software vendors for any industry to support – even bloated healthcare. The simple fact is it can’t. While there have been a few high-profile cases of EHR vendors shutting down, this last week was the first high-profile example of a vendor that was effectively decertified by the Feds for both their “ambulatory” and their “inpatient” EHR products. From the HHS.gov website last Thursday:

“We and our certification bodies take complaints and our follow-up seriously. By revoking the certification of these EHR products, we are making sure that certified electronic health record products meet the requirements to protect patients and providers,” said Dr. Mostashari.“Because EHRMagic was unable to show that their EHR products met ONC’s certification requirements, their EHRs will no longer be certified under the ONC HIT Certification Program.”

You may ask yourself, well, how did we get here? This, folks, is a mess. What’s missing? Applied standards.

“But Andy, you’ve told us standards slow down development!”

And I stand by that statement; standards do slow down development…unless you’re building interfaces. And then standards become the means for decoupled snippets, functions, methods, applications, and even platforms to communicate with each other. In some cases, we simply cannot be productive without standards – like TCP/IP. What would happen if everyone coded their own version of internet traffic? If that was the case, very few of you would reading this post.

Yes, standards slow things down. And yes, they are necessary to insure base functionality. In my humble opinion, we have to get this right with healthcare data. We simply must. While we see similar issues of data management across many fields, medical data is too important to mess around with; it’s (often literally) life and death. And it is certainly a high cost.

More to Consider

Standards exist. Administering and certifying 400-500 vendor solutions is hard.

Part of the Solution

From the actions of the Department of Health and Human Services last week, one can ascertain HHS is taking steps to address the matter. But will all 400-500 companies voluntarily congeal their schemas? Possibly, but doubtful.

My experience delivering US state Medicaid ETL solutions informs me there will be a need for data integration – regardless of the existence of standards and in spite of certification. Why? Standards are not static. The idea of de facto standards emerges from the life cycle of software because software is organic. Even if everyone agreed on the same interpretation of rigid standards (and they won’t), versions 2.0 through n.n will – at a minimum – add fields to the schema. And with additional fields comes additional data.

Standards will be revised when enough product schemas adopt the de facto, and this will drive the need for yet more integration. Don’t take my word for it, examine the entropic history of ICD-9 and ICD-10 codes – the direction of progress is more data, not less.

Learn More

This is one reason we at Linchpin People are focusing on Medical Data Integration. The recording of our first (free!) webinar about Medical Data Integration with SSIS 2012 is available here. Kent Bradshaw and I continue the series tomorrow presenting Medical Data Integration with SSIS 2012, Part 2 in which we focus on loading Provider and Drug data.

I hope to see you there!

:{>

SSIS gotcha – Regional Settings can affect your expressions

jamiet — Tue, 09 Apr 2013 09:05:33 GMT

I recently stumbled across a nuance of the SSIS expression language which, when you think about, kinda make sense – but it does help to be aware of it. Its concerned with casting of datetime values using the SSIS expression language

Take the following expression:

(DT_WSTR,30) @[System::ContainerStartTime]

That expression casts a datetime value into a string value. If I evaluate that with my OS Regional Settings set to English (United Kingdom) I see this:

If I set my OS Regional Settings to English (United States) I see this:

Note how that simple change to the regional settings has caused the result of my expression to change. This could have dangerous consequences; for example, if you are using the result of this expression in a dynamically built SQL statement (as I was) then one of two things will happen, either you will get the wrong result or you’ll get an error. Observe how, n my dynamically built SQL statement, I’m CONVERTing a string literal (which is constructed using the above expression) to a datetime value:

however with a simple change of my regional settings to English (United States) I see this:

and when you run that particular SQL statement in SSMS:

it blows up!

Definitely one to be aware of! Watch those Regional Settings and their affect on casting of dates in the SSIS expression language!

What should you do instead?

If you need a failsafe way of constructing a date that doesn’t rely on Regional settings then consider something like the following:

(DT_WSTR,4)YEAR( @[System::ContainerStartTime] ) + "-" +
RIGHT("0" + (DT_WSTR,2)MONTH( @[System::ContainerStartTime] ), 2) + "-" +
RIGHT("0" + (DT_WSTR,2)DAY(@[System::ContainerStartTime] ), 2)

That expression will build a date string with format YYYY-MM-DD (which is the ISO-ratified unambiguous way of representing a date) regardless of Regional Settings:

@Jamiet

SSIS 2012 Deep Dive presentation

jamiet — Fri, 22 Mar 2013 15:55:10 GMT

This is something I’ve been meaning to blog about for ages but it kept slipping my mind, sorry about that!

Wee Hyong Tok from the SSIS product team has built a slide deck that covers some of the deep secrets about SSIS2012 including:

Catalog deep dive
Security
Low level monitoring and troubleshooting

The slide deck is available to view online at https://skydrive.live.com/redir?resid=BB8E1FF2CE0CD545!252&authkey=!AMFavRXK0aVq314.

As its a slide deck its not quite as good as hearing from the presenter himself however there’s still some really useful information in here. If the internals of SSIS float your boat then click through and take a look (there are only 22 slides).

@Jamiet

sp_ssiscatalog v1.0.3.0 available now [SSIS]

jamiet — Fri, 15 Mar 2013 22:19:07 GMT

I’ve just put out a new version of sp_ssiscatalog. The main change is requesting a list of executions using:

exec sp_ssiscatalog @operation_type='execs'

returns a new field called relative_duration:

This length of the “bar” in that field represents the duration of that execution relative to all the other executions in the resultset. Put more simply, its an attempt to simply visualise which executions took the longest to complete.

The other change in this release is the addition of a new parameter called @ot which is simply a short form of @operation_type. Its there because I basically got fed up of typing @operation_type every time I wanted a list of executions. hence the statement above can now be written as:

exec sp_ssiscatalog @ot='execs'

That’s it, you can download the latest bits from http://ssisreportingpack.codeplex.com/releases/view/103452. Installation instructions are in my previous blog post on sp_ssiscatalog sp_ssiscatalog v1.0.2.0 now available for download [SSIS].

Look for more updates coming soon, I’m hoping to pick up the pace of iterations on this.

@Jamiet

sp_ssiscatalog v1.0.2.0 now available for download [SSIS]

jamiet — Mon, 11 Mar 2013 22:51:20 GMT

v1.0.2.0 – what’s in it?

Things have been a bit quiet on the sp_ssiscatalog front since I last blogged about it three months ago in December 2012. Rest-assured development continues apace however and today I’m making available a minor update, v1.0.2.0 which is now available for download from Codeplex. For those that don’t know I describe sp_ssiscatalog as:

sp_ssiscatalog is a stored procedure that makes it easy to query for information that is strewn around the SSIS Catalog.

There aren’t too many functional changes in this release, it is more focused on making sp_ssiscatalog easier to use. Back in Documenting sp_ssiscatalog I explained how I was adding documentation to the messages tab of SSMS. Hence as of this new release when you execute sp_ssiscatalog you will see information such as this:

which I think should be very useful for anyone that wants to use sp_ssiscatalog to its fullest. Even I who wrote the thing and has been using it day-in, day-out for quite some time now can’t remember the names of all the parameters – now I no longer have to!

Note that you can turn off the display of the documentation using the @show_docs parameter:

exec sp_ssiscatalog @show_docs=0

If you want to display only the documentation and not actually have sp_ssiscatalog do any querying of the SSIS Catalog its @show_docs_only:

exec sp_ssiscatalog @show_docs_only=1

If you have any suggestions for future enhancements please put them in the comments below or submit them to the discussions page on the Codeplex site.

As a reminder, here is the sort of thing you can do with sp_ssiscatalog:

--Return all failed executions  
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed'


--Return all executions for a specified folder
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_folder_name='My folder'


--Return all executions of a specified package in a specified project
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_project_name='My project', @execs_package_name='Pkg.dtsx'


--Return information about the most recent execution
EXEC [dbo].[sp_ssiscatalog]

One last thing, if sp_ssiscatalog is useful to you and you’d like to support future development feel free to donate to my personal beer fund at http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&business=jamie@jamie-thomson.net&item_name=Supporting%20sp_ssiscatalog.

Installation Instructions

Download the zip file at DB v1.0.2.0. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
Unzip to a folder of your choosing
Open a command prompt and change to the directory into which you unzipped the files
Execute:
- "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
  (/tsn specifies the target server, change as appropriate. /tdn specifies the database name, you can call it whatever you like.)

If everything works OK you’ll see something like the following:

This will (if it doesn’t already exist) create a database called [SsisReportingPack] (or whatever you chose to call it) which contains [dbo].[sp_ssiscatalog].

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!

From Zero To SSIS Training: 4-8 Mar 2013 in Reston Virginia

andyleonard — Thu, 10 Jan 2013 16:00:00 GMT

Want to learn more about SSIS? I can help. In my course - From Zero To SSIS – I teach you how to effectively use SSIS to deliver data integration solutions.

This is not a market-y survey of the bells and whistles contained in SSIS. I focus on the tasks data integration and ETL developers will use to produce SSIS packages in the enterprise.

How do I know which tasks are used most?

I have been using SSIS since the early beta versions. For over two years, I managed a team of ETL developers at Unisys as we built Medicaid solutions for two state governments and maintained the Medicaid solution for another state. When we needed more people, I had a hard time finding experienced SSIS developers. I needed a way to bring less-experienced developers up to speed quickly. I found a way to train data integration developers to use SSIS to build enterprise-ready, metadata-framework-driven SSIS applications in less than one week. The experience served as the basis for this course which has been delivered publicly and privately to hundreds of students over the past two years.

You can learn more about – and register for – the course here.

Advanced registration is available until 1 Feb 2013: $2,749 USD

Registration after 1 Feb 2013: $2,999 USD

I hope to see you there!

:{>

Microsoft advocates checkpoints - proceed with caution [SSIS]

jamiet — Fri, 04 Jan 2013 13:28:15 GMT

In December 2012 Microsoft published a whitepaper entitled SSIS Operational and Tuning Guide which you can download by clicking here.

I have skimmed through the whitepaper and it appears to be a compelling read however a couple of sentences caught my eye that I want to draw attention to here. These sentences refer to the use SSIS checkpoint files:

Some of the most common challenges with SSIS packages are in how to handle unexpected failures during execution, and how to minimize the amount of time required to finish the execution of an ETL process when you must resume processing after a failure. For control flow tasks such as file system tasks, checkpoints can be used to resume execution without reprocessing work that has already been completed.
When designing packages, one of the largest concerns is designing so that in the event of failure, you do not lose all of the progress the package has made up to this point. For items in the control flow of a package, this is accomplished by the use of checkpoints.

In blog post Why I don't use SSIS checkpoint files from March 2011 I explain my experiences of SSIS checkpoint files and why I choose not to use them. In addition there are a number of Connect submissions that speak to the problems associated with SSIS checkpoints:

SSIS Checkpoint File Object Type issue with complex data types
Peculiarity using CheckPoint files and the OnPostExecute event handler
The values of Object variables do not get stored in checkpoint files
Checkpoints within containers
Make it easier to configure restartability

Full disclosure – two of those were submitted by myself

Microsoft have not (as far as I know) publicly acknowledged any issue with SSIS checkpoints however I am certain that internally the SSIS product team know the current implementation is inadequate for many scenarios. With this in mind I am disappointed that a Microsoft whitepaper openly advocates the use of them without at least acknowledging their deficiencies.

Designing for restartability is hard. I personally choose not to use checkpoints and instead implement my own custom restartability solution; I am not saying steer clear at all costs but I hope that if you choose to use them that you both understand the implications of doing so and test that they behave as you expect.

@Jamiet

Using Enterprise Data Integration Dashboards Recording Available

andyleonard — Thu, 13 Dec 2012 12:00:00 GMT

The recording of the presentation Using Enterprise Data Integration Dashboards is now available!

Enjoy!

:{>