So if I have a job that feeds from a source to a target then I come across the situation where there was a column missed in the source to target mapping then what is the correct way to do this without loosing data or mucking up existing tables?
What I mean is that the source table in the DB will need this new column, do I then map the missed column in the query, and just run it, will this update the table or will I have to delete the table in SQL DB and recreate it?
A few variables will affect the answer. If the table is Delete Before Load then you outlined the correct steps, to wit:
Modify DB table.
Reimport table to datastore to update metadata.
Add new column to your query transform.
Run job.
If the table is Autocorrect Load, then any record which is updated will get data into the new column.
If the table is neither Autocorrect Load or Delete Before Load then you will need to either delete the rows from the target table and rerun the job to populate the empty column or set the job to Autocorrect Load and rerun the job for all records.
What do you mean by autocorrect load? all our tables are delete before load. I’m getting confused mainly because when I first create the table, it is created by adding a target icon, then naming the table and picking the target data store for it. Then once I have ran it I will import it from Template area in Datastore, then change to delete before load.
So I’m thinking that the correct way is to update the table first with new column in SQL by drop and recreate the table with new column, which will delete the data but this should be fine as going to run job again. Then delete target table in the dataflow, refresh the target datastore, pull the updated table with new column in as the target then remap.
My thinking is that when you create the target you are pushing the table through to the SQL DB, but when updating it with new column(s) you need to sort of do it in reverse.
As I said I’m not sure if this is the completely the correct way or there is a more simple way to do it.
Next, easy way to do what you want with delete before load:
Make DB changes to table. That may or may not mean dropping the table, it does not matter if you use an ALTER or DROP, as long as the table name stays the same.
In Data Services, right click the table inside the data store and select “reimport”.
In the data flow, add the column(s) to the query before the table.
(BOB member since 2005-09-16)