A practical use of a numbers table.

Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?

The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.

Hauppauge Wireless Media MVP - Watch PC based media on your TV set!

Continue reading “A practical use of a numbers table.”

SSIS – Writing SQL results to a string variable

In an earlier post, I demonstrated how to send HTML formatted mail using the script task. Another frequently requested use of the mail task is to be able to send query results within the email message body. Here’s how…

Introduction to Internet Marketing; Search Engine Optimization, Adword Marketing, Email Promotion, and Affiliate Programs

Continue reading “SSIS – Writing SQL results to a string variable”

Dynamic table loading in SSIS (Part 2)

I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).

SQL Server Developer Edition 2005 Win32/X64/IA64 English CD/DVD

Continue reading “Dynamic table loading in SSIS (Part 2)”

OUTPUT clause – the SQL developers panacea.

A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.

It is easy enough to capture the last inserted identity if we are only dealing one record at a time using @@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:

  • Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
  • You’d have to select the same data twice, once to insert the audit records and once to update the data
  • You’d rely on a trigger to write to the audit table

Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious. 

Amazon Link: Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes

Continue reading “OUTPUT clause – the SQL developers panacea.”

SSIS – download a file from a website

Some of the companies I’ve worked for have had reporting contracts with smaller 3rd party providers. Campaign or website analysis provided by them would have to be loaded to the datawarehouse

Security policy dissallowed downloading the files over FTP, because credentials are passed in clear text, and our 3rd party report providers didn’t have a sFTP facility, so in this scenario what other options are available?

Microsoft Office 2007 SharePoint Designer Edition (PC)

Continue reading “SSIS – download a file from a website”

SSIS system variable – ExecutionInstanceGUID

ExecutionInstanceGUID is a system variable of type string. If you are exporting this variable to a package management, custom logging or auditing table ensure that you have the correct mapping in your execute SQL task. It is a string variable and not a GUID (the same applies to other variables whose type may not be the same as the value they represent).

SQL Server 2005 Reporting Services Custom Controls

Continue reading “SSIS system variable – ExecutionInstanceGUID”

SSIS Parent Package Configuration (Gotcha!)

The order of the configurations used in the development studio is not the same as the order used by DTEXEC, specifically in relation to parent package variables.

Parent Package variables always have the lowest priority no matter what order you set in the configuration manager.

Microsoft have recently added a note to BOL on this subject here

Amazon Link: Microsoft SQL Server 2005 Integration Services

Continue reading “SSIS Parent Package Configuration (Gotcha!)”

Bulk Import of DTS 2000 packages to SQL2005

I recently saw some forum posts on SSC.com asking if there was a way to bulk import DTS 2000 packages into a SQL 2005 database.

The answer is that you can use the DTSBackup utility available for download at www.sqldts.com. Its a lightweight and extremely useful tool that can save hours of mouseclicking, and it works on both SQL 2000 and 2005.

Amazon Link: SQL Server 2000 Developer Edition

OLE DB Command for Jet 4.0 in SSIS

Normally in SSIS when we want to issue a SQL command against each row in a dataflow, we use the OLE DB Command component. When executed against SQL Server the command normally takes the form:-

UPDATE dbo.TableName SET ColumnName1 = ? WHERE ColumnName2 = ?

Where “?” can be mapped to the incoming columns in the order they are written.

However you will find that when you are using this component against an Access database, that this syntax will not work. You will get an error saying:

There is more than one data source column with the name “?”. The data source column names must be unique.

Amazon Link: Microsoft® Office Access™ 2007 Inside Out (Inside Out (Microsoft))

Continue reading “OLE DB Command for Jet 4.0 in SSIS”

Dynamic table loading in SSIS (Part 1)

The quick answer to this is that you can’t… at least not in a dataflow. Whilst you can dynamically change environments and databases, the column metadata has to remain the same. It’s therefore fairly limited as to what you can load “on the fly”.

There is a work around however for those of you who wish to be able to create a looping mechanism in a single package with a single task to load the data.

Amazon Link: SQL Server 2000 Fast Answers for DBAs and Developers, Signature Edition

Continue reading “Dynamic table loading in SSIS (Part 1)”