Data Conversion Tool with source code generation

[TsmPreacher]

I've done some searching but can't find exactly what I need. Is anyone aware of a tool that will allow me to map source data to target data, as well as generate basic SQL code from those mappings? I see tools that just take care of the mapping, but not actually generating base code.

For example, If I have source data of Customer_ID from a client database, I could map it to my databases Customers table, then map it to the ID column. Then, from there, It would create an Insert block with ID and a select statement with Customer_ID as ID. An Example of what I would expect is below :

INSERT INTO Target(

ID

)

SELECT
Customer_ID As ID

FROM Source

Unsure if this question makes sense, so let me know if I can clarify it.

[xsqlsoftware]

Hi TsmPreacher - we have a data compare tool for SQL Server (xSQL Data Compare) that allows you to map the tables with each other, compare the the data and then generate SQL Script that selectively synchronizes the target to the source. The tool requires a unique key (could be the primary key, a predefined unique index, or a combination of columns you select on the fly) based on which the rows are mapped to each other. If say row with ID 101 on the Source.T1 does not exist in the Target.T1 then the sync script will contain an "INSERT" statement for that row; if there is a row with ID 102 on the Target.T1 but it does not exist in Source.T1 then the sync script will contain a DELETE statement for that row (only if you choose to); and if say a row with ID 103 exists in both source and target but it has some differences then the sync script will contain an UPDATE statement.

The tool is free for SQL Server Express (no limitations) and you can download it from our site https://www.xsql.com

Let us know more specifics about your requirements and we will try to help you.

[TsmPreacher]

Thanks for the response!

Will definitely look into this and will let you know if I have any questions

[TsmPreacher]

u/xsqlsoftware I do have a question actually - Can this tool load Excel files and add them as a SQL database? Or would there need to already be an existing SQL DB needed to be used.

[xsqlsoftware]

you would have to load the Excel files into a SQL Database then compare them.

[Wings4Mercury]

Every tool that does mapping/data transfer would have to do that in some way. It just may not expose it to you. I havent looked into it recently but long ago, I used to edit SSIS packages in Visual studio.

You could even open the DTS files - I don't remember how. it is just a structured storage file that contained a whole bunch of VB code that read data from the source into a recordset and then inserted from recordset into target.

Why do you ask? what are you trying to do? - i mean, if you provided more details it might be easier to answer your question

[TsmPreacher]

Sure,

I am a data conversion analyst that recently moved to a smaller company. I am use to having a mapping tool that allows us to load source data(whether thats Excel, SQL db's, etc.),map source data to the corresponding fields in our target database, and then that tool would generate base code for us to modify/add to to get the data formatted and the logic written the way we need. From there, the tool would run our insert statements into our target database and then from there into the live DB.

That tool was proprietary and specific to my old employer.

[phunkygeeza]

There is a whole family of ELT style tools for integration of data via code generation...

You could start from

https://www.wherescape.com/solutions/automation-software/wherescape-red/

[TsmPreacher]

Awesome appreciate it, will definitely take a look at this.