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 |
|
humanpuck
Yak Posting Veteran
94 Posts |
 Posted - 2006-07-14 : 10:58:31
|
| Hi folks,I'm working on a DTS package. I have a txt file that I'm importing. I'm on the Transformation properties. I have a date that comes in 130606(DDMMYY). I tried using Date Time string type and I guess your source can be a date and destination can be string, but the other way around doesn't work. So now I'm trying the activex script and this is the code I'm trying. Any suggestions on how to get this to work. I have a workaround for now that imports the string and then I have a stored procedure that does the conversion in the database, but I'd rather be able to eliminate that step.' Copy each source column to the destination columnFunction Main() DTSDestination("Call_Date") = DTSSource(CDate(Mid( "Col001" ,3 , 2) & "/" & Mid( "Col001" ,1 , 2) & "/" & Mid( "Col001" ,5 , 2))) Main = DTSTransformStat_OKEnd Function |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-14 : 11:25:05
|
| Can you import into a staging table then transform in an sp.I would probably do it all in an sp using bulk insert into a temp table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
humanpuck
Yak Posting Veteran
94 Posts |
Posted - 2006-07-14 : 11:26:18
|
quote:
Originally posted by nr
Can you import into a staging table then transform in an sp.I would probably do it all in an sp using bulk insert into a temp table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thats how I'm handling it now. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-14 : 11:42:21
|
| ????Make the column a varchar in the staging table then you can use convert to convert it to a datetime when it's moved to the production table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
humanpuck
Yak Posting Veteran
94 Posts |
Posted - 2006-07-14 : 11:48:36
|
| Yeah currently I'm doing a DTS dump into dummy columns as string values. Then I have SP that goes out and formats the data into usable data. update boston_Feed set call_Date = cast((substring(datestring,3,2)+ '/' + substring(datestring,1,2) + '/'+substring(datestring,5,2)+' '+SUBSTRING(TIMESTRING,1,2)+':'+SUBSTRING(TIMESTRING,3,2)) as datetime) from boston_feed a where len(datestring) = 6 and call_date is null |
|
|
|
|
|
|
|
|
DTS Help