Search results matching tag 'sql server integration services'

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.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].

The current state of a MERGE Destination for SSIS

jamiet — Fri, 07 Dec 2012 11:18:45 GMT

Hugo Tap asked me on Twitter earlier today whether or not there existed a SSIS Dataflow Destination component that enabled one to MERGE data into a table rather than INSERT it. Its a common request so I thought it might be useful to summarise the current state of play as regards a MERGE destination for SSIS.

Firstly, there is no MERGE destination component in the box; that is, when you install SSIS no MERGE Destination will be available. That being said the SSIS team have made available a MERGE destination component via Codeplex which you can get from http://sqlsrvintegrationsrv.codeplex.com/releases/view/19048.

I have never used it so cannot vouch for its usefulness although judging by some of the reviews you might not want to set your expectations too high. Your mileage may vary.

In the past it has occurred to me that a built-in way to provide MERGE from the SSIS pipeline would be highly valuable. I assume that this would have to be provided by the database into which you were merging hence in March 2010 I submitted the following two requests to Connect:

BULK MERGE (111 votes at the time of writing)
[SSIS] BULK MERGE Destination (15 votes)

If you think these would be useful feel free to vote them up and add a comment.

Lastly, this one is nothing to do with SSIS but if you want to perform a minimally logged MERGE using T-SQL Sunil Agarwal has explained how at Minimal logging and MERGE statement.

@Jamiet

The perils of double-dash comments [T-SQL]

jamiet — Thu, 06 Dec 2012 09:21:39 GMT

I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re developing SSIS packages so head over to SSIS OLE DB Source, Parameters And Comments: A Dangerous Mix! and be educated. Note that the problem is solved in SSIS2012 and Valentino explains exactly why.

If reading Valentino’s post has switched your brain into “learn mode” perhaps also check out my post SSIS: SELECT *... or select from a dropdown in an OLE DB Source component? which highlights another issue to be aware of when using the OLE DB Source.

As I was reading Valentino’s post I was reminded of a slidedeck by Chris Adkin entitled T-SQL Coding Guidelines where he recommends never using double-dash comments:

That’s good advice!

@Jamiet

sp_ssiscatalog v1.0.1.0 now available for download

jamiet — Tue, 20 Nov 2012 21:42:09 GMT

13 days ago I wrote a blog post entitled Introducing sp_ssiscatalog (v1.0.0.0) in which I first made mention of sp_ssiscatalog, an open source stored procedure intended to make it easy to query the SSIS Catalog. I have been working on some enhancements since then and hence v1.0.1.0 is now available for download from Codeplex.

What’s new in this release

This release includes the following enhancements:

[execution_id] now gets returned in a call to

EXEC [dbo].[sp_ssiscatalog] @operation_type='exec';
Filter events by specifying packages to ignore
EXEC [dbo].[sp_ssiscatalog] @operation_type='exec',@exec_events_packagesexcluded='SomePackage.dtsx,AnotherPackage.dtsx';
[event_message_id] is now returned in a list of events
List of executions can now be filtered via a minimum and maximum execution_id

EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_minimum_execution_id=198,@execs_maximum_execution_id=201
Events resultsets now have a field, [event_message_context_xml] that contains an XML document containing all [event_message_context] info (if any exists)

Installation instructions

Download the zip file at DB v1.0.1.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.)

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

or

depending on whether the target database already exists or not

This will create a database called [SsisReportingPack] which contains [dbo].[sp_ssiscatalog]

Feedback is welcomed!

@Jamiet

SSIS Reporting Pack – a performance tip

jamiet — Mon, 05 Nov 2012 22:36:41 GMT

SSIS Reporting Pack is a suite of open source SQL Server Reporting Services (SSRS) reports that provide additional insight into the SQL Server Integration Services (SSIS) 2012 Catalog. You can read more about SSIS Reporting Pack here on my blog or had over to the home page for the project at http://ssisreportingpack.codeplex.com/.

After having used SSRS Reporting Pack on a real project for a few months now I have come to realise that if you have any sizeable data volumes in [SSISDB] then the reports in SSIS Reporting Pack will suffer from chronic performance problems – I have seen the “execution” report take upwards of 30minutes to return data. To combat this I highly recommend that you create an index on the [SSISDB].[internal].[event_messages].[operation_id] & [SSISDB].[internal].[operation_messages].[operation_id] fields. Phil Brammer has experienced similar problems himself and has since made it easy for the rest of us by preparing some scripts to create the indexes that he recommends and he has shared those scripts via his blog at http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip. If you are using SSIS Reporting Pack, or even if you are simply querying [SSISDB], I highly recommend that you download Phil’s scripts and test them out on your own SSIS Catalog(s).

