Relational table copy with transformation in most generic way ( declarative rules and etc... ) - Microsoft sandpit

Question

I guess, it is quite common task. There is a table A let's say in Visual Foxpro and needs to be transformed to table B in SQL Server 2008. Operation is going to be repetitive. Column names may be changed , some input columns dropped , column types may be changed ( string <=> int ), some output columns generated ( GUID , identity ) . Also normalization may happen where input table results in 2 joined with pivot table - let's drop this case for simplicity

I know about SSIS existence , probably there are scripts which can do what I described ?

I've already written C# program which reads XML ( snippet below ) and does mapping and out key generation but just thought whether all that - another wheel reinvention.

  <Tables>
    <Table inTbl="A" outTbl="B">
      <Keys>
        <Key name="it_pk" type="GUID">
      </Keys>
      <Mappings>
        <Column inCol="hxkey" outCol="it_tariff"  />
        <Column inCol="txt" outCol="it_description" />
        <Column inCol="uq1" outCol="it_uq1" />
        <Column inCol="date1" outCol="it_startdate" />
        <Column inCol="date2" outCol="it_stopdate" />
      </Mappings>
    </Table>
  </Tables>

Answer 1

This is pretty much exactly what SSIS is designed for, however it's not as "generic" as your current system, but it's very easy to set up what you've described.

You have a control flow and a data flow. Your control flow can contain many data flows (e.g. sync table A, then B, then C) and other tasks (e.g. move files, iterate, custom scripts).

In your data flow you define your source, destination and transformations. Your source can be pretty much anything. Then, you can do derived columns and change data type tranformations to do what you've described. Then your destination takes the transformed data (e.g. SQL table, SQL insert statement, SQL update statement)

This is done using Visual Studio, and I reckon you could knock out a simple package to sync two tables in 5 minutes. Then, you can schedule it using SQL agent.

This is called ETL btw (extract, transform, load) in case you wanted to evaluate other options, but I think SSIS would fit nicely.

Answer 2

In addition to the excellent answer above, SSIS is a good platform for implementing programmatic solutions in this space even if the Visual Studio package designer tools aren't appropriate.

Actually the platform is more flexible and better finished than the Visual Studio tools built on top of it.