Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts

Monday, 6 June 2016

Expert Scripting and Automation for SQL Server DBAs

My new book "Expert Scripting and Automation for SQL Server DBAs" is now available to pre-order on Amazon and Apress.com




The link for Apress.com is www.apress.com/9781484219423?gtmf=s

It is worth noting that the expected release date on Amazon is not correct. Publication is actually expected in August.

Monday, 18 January 2016

SSIS Toolbox in SSDT for Visual Studio 2015

I am currently writing my latest book - Expert Scripting and Automation for DBAs, which Apress will be publishing later this year. The book is based around SQL Server 2016 and I am currently writing a chapter that is based around SSIS.

I needed to use SSDT for Visual Studio 2015, so that I could use the pre-release version of the Azure Feature Pack, for SSIS 2016. When creating a project, however, I was rather disturbed to find that I had no controls in my Toolbox!



Apparently, there are two Toolbox windows, and SSDT defaults to the wrong one. You should navigate to View | Other Windows and open the SSIS Toolbox window. Normality will at once be restored.


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Monday, 11 July 2011

SSIS - Execute Process Task Hangs When Run From Server Agent

I was faced with an interesting problem recently. I had a SSIS Package that was stored in the Package Store on an ETL Server, and run from a Server Agent job on a Clustered SQL Instance.

If the package was run in Debug mode, it worked perfectly. If it was run from Windows Scheduled Tasks on the Cluster, it also ran perfectly, but when run from Server Agent on the Cluster, it failed.
Or to be more accurate, it didn’t fail! In fact it didn’t do anything! It reached an execute process task that was calling a decompression utility, and sat there, not working, not failing, not doing anything!

Well, I tried the usual things, such as:

Adding SSIS Server to Local Intranet Zone
Turning on Allow Unsafe Files
Using "God Account" to run job
Using "God Account" to Run As on the execute process
Making the owner of the .exe the same account as the execution context of the process
Disable attachment manager

I knew the package and executable worked, so I was sure that it was a OS level setting, but what? Well, I struggled with this, with the aid of an infrastructure team and also MS Technical Support, but the answer finally came from the MSDN Forums, who suggested creating an Environment Variable called SEE_MASK_NOZONECHECKS and setting the Value to 1.

This worked perfectly, but it was an interesting situation, and wanted to share the resolution with you all.


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom

Sunday, 24 April 2011

Including Current Date In a Flat File Name - SSIS

There was a thread on MSDN Forums, which paraphrased, asked "How can I include the current Year and Month in a file name when I create a Flat File in SSIS"

Although this is a relatively straight forward task, because SSIS is so visual, it can be difficult to explain how to do things in Forum threads, so I decided to produce this short walk through video. Hope you enjoy, and find it useful...




Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Sunday, 20 March 2011

Sexy SSIS In Denali - Usability Enhancements

Following the success of my last post on SSIS in Denali see here I have been wanting to do a post for some time, giving an overview of some of the nice new usability enhancements for Developers. Unfortunately, it is predominately visual, so I have been putting it off, as really it requires a webcast, and 1) I hate the sound of my own voice! 2) I do not have any professional screen capture software. Today, however, I bit the bullet and recorded a short piece, that gives an overview of some of my favourite new features for developers. Most of them seem like small changes, but together, they make the product much easier to use.

I hope you enjoy it, but please bare the previous two caveats in mind!!! ;-)

If you enjoy this, and would like to see more webcasts, then please leave a comment to let me know. It's always good to know how to spend my blogging time!



Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Tuesday, 15 February 2011

SSIS In Denali. Sexy! But Substance?

In Denali, SSIS has been given a face lift, and it looks pretty sexy, that's a given. But for my first post on the subject, I wanted to look instead at some of the new functionality to see how it performed, and one of the biggest new features in terms of functionality, is the Integration Services Database, so...what does it give us?

Well, I started by creating a simple SSIS package in the VS 2008 Shell. (We have been promised VS 2010 shell for RTM!) I used the new Source Assistant and Destination Assistant to create a data flow that just pumps rows from one table to another.

Then, in SSMS, I created an Integration Services Catalog, by right clicking on the Integration Services folder. Two points of note here were that it made me enable CLR, and it enforced a complex password...


 ...Then I created an organisational folder, under the catalog...



...I then tried to import the package from the context menu of the folder. It did not work, but to be fair, I am using CTP1! So I went back to BIDS and used the new Deployment Wizard (I'll blog about this soon, because it is quite good, and very, very different!) and deployed the project to the SSIS database. Denali uses the concept of deploying projects, much more rigorously than previous version, as many of the new features (such as parameters) are based at the project level.

After deployment, The project and package appeared under my folder. Right clicking the package allowed me to run a validation process, shown below. I could also have done this at the project level...



...Everything seemed Ok, so I ran the package. This gave me a jazzed up "Progress Window", which furnished me with status information in real-time...



...After package execution, a right click on the Catalog, allowed me to click the operations button, which gave me a history of all the actions that I had performed so far...



...I then found a whole raft of extra information in the SSISDB Database, that was automatically created when I created the catalog. Some of the most interesting tables included Executions table, the Catalog_Property table and the Data_Type_Mapping table, all shown below...







...Finally, a quick look at the Properties of the Project, revealed the Previous Versions Window. This allows us see, and easily roll back to a previous version of the package, in the event that you had not tested your code properly! (Not that would every happen of course!)...



...So in conclusion, there will be many more Denali SSIS posts to come, but I'm sure you can tell from this post alone, that DBAs are going to love it!!!