| Author |
Topic |
|
devonkyle
Starting Member
19 Posts |
 Posted - 2003-07-25 : 13:15:57
|
| I'm using DTS to import records in which I need convert the incoming source string/ASCII date, currently in YYYYMMDD format, into a format for smalldatetime field MMDDYYYY. The reference line of code in the DTS transformation script is:DTSDestination("recordingdate") = DTSSource("Col005")I have other working lines of code in the script referencing other fields, this is the only one I am having difficulty with.Does anyone know of any online tutorials/references on the best way to do this is SQL2000?Thank youDevon Kyle |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-25 : 13:28:23
|
| Why don't you import the records into a temporary table (not a #temp table but a holding area)? Then after the import into this table, transfer the records from this table into the table that you need it in but for the one column use CONVERT so that the data is converted. INSERT INTO Table1 (Column1, Column2, Column3, Column4, Column5)SELECT Column1, CONVERT(VARCHAR(50), Column2, 101), Column3, Column4, Column5FROM TempTable101 in the code above is the style that I used. I don't have access to Books Online right now, so you'll need to look up which style that you need. Just look up CAST and CONVERT in BOL.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-25 : 13:28:29
|
| If it's troublesome, why not change the destination table (or make a new one) that has it defined as varchar.Then Do a SELECT * FROM myTable WHERE ISDATE(col1) <> 1To see if you have any invalid date dataBrett8-) |
|
|
|
devonkyle
Starting Member
19 Posts |
Posted - 2003-07-25 : 13:34:34
|
| I'd like to set it up in DTS as an automated script which will be importing 15 million records from an exported mainframe file once every Friday. |
|
|
|
devonkyle
Starting Member
19 Posts |
Posted - 2003-07-25 : 13:35:02
|
| I'd like to set it up in DTS as an automated script which will be importing 15 million records from an exported mainframe file once every Friday. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-25 : 14:00:52
|
quote:
Why don't you import the records into a temporary table
Great minds think alike (and at ALMOST the same time)6 Seconds Tara!!!Still not the record though...graz beat me by three once...Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-25 : 14:04:52
|
quote:
I'd like to set it up in DTS as an automated script which will be importing 15 million records from an exported mainframe file once every Friday.
Whoah!!!!!15 million..through DTS and hope you're not think of using transformations...you'll be better of with bcp I think, and THEN break it up into batchesWhat kind of data is this?Wait, let me get my calculatorIf you don't replace the data every friday, you're looking at 780 million rows a yearIf not, that's still a lot of dataYou got indexes on this thing?What's the actual req you were given?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-25 : 14:40:42
|
quote:
I'd like to set it up in DTS as an automated script which will be importing 15 million records from an exported mainframe file once every Friday.
But it still can be automated doing it the way that I suggested, all within the DTS package. You would just create another task that happens after the successful import. The task would just be the INSERT INTO/SELECT statement. It might add execution time to your DTS package, but at least it works. I believe that you could also do the transformation through VBScript, but I've never done it that way. If I ever need to change the data on import, I ALWAYS use a temporary table.Tara |
|
|
|
devonkyle
Starting Member
19 Posts |
Posted - 2003-07-25 : 14:59:56
|
| Thanks - I'll try that approach. I just spend a day trying to do it in DTS with VBscript but it keeps swearing at me. I'll try it with Cast and Convert with a Temp Table and see if I have better luck! Mucho Gracias |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-25 : 15:36:17
|
| Tara,How long do you think that'll take?And then a logged INSERT of 15 million rows?Brett8-) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-25 : 16:01:45
|
quote:
And then a logged INSERT of 15 million rows?
The insert is logged no matter what. It is an intrinsic feature of SQL Server. The real question is the interaction of the DTS batch size and locking with the recovery model of the database. Selecting the table lock and no constraint checking options results in speed nearly equal to BCP, with considerably more functionality (assuming the simple recovery model).Jonathan{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-25 : 16:20:12
|
quote:
Selecting the table lock and no constraint checking options results in speed nearly equal to BCP, with considerably more functionality
Really? I didn't think bcp was a logged operation.And also what you blow out your logs with that big of an insert?Brett8-) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-25 : 16:28:52
|
quote:
Really? I didn't think bcp was a logged operation.
Depends on your recovery model. If Full, then yes.Jonathan{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-25 : 16:32:17
|
quote:
Tara,How long do you think that'll take?And then a logged INSERT of 15 million rows?Brett8-)
I don't know, just depends on how fast the server is. Devon will need to test it out to see if the table to table copy will add too much time to the DTS package. If it does, then he will need to figure out the VBScript way.Tara |
|
|
|
devonkyle
Starting Member
19 Posts |
Posted - 2003-07-25 : 17:05:18
|
| Figured it - Did it in DTS/VB just the way I wanted it to work. Here's the script I used just in case it might help someone else in the future. Embassassingly easy.... Thank you for your input! DevonFunction Main()DTSDestination("datetest") = Cint(Mid( DTSSource("Col001") ,5 , 2 )) & "/" & Cint(right( DTSSource("Col001") ,2 )) & "/" & Cint(Left(DTSSource("Col001"),4)) Main = DTSTransformStat_OKEnd Function |
|
|
|
|
DTS Import Date Conversion