Month: January 2008

PTI Learning Series 1: Post-Morten

As promised, here are the slides from my presentation. Thanks to all whom attended the event and hopefully you will join us for the next one. Feel free to contact me with further questions by using the contact tab and keep looking on http://events.perptech.com for further event information. DBA Toolkit:SSIS Presentation Cheers, AJ Powered by...

Read More

SSIS: Setting up an XML Configuration File

SSIS provides several methods to create configuration information for your ETL process. The easiest of these to implement is the XML configuration file. Additionally, it provides the easiest method to migrate and update your configuration information of the method available. I will go step by step on how you can implement this in your application. Below we have a screenshot of a simple application which reads data from a flat file and places the data into a corresponding SQL Server table. First we will set up a variable in which we will set up to hold the location of our flat file. Right click on the design surface of the Control Flow and select Variables to bring up the variables screen shown below. Click on the ‘New Variable’ button,first one on the left, to create the new variable. Simply fill out the information for the variable. For our example, we wanted a string variable to hold the location of the our flat file as shown below. Next we want to actually set our flat file connector to use this variable to determine its connection string. In your connection manager section, right click on the flat file connection and then select the […] button next to Expressions. This will bring up the Property Expressions screen below. Select ‘ConnectionString’ from the drop-down on the left and then select the […] button...

Read More

SQL Server 2008 Sneak Peak

Looks like Perpetual Technologies may be having me put on yet another event! This one looks like it will entail a SQL Server 2008 Sneak Peak. So keep tuned to my blog and also check out events.perptech.com for registration details here in the near future but for now it looks like the date will be sometime in early May. Cheers,AJ Powered by...

Read More

Recursive Queries using Common Table Expressions (CTE)

SQL Server 2005 uses a new T-SQL Entity called a  Common Table Expression(CTE), which can be thought of a resultant dataset that can be used within a query(SELECT,INSERT,UPDATE,DELETE or even a View). In many respects a CTE is similar to a derived table variable because it lingers around only for the duration of the statement. What is often unused in these instances,is the ability of the CTE to self-reference and therefore provide a way we can use it to perform recursive queries. In order to do this you would structure the CTE like this… WITH [CTE_Name](<Column names>)AS(--Put the anchor statement here--This would give you the root of the recursive resultsetUNION ALL--Put the recursive part of the query here--This will handle the recursions and should reference [CTE_Name]) If we take for an example a table of Employees with the following structure EmployeeIDManagerIDFirstName.LastName.JobTitle,Phone In which the ManagerID references back to the EmployeeID of the manager whom the Employee reports to. Now, if we needed to create a list of all of the Employees whom reported to a particular manager and to make that list recursive …we could use the following in order to get the full recursive list. DECLARE @EmployeeTable TABLE ( -- Add the column definitions for the TABLE variable here FirstName nvarchar(50) ,LastName nvarchar(50) ,JobTitle nvarchar(50) ,Phone nvarchar(25) ,ManagerID int ,EmployeeID int) -- INSERT the initial level of employeesINSERT...

Read More

New SQLCLRPROJECT Released!

For everyone that may have attended yesterday’s presentation at CincySQL..I mentioned that I liked using the SQLCLRProject for creating and deploying my user-defined functions. Well. sorry for missing this one but it appears that on the 14th Neil has released a newer version of the project in which he addressed some ‘undocumented features’. So head on over to his blog and read about it….I promise you won’t be disappointed. Neils SQL Server Blog...

Read More
  • 1
  • 2