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 |
|
FRANKMANIC
Starting Member
10 Posts |
 Posted - 2008-06-18 : 13:39:19
|
| Hi all, new to SSIS so please bear with me on the noobie question:Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor. Need to copy the data to a new database and tablesThen for each table: 1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order. What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rowsAny Ideas would be appreciated! Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-18 : 13:45:31
|
| The data transfer can be done by data flow task and inside thet you need a oracle source connection to get source data, then a transform task to check date and convert it to sql date and finally an OLEDB destination to dump data to SQL server. |
 |
|
|
FRANKMANIC
Starting Member
10 Posts |
Posted - 2008-06-18 : 13:51:41
|
| Hi, thanks - the Oracle database is a proprietary and do not have access to it - that is why we are using the weekly SQL server version they create. But if i understand your answer i can still use the data transfer task just connecting to the SQL server database instead. Still need to know how to check and transfrom multpile columns in a record - is this done in the data transfer? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-18 : 14:20:04
|
quote:
Originally posted by FRANKMANIC
Hi, thanks - the Oracle database is a proprietary and do not have access to it - that is why we are using the weekly SQL server version they create. But if i understand your answer i can still use the data transfer task just connecting to the SQL server database instead. Still need to know how to check and transfrom multpile columns in a record - is this done in the data transfer?
Nope its a seperate task available under Data Flow Task. Its called Derived Column task. |
|
|
|
FRANKMANIC
Starting Member
10 Posts |
Posted - 2008-06-18 : 14:44:08
|
| Hi, i understand how to use the connections, derived column task, and data conversion task - just need to know how to set it up so I can check each column as stated above then perform the apporiated transformation - look like i need some type of conditional logic as i check each field value - do I have to use a conditional split for each field? or is there a easier cealner way to do it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-19 : 02:50:04
|
quote:
Originally posted by FRANKMANIC
Hi, i understand how to use the connections, derived column task, and data conversion task - just need to know how to set it up so I can check each column as stated above then perform the apporiated transformation - look like i need some type of conditional logic as i check each field value - do I have to use a conditional split for each field? or is there a easier cealner way to do it?
You can use conditional expressions inside the expression builder window of derived column task itself. |
|
|
|
|
|
|
|
|
TRANSFORM MUTLIPLE COLUMNS