How to do Data Transformations - DTS to SSIS

[katbear]

Hello,

I have a bunch of migrated DTS packages in SSIS. Currently, one of the migrated packages contains a bunch of EXECUTE DTS 2000 TASK.

The EXECUTE DTS 2000 TASK's are very similar in that they are all getting input from a flat file, then transforming the columns before inserting into the database.

However, there are several transformations per file, per column, and are fairly complex. For example, check the value of columns 1 through 10. If the value is a "xyz", do this, else do that. Next, check the value of columns 3 through 7, if value is numeric, do this, else do that.

So, my question is... how am I to "translate" these transformations? Do I need to use scripting to do this? And how do I handle *multiple* transformations? That is, what will my package actually LOOK like if I have something like:

Flat File Souce - tranform1, transform 2, transform 3 -> SQL Server Destination.

I guess my primary question is this, what is the SSIS replacement for the DTS "Transform Data Task"? The Transform Data Task has a tab specifically for transformations, where you define your script.

Will I now have just 3 script tasks in SSIS in a Data Flow task? (using the example above) Or something else?

Thanks much!!

 

[paulkeys]

For complex transforms, you should be able to just use a single script task, which will be able to read and transform any or all input columns.

Within this script you may have multiple functions to do the different bits of processing, for example

Main routine to tell the transform what to do for each field...

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

row1 = function1(row1)
row2_out = function2(row2)
row3_out = CStr(row7)
row4_out = Some_DTS_Variable

End Sub

Function function1(byval x as string)
..do some processing and return a value...
End Function

Function function2(byval x as string)
..do some processing and return a value...
End Function

All the above can reside in the same script transform

Hope this helps

 

[MDXer]

Within the Dataflow your SSIS tool box has numerous transformation objects.each designed to do a specific function. By stringing these together you accomplish the complete transformation. THis isn't to say that all needed transformations exist already, that is why the Script task and the ability to create custom transforms exist. THe fact that you may use 6 transforms doesn't mean the process is going to be slow if you have used the proper objects. I have numerous packages with upwards of 20 transforms that can move volumes of data in a relatively small amount of time. The key is in selecting the proper task, as I learned in very early development. Replacing some OleDB data sources and Merge joins with some lookup tasks had almost an 75% performance gain for me. But these are the things you need know to build effective and fast processes.

Not trying to be rude or anything but what you really need to do is get books and stuff and read up on SSIS and how to use the various transform objects. SSIS has a learning curve and a person can't realistically believe they will learn how to really use it just by building packages. While hands on is a great way to learn you also need to know the fundementals which you can only do through reading.

If you invest some solid time in learning SSIS then I believe you will be much more productive.

Good Sources to Start with:
Professional SQL Server 2005 Integration Services
Hands-On SQL Server 2005 Integration Services
Microsoft SQL Server 2005 Integration Services

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[katbear]

Thanks for the info. Yes, I have Kirk Haselden's book. While it is good, it doesn't come close to covering everything I need. The rest has come by trial and error, and people who are willing to share their valuable knowledge.