Those indexes will not solve all problems but they will make some of your reports run quicker. I am working on some further enhancements that should further improve the performance of the reports. Watch this space.

@Jamiet

Querying the SSIS Catalog? Here’s a handy query!

jamiet — Wed, 17 Oct 2012 12:05:57 GMT

I’ve been working on a SQL Server Integration Services (SSIS) solution for about 6 months now and I’ve learnt many many things that I intend to share on this blog just as soon as I get the time. Here’s a very short starter-for-ten…

I’ve found the following query to be utterly invaluable when interrogating the SSIS Catalog to discover what is going on in my executions:

SELECT event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_type
FROM   (
       SELECT  em.*
       FROM    SSISDB.catalog.event_messages em
       WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
           AND event_name NOT LIKE '%Validate%'
       )q
/* Put in whatever WHERE predicates you might like*/
--WHERE	event_name = 'OnError'
--WHERE	package_name = 'Package.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC

Know it. Learn it. Love it.

@jamiet

Should we be able to deploy a single package to the SSIS Catalog?

jamiet — Tue, 16 Oct 2012 08:18:14 GMT

My buddy Sutha Thiru sent me an email recently asking about my opinion on a particular nuance of the project deployment model in SQL Server Integration Services (SSIS) 2012 and I’d like to share my response as I think it warrants a wider discussion. Sutha asked:

Jamie

What is your take on this?
http://www.mattmasson.com/index.php/2012/07/can-i-deploy-a-single-ssis-package-from-my-project-to-the-ssis-catalog/

Overnight I was talking to Matt who confirmed that they got no plans to change the deployment model.
For example if we have following scenrio how do we do deploy?

Sprint 1
Pkg1, 2 & 3 has been developed and deployed to UAT. Once signed off its been deployed to Live.

Sprint 2
Pkg 4 & 5 been developed. During this time users raised a bug on Pkg2. We want to make the change to Pkg2 and deploy that to UAT and eventually to LIVE without releasing Pkg 4 &5.
How do we do it?

Matt pointed me to his blog entry which I have seen before . http://www.mattmasson.com/index.php/2012/02/thoughts-on-branching-strategies-for-ssis-projects/

Thanks
Sutha

My response:

Personally, even though I've experienced the exact problem you just outlined, I agree with the current approach. I steadfastly believe that there should not be a way for an unscrupulous developer to slide in a new version of a package under the covers. Deploying .ispac files brings a degree of rigour to your operational processes.

Yes, that means that we as SSIS developers are going to have to get better at using source control and branching properly but that is no bad thing in my opinion. Claiming to be proper "developers" is a bit of a cheap claim if we don't even do the fundamentals correctly.

I would be interested in the thoughts of others who have used the project deployment model. Do you agree with my point of view?

@Jamiet

SSIS Catalog, Windows updates and deployment failures due to System.Core mismatch

jamiet — Wed, 11 Jul 2012 14:36:23 GMT

This is a heads-up for anyone doing development on SSIS.

On my current project where we are implementing a SQL Server Integration Services (SSIS) 2012 solution we recently encountered a situation where we were unable to deploy any of our projects even though we had successfully deployed in the past. Any attempt to use the deployment wizard resulted in this error dialog:

The text of the error (for all you search engine crawlers out there) was:

A .NET Framework error occurred during execution of user-defined routine or aggregate "create_key_information":
System.IO.FileLoadException: Could not load file or assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) ---> System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
System.IO.FileLoadException:
System.IO.FileLoadException:
at Microsoft.SqlServer.IntegrationServices.Server.Security.CryptoGraphy.CreateSymmetricKey(String algorithm)
at Microsoft.SqlServer.IntegrationServices.Server.Security.CryptoGraphy.CreateKeyInformation(SqlString algorithmName, SqlBytes& key, SqlBytes& IV)
. (Microsoft SQL Server, Error: 6522)

After some investigation and a bit of back and forth with some very helpful members of the SSIS product team (hey Matt, Wee Hyong) it transpired that this was due to a .Net Framework fix that had been delivered via Windows Update. I took a look at the server update history and indeed there have been some recently applied .Net Framework updates:

This fix had (in the words of Matt Masson) “somehow caused a mismatch on System.Core for SQLCLR” and, as you may know, SQLCLR is used heavily within the SSIS Catalog. The fix was pretty simple – restart SQL Server. This causes the assemblies to be upgraded automatically. If you are using Data Quality Services (DQS) you may have experienced similar problems which are documented at Upgrade SQLCLR Assemblies After .NET Framework Update. I am hoping the SSIS team will follow-up with a more thorough explanation on their blog soon.

You DBAs out there may be questioning why Windows Update is set to automatically apply updates on our production servers. We’re checking that out with our hosting provider right now

You have been warned!

@Jamiet