INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

NEWBIE LOOKING FOR BEST SSIS FLOW TO ACHIEVE SIMPLE GOAL

NEWBIE LOOKING FOR BEST SSIS FLOW TO ACHIEVE SIMPLE GOAL

(OP)
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

RE: NEWBIE LOOKING FOR BEST SSIS FLOW TO ACHIEVE SIMPLE GOAL

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: How to get the best answers before you ask a question

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close