I received an error message today which I haven’t came across before.
This Version of Microsoft SQL Server Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers. (ConnectionDlg)
I was quite surprised at this due to the number of times I have connected to SQL Server 2008 from 2005 in the past, typically midway through a migration project. Googling didn’t prove much help either as many formus dated prior to the availability of any fix states “backward compatibility is not supported in SQL Server Management Studio”.
So the solution? Well, my client was running on version 9.00.3152 which is the SP2 Cumulative Hotfix (SQLTeam have a decent reference on SQL Server Version Numbers) so I had a suspicion support for connecting to SQL Server 2008 from the 2005 version of management studio must have came in following a patch.
Although I couldn’t find the exact error I managed to track this down Fix: 946127 You may experience problems when you use SQL Server Management Studio in SQL Server 2005 to connect to an instance of SQL Server 2008. Which was first part of the SQL Server 2005 Cumulative Update 5 for SP2.
To be on the safe side I patched to SP3 and Hey Presto, it worked!
When working with datetime variables in SSIS a word of warning. If you use the DATEPART function in an expression as below leading zeros will be removed from your month and days.
For example the if the User::CutOffDate variable = 01/01/2010 then the following expression would return 201011. Remember that the DATEPART function returns an INTEGER so you must cater for this.
“DELETE
FROM MyTable
WHERE DATE >= ‘” + (DT_STR, 4, 1252) DATEPART(”yyyy”, @[User::CutOffDate]) +
(DT_STR, 2, 1252) DATEPART(”mm”, @[User::CutOffDate]) +
(DT_STR, 2, 1252) DATEPART(”dd”, @[User::CutOffDate]) + “‘”
This can be easily missed if you are testing using a date such as 31/12/2009 for example. Once you’ve identified it as an issue the solution is fairly straight forward;
“DELETE
FROM MyTable
WHERE DATE >= ‘” + (DT_STR, 4, 1252) DATEPART(”yyyy”, @[User::CutOffDate]) +
RIGHT(”0″ + (DT_STR, 2, 1252) DATEPART(”mm”, @[User::CutOffDate]),2) +
RIGHT(”0″ + (DT_STR, 2, 1252) DATEPART(”dd”, @[User::CutOffDate]),2) + “‘”
We had a last minute panic at the last user group…
Satya, one of our guest speakers for the evening got stranded in Washington DC on Tuesday! Fortunatly Alex Whittles of Purple Frog Solutions stepped up to the mark and saved the day.
We had a good turn out and a great venue at Eversheds in the city center. Thanks for making the effort and coming along, judging by the feedback a good time was had by all!
Please keep an eye on www.sqlserverfaq.com for the next date which we’re thinking about running in May.
Had a bit of an interesting discussion today over the pros and cons of where best to store SSIS packages. So where is best to store them? The file system or in MSDB?
Personally I prefer the MSDB database as I find it a more controlled way or administring packages. For example you can only store 1 package of the same name on the MSDB database whereas the file system could have the same package in multiple locations scatter across the hard disk - could leave you open to more risk if your dynamic connections to packages aren’t verified. In addition if you wanted to run a package which in turn calls several child packages in BIDS although it can be nice to see them all appear during execution I have had performance issues on my machine when many packages open in turn. I have also had another more prominent issue when executing packages this way when I have received the error message “File is in use by another process”. Executing the package through DTExec gets round this problem.
The only drawback I can think of when using the MSDB database is ensuring a SSIS package is deployed to the after making a change and before carrying out an end-to-end test.
I would be interested to know what method people use and what pros and cons they find using the differing methods.
Agenda
18:30 - 18:45 - Introduction, Networking and Food
18:45 - 19:00 - Round Table problems and answers, SQL nuggets - bring a tip or 1 - 5 minute demo and share with your peers.
19:00 -Â 19:45 - Ensuring Business Continuity - Tony Rogerson, SQL Server MVP
Business Continuity is essential (period); in this session we talk about creating your Backup and Recovery strategy, we dive into Database Mirroring and Log Shipping and possibly Clustering if time; we also look at monitoring by diving into the SQL Server 2008 Event Notifications features of the product to assist you in building a solid and reliable method of being notified when something is going wrong.
19:45 - 20:00 - Break
20:00 - 21:00 - Integration Services in SQL Server 2008 - Allan Mitchell, SQL Server MVP
In SQL Server 2005 Microsoft introduced their new ETL tool (SSIS). It is a complete rewrite of the beloved DTS (Data Transformation Services). It is incredibly quick and incredibly flexible. In SQL Server 2008 they have made it even better. In this session I want to show some of the things that make it better. Most of them are engine enhancements but I will show you why they are good news. Things that will be included
Persistent Lookups
Pipeline Limiter
Threading
C# Scripting Language
Change Data Capture
A fun fast paced session that will show Microsoft is committed to making ETL a priority product in their BI suite of tools.
I promised I would write again about the Automated Deployment piece so here is the long overdue 2nd part. Thankfully we’re over the hard part - deploying the database objects is far more complex then simply deploying SSIS packages!
Ok so again we have a batch file which takes to parameters; the 1st being a top level directory of where your packages sit within your local working folder from source control and the 2nd the top level directory of your file system or of your MSDB database. Personally in my post deployment script of my DB objects I create sub folders within the MSDB SSIS package store to easily locate packages once they’re deployed on the server.
Ok so with what does the batch file do? Well it enumerates thorough each directory - which must be hard coded into the file but it’s flexible enough as it’s relative to the 1st parameter you pass in - searches for each file of a dtsx type and then passes that onto DTUTIL to move the file to the relevant target - easy. DTUTIL also has a /validate switch with can be used to validate your packages on deployment and flag any errors up front. This way you can address these before running your ETL!
Great news! I’ve been able to secure a venue for the very first SQL Server User Group in South Wales. The first event will be held on Thursday 8th October at Principality House, situated just off Queen Street in Cardiff city centre. We’re delighted to welcome both Tony Rogerson and Allan Mitchell as guest speakers on the night and from initial feed back we feel the 30 odd places we have available are going to fill up pretty quickly.
To register and for the latest agenda information Tony should be adding the details up on www.sqlserverfaq.com very soon.
Hope to see you there!
At the moment I am working on creating a 1-click deployment solution for a client. Shortly after getting into this company it was obvious that source control was not being used effectively. This was one of the first things I addressed by pulling all objects into SourceSafe 2005 to create a baseline and training the users on the VS 2005 intergration with source control.
In order to prove that I had captured all the components required I decided to build to a clean environment. So in order to deploy this new build and to support future incremental deployments I split this into 4 distinct areas of work;
- Database Deployment
- SSIS Deployment
- SSAS Deployment
- SSRS Deployment
In this first part of talking you through my automated deployment methods I am going to start with the database deployment. This is potentially where most of the development effort will be focused. In the past I have used Team Foundation Server for Database Professionals 2005/8 for deployments. TFS, DB Pro or Datadude as it was known utilises the SQLCMD area of SQL Server. Instead of going into detail about SQLCMD here I’ll refer you to this article by David Poole which explains the fundamentals very well.
Here I haven’t got access to DB PRo but I do have access to the excellent RedGate SQL Compare 8.0 which has a command line interface. This is a high level overview of the steps I perform in order to build the database and all the objects to the baseline in SourceSafe.
- Call a Master.bat script which contains all environment variables, these contain various file locations and connection strings - I’ll expand on these in the next steps. (Ideally a C# windows form will feed all these variables from user input).
- A SQLCMD script is then called which checks the existence of a target database on a target server, (both of which are variables) if it exists it is dropped and then recreated using file paths for the data and log files, again these are passed as variables from the master.bat file.
- The RedGate SQL Compare.exe is called and the SourceSafe working directory scripts folder (variable) is compared against the new blank database and syncronised. Getting latest prior to this is left to the user at the moment, I did add in code to automatically get latest from SourceSafe using the ss.exe but wasn’t convinced that it wouldn’t overwrite any uncommitted changes on the local users machine by accident.
- After control passes back to the Master.bat file another SQLCMD script is called. This time any post deploy operations are executed. This consists of populating static tables with insert scripts and adding inferred members. You may need to carry out a similar action before point 3 to handle any pre deployment operations which SQL Compare may not handle such as creating new Schemas.
- And that’s it for part 1! You database was created from scratch, all objects applied from your source control baseline and all static tables populated. You’re now ready to deploy you packages which I’ll cover in part 2.
Any queries or comments are welcome
I think so. This has been one of the few technical books I have read which I cannot praise enough. For anyone who wants to gain a good end-to-end understanding of Data Warehousing using SQL Server 2005 this book is essential. It explains all data warehousing concepts in a clear, friendly manor without alienating the reader by explaining all the terminology. I wonder if they’ll do the same for SQL Server 2008?
Yes you heard it right…in Cardiff! I’m getting involved in the UK SQL Server user group arena by organising the South Wales SQL Server User Group.
I’m planning to hold it initially on a quarterly basis with the first meeting around late September/early October. I am trying to locate a venue which is pretty central and have started to make enquiries but if anyone has any knows of any suitable locations please give me a shout.
I am sure there are plenty of people in and around the area who would be interested in coming along and sharing some SQL Nuggets. We’ll also have some guest speakers coming along and imparting some of their invaluable knowledge. But best of all it’s all FREE!
I’ll be providing more details over the next couple of months, so keeping coming back for more updates. In the meantime get registered and check out the main UK SQL Server User Group site for information on all other regional user group sites.
