| Author |
Topic |
|
rushdib
Yak Posting Veteran
93 Posts |
 Posted - 2003-09-17 : 17:25:50
|
| Hi,How do I modify or vaildate the data while importing from DTS?For an example: I wanted to converted dates from one format to another using a SQL Server function. How do I call the function in a DTS query?Thank you,Rushdi |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-18 : 12:30:47
|
| How are you importing the data? Please explain what the DTS package is doing. You could create an additional object, the Execute SQL task object to run the function.Tara |
 |
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2003-09-18 : 12:46:57
|
| Hi Tara,We have a file in Unix format. I am using an ODBC driver called ProvideX which can connect to that file. I am using the DSN in the DTS wizard to connect the file, get the info using a query (because I have to filter certain info), map the fileds to the SQL table and moving the data. Now at this moving process, I have to validate certain fields and convert them to values accepted by the SQL system.How do I create an additional object to call a SQL Server function or some code?Rushdi |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-18 : 13:20:33
|
| Can't you just call the function inside your query? To create an object in DTS, just click on the object that is on the left pane, then create a workflow between them.Tara |
|
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2003-09-18 : 15:48:47
|
| Hi Tara,Ok, I was able to figure out how to convert data while it's being moved from the source table to the destination table.Now, I have another problem. I have to join a table from a another SQL database with the source (ODBC) and populate a column in destination table. If you don't mind, can you help me?Thanks,Rushdi |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-18 : 15:58:02
|
| Then just add the join in the query:SELECT a.Column1, b.Column1FROM Table1 a INNER JOIN Table2 b ON a.ColumnA = b.ColumnAWHERE a.Column2 IS NOT NULLTara |
|
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2003-09-18 : 16:58:08
|
| Hi,I have the 3 connections on the designer (1 ODBC and 2 SQL). I am trying to create SQL Task and I have a problem refering to the ODBC table in the query builder. It says the table not found. I wanted to do an insert.Thanks,Rushdi |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-18 : 16:59:56
|
| I have never done a join using an ODBC data source. I'm not sure that it is even possible. Can you create a linked server in SQL Server pointing to your ODBC data source? This would be configured outside of DTS.Tara |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-09-19 : 00:17:43
|
| rushdib,Normally for large source system files comming into the datawarehouse environment I use a stagging database where I have direct 1 to 1 mapping of all fields and datatypes into non-normalized table structures.Next I perform all the data manipulations and cleanse all the data with T-SQL and place this data in another database which mirrors the final destination.Finally I move the data from the mirror to production.Depending on the size of the system you are trying to create you could always scale down to using tables in one database.However I would still recommend bringing the data into sql raw and then using T-SQL to tranform. |
|
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2003-09-19 : 09:01:51
|
| Ya, I that's what I have to do. I thought the performance wise it's faster not having intermediate tables. Maybe I am wrong. I tried th e linked servers, but it's gives an error when I try to use the four part connection string to the tables.Thanks,Rushdi |
|
|
|
|
modifying data while importing from DTS