Wednesday, May 13, 2009

SQL2k5 tip of the day: Recheck your data mappings!

Basically been trying to perform magic with SQL2k5 these 2 days. Not really magic but I'm trying to jump even before I know how to crawl...

Task: Retrieve data from a table or a few tables, split them by a certain criteria and place the results into separate Excel files.

Description: For example there is a DB table with the names of students and their nationality (see below), I want to generate Excel files with the student details based on their nationality(criteria) :

A-san, Japan
B-san, Japan
Mr Dumb, Singapore
Mr Stupid, Singapore
Mr Dumber, Hong Kong
Mr Dumbest, Moon

At the end of the day, the SISS package should be able to generate 4 xls files (Japan, Singapore, Hong Kong and Moon) with the details of the relevant students in each file.

Plan: Execute an SQL Task to retrieve unique nationalities, using a foreach ado loop to create xls file and run query to retrieve/insert data into said xls file.

Execution: The planning was easy, execution was hell. Learning how to pass the variables from the first SQL task to the foreach loop took only a short while. Using expressions on the connections, I was able to dynamically create and point to the correct xls file.

Surf the net and from dear MS came this article. That was easy. Just insert a data conversion between source and destination. Tried this whole morning and I kept getting the same error.

THEN I rechecked my destination and realised that I didn't remap the results to the output from the data conversion. "So that's the problem..." *Slap myself 20 times*

The computer did what it was supposed to do. Humans just don't get it... or at least its just me...

No comments:

Worldwide Visitors