DTS Package--splitting into different excel files

[vaichi123]

I am creating a DTS package in which I need to create as many excel files using DTS as there are business units in my SQL table. This table has other columns for emplid, emplname and managername. I need to figure out how business units can be used to separate data into different tables and then use DTS to create excel files based on the different business units. I need to be able to loop through the entire table and then move to next table when one Business unit is done without hardcoding the name of the business unit.

 

[triggerhappy]

Hey giving it a shot...since i didn't see anyone reply...not sure if you are still waiting...

------------------------------

I believe you have a table that looks like:

- BusinessUnit, EmpId, EmplName, ManagerName

and would like to create multiple files (excel, csv...etc) for each BusinessUnit. Right?

Here is what i would do -

I would create "global variables" for the following that would need to dynamically change throughout the process (assuming output location is same).

- @maxID, @prevID and @currID for ID field on the businessunit (int column to make loop easier)
- @BusinessUnit for BusinessUnit
- @outputfilename for OutputFile based of BusinessUnit

---------------
Step1: Select Distinct BusinessUnit into a Temp Table (say T1), with an ID field (Identity int column...so values ...1,2,3,4...this helps you loop the BusinessUnits easily)

Step1: Set var @prevID = 0
Step1: Get Min(ID) from T1 where ID > ? (set Input Parameter ? to @prevID ...set Output Parameter to @currID)
Step2: Get Max(ID) from T1 (output parameter @maxID

This step gets the maxID...basically count of BusinessUnits since its an Identity column..so if you have 5 distinct BU it will be 5

Step3: Activex Script that checks if @currID < @maxID

This step controls the loop...Based upon the above condn being true or false...you can enable/disable the next step...through right-click on task > "Workflow Properties" > "Disable This Step" checkbox

Step5: Get BusinessUnit where ID = ? (set Output parameter to @BusinessUnit, Input ? to @currID

This gets the BU for the current ID.

Step6: Put a VBScript or Dynamic Properties Task that updates the Source Query and Destination Filename for each Business Unit (@BusinessUnit)

Using VBScript...the source query could be

Select @Sql = "Select " & DTSGlobalVariables("@BusinessUnit").Value & ", emplid, emplname, emplmanager from BusinessTable where
BusinessUnit = '" & DTSGlobalVariables("@BusinessUnit").Value & "'"

For destinationfile...just create a variable
@destfile = "ExportFile" & DTSGlobalVariables("@BusinessUnit").Value

Ofcourse you have to create a variable reference to the DataPump Task to be able to update the source and dest...

try a google search with the following:
"vbscript datapump dtsglobalvariables

Step7: Would be you actual Transformation

From Sql table to destination (Excel, csv...etc)

Step8: Set @prevID = @currID

You can do this using Activx Script or Dynamic Properties Task...the latter is easier

Step8: Just put an ActiveX Script task that transfers control to Step 3.

Step9: Just put a task that is executed when Step 3 condition fails...some kind of notification that says...
"Package Execution Completed"

---------

Disclaimer: Just trying to paint an overview of the process...if you are looking for working code...i am sorry this is not it...and as much i like...i dont believe that one could learn by having code ...one needs to write themm...hehehe just kidding dude...

Further...there could be better more simpler/easier ways of doing this...but thats how i know how to do it...and thats what i share...as they say -> "x number of things can be done in y number of ways" ;-)

You could also add frills like staging and error logs to make maintenance, tracking easier...just in case you are going to be responsible for this task for a longer time...

However...to sum up...from all i know i would suggest some read ups on VBScript...and DTS Object Model...

------------------

Thanks!

TriggerHappy