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) :
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...