Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2008-10-21 : 16:26:16
|
| Hi,I'm trying to create a dts package that will transfer data from an access database to a sql server database. Unfortunately there are a number of different versions of the access table structure. For example all of the access database table will have the "static" columns and may have any number of the dynamic columns.staticColumn1staticColumn2dynamicColumn3dynamicColumn4dynamicColumn5The sql server datbase will have all the columns. For any access database that doesn't have a dynamic column, I would like to load a null value. Unfortuantely I'm not seeing how to do this in DTS. I could create a seperate DTS package for each access database but since they all follow a same general format it would be nice to only have one DTS package. When I currently load the access database into sql, if a column is missing the values get shifted over so the mapping is incorrect. Anyway, I'm just looking for a little guidence on how you would do this. Do you set up the table structure in sql first and then migrate the data? If so how do you handle the missing columns?Any help would be appreciated.Thanks,NicNic |
|
|
RitaBowman
Starting Member
10 Posts |
Posted - 2008-10-22 : 10:17:18
|
I would create the table first and then in the DTS package, import the data using multiple Transform Data tasks. Only transfer the fields that are appropriate from each access database, and at the end use an Execute SQL task to change the field values to NULL if necessary. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-22 : 11:52:32
|
| i also think you have to use seperate transform data task for each as its not possible to change your metadata at runtime in dts. |
|
|
|
|
|
|
create one dts package from multiple access tables