NEWBIE LOOKING FOR BEST SSIS FLOW TO ACHIEVE SIMPLE GOAL

[daveddvf]

Hello,

I am new to SSIS (using 2008 R2). I have what I hope is a very simple flow. I am looking for a recommendation on a general data flow process I should use to achieve the goal that I am about to state (.specifically which SSIS objects to use and in which order, etc.). Then, I hope to use that as my template to build from, where I will then go through the recommended flow objects one at a time until I achieve the desired result (no only will I achieve my 1st request, but also will learn a lot along the way).
These are the general process steps that need to happen in my flow:

1. DATA SOURCE -Oracle (TABLE1)with fields:
ID (int)
Result_Name (str)
Result_Value (str)

2. Extract Specific values from Result_VALUE (based on specific ID numbers), and place this data subset in its' own column named, NEW_RESULT

3. Remove the % symbol from the data within the NEW_RESULT column that was just created

4. Convert the NEW_RESULT column from a String to number (whole numbers up to 100)

5. Finally, everything ends up in the new DESTINATION -SQL location (Table2) with the original fields + my new field
ID (int)
Result_Name (str)
Result_Value (str)
NEW_RESULT (int)

Thank you for suggesting a basic template/flow (SSIS objects and flow order) for me to use. I know there is probably more than one way to do this, but would appreciate a suggestion to start with for a newbie. I’ll not only

Dave

 

[xlbo]

Data flow task container

OLEDB Data source set to query type to extract all data from table1

Derived Column transformation component to create a new column that removes the % and converts to int

OLEDB destination to insert all rows to Table2

Depending on your process, you mat also want to inlcude a LOOKUP component to differentiate from values already in your table vs new entries. From there you insert new entries as is

For the existing entries you can either insert to a staging table and then use a SQL task to peform a bul update (quicker for large data sets) or use the OLEDB command component to run an update query 1 row at a time

Alternatively you can delete everything in Table 2 prior to the start of your process in which case you simply need to insert intop table2 with no lookup required

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question