SQL DTS TRansform Script "type" error?

[wgjames]

CREATE TABLE [PHIL].[dbo].[events] (
DTSDestination("Evt_Date") datetime=DTSSource([Col001]) char (6) NULL,
DTSDestination("Evt_Time") datetime=DTSSource[Col002] char (6) NULL,
DTSDestination("Rec_Type") varchar=DTSSource[Col003] char (1) NULL,
DTSDestination("Rec_Subtype")int=DTSSource[Col004] char (1) NULL,
DTSDestination("Evt_Number") varchar=DTSSource[Col005] char (10) NULL,
DTSDestination("a911_Time") datetime=DTSSource[Col006] char (6) NULL,
DTSDestination("Lv_Time") datetime=DTSSource[Col007] char (6) NULL,
DTSDestination("Orig_Area") varchar=DTSSource[Col008] char (7) NULL,
DTSDestination("Own_Area") varchar=DTSSource[Col009] char (7) NULL,
DTSDestination("Med_Zone") varchar=DTSSource[Col010] char (2) NULL,
DTSDestination("Location") varchar=DTSSource[Col011] char (50) NULL,
DTSDestination("Event_Type") varchar=DTSSource[Col012] char (6) NULL,
DTSDestination("Event_Code") varchar=DTSSource[Col013] char (1) NULL,
DTSDestination("Event_CdUpDt") varchar=DTSSource[Col014] char (1) NULL,
DTSDestination("Event_Details") varchar=DTSSource[Col015] char (133) NULL,
DTSDestination("Med_Post") varchar=DTSSource[Col016] char (4) NULL,
DTSDestination("Spaces") varchar=DTSSource[Col017] char (11) NULL,
DTSDestination("DryRun_Code") varchar=DTSSource[Col018] char (1) NULL,
DTSDestination("Disp_Unit") varchar=DTSSource[Col019] char (4) NULL,
DTSDestination("Disp_Type") varchar=DTSSource[Col020] char (6) NULL,
DTSDestination("C_Date") datetime=DTSSource[Col021] char (6) NULL,
DTSDestination("C_Time") datetime=DTSSource[Col022] char (4) NULL,
DTSDestination("X_Coord") bigint=DTSSource[Col023] char (7) NULL,
DTSDestination("Y_Coord") bigint=DTSSource[Col024] char (6) NULL,
DTSDestination("ZBB")int=DTSSource[Col025] char (4) NULL,
DTSDestination("Juris") varchar=DTSSource[Col026] char (2) NULL,
DTSDestination("Xception") varchar=DTSSource[Col027] char (1) NULL,
DTSDestination("DUC") varchar=DTSSource[Col028] char (1) NULL,
DTSDestination("SubZone") int=DTSSource[Col029] char (2) NULL,
DTSDestination("SysLevel") decimal=DTSSource[Col030] char (2) NULL,
DTSDestination("EarlyTime") datetime=DTSSource[Col031] char (6) NULL,
DTSDestination("EarlyCode") varchar=DTSSource[Col032] char (1) NULL,
DTSDestination("ExternlAgFlag") varchar=DTSSource[Col033] char (1) NULL,
DTSDestination("ExcadEvtNum") varchar=DTSSource[Col034] char (10) NULL,
DTSDestination("ECorig_Area") varchar=DTSSource[Col035] char (5) NULL,
DTSDestination("ECcomCode") varchar=DTSSource[Col036] char (1) NULL,
DTSDestination("Fire_Juris") varchar=DTSSource[Col037] char (2) NULL,
DTSDestination("EventPAI") varchar=DTSSource[Col038] char (3) NULL,
DTSDestination("PAI_Badge") varchar=DTSSource[Col039] char (4) NULL,
DTSDestination("MPDS") varchar=DTSSource[Col040] char (7) NULL,
DTSDestination("SpcNotUsed") varchar=DTSSource[Col041] char (6) NULL
)[FONT =Arial] 12

[skhanal]

Make sure the source and destination columns have compatible datatypes.

[rmiao]

If you want to convert char type to numeric type, have to ensure values in char type are number only.

[Alexander]

Why is it Create Table statement in the first place?

[wgjames]

My assignment is to take an undelimited, raw, text file that is auto-created everyday (all the county's ambulance runs recorded in our Co' Comm center) and auto-schedule the import of that file (every day) into a delimited, structured (with specific field names and field types) file into a replaceable, SQL table. Consequently, I chose to use the DTS wizard design of a previously run DTS that kept all the fields as char. I revisited it and used the code you see, copied from an SQL book. However, when I ran it, I got the error message that it could not find the destination, field "type".
I thereupon pasted the script into a SQL query, and via trial and error eliminated the "field type" error only to get another error. In the final try, the message said I had the wrong syntax around the ' = ' sign.
CREATE table is what SQL DTS does.

[skhanal]

Where did you copy this script from? If it is from transform properties, it should not have CREATE Table there. It should have a function called main, then all the transformations.

[wgjames]

I have narrowed down the issue to DTS refusing to convert the date fields
I am given to: 'datetime' --Unfortunately, I have several of this type of data in this text file.
Following is a sample of the text data that I am receiving:

050822000421

The user wants to see this info in two separate columns, one for date, formatted, 8/22/2005
and one for time: 00:04:21.
When I just let the defaults in DTS bring the data into my SQL table with all 'char' fields,
separating the 1st 6 bytes as Col001, and the 2nd 6 bytes as Col002, followed by running
a SQL script that alters Col001 to datetime, it works, but it comes out
in the following format:

2005-08-22 00:00:00.000

--and Col002 (time) does not convert.

Again the user expects to see the following format:
8/22/2005
Ironically, when I do not use a script to change Col001 to datetime, but manually
bring up the table in 'DESIGN' mode, and change the Col001 char type to datetime, it does
convert the data to the desired, 8/22/2005 format.

I also tried combining Col001 with Col002 into one column, 050822000421, but
altering this column failed in script and manually, with a syntax error.

--Type, smalldatetime, works exactly the same as datetime.

[rmiao]

You can try load data to staging table as it is, then covert to datetime type when load data from staging table to target table.

[wgjames]

Your answer may have to suffice.
Meanwhile, I also have the problem:
Because the 'events' table created will be replaced daily, I need to save it for the user, ideally with the date concatenated to the name of the table, i.e. events090105.
Is there a script available to do this?
Minimally, something, as in done in MSACCESS wherein when a table of an equal name is imported or copied, it gets a sequenced number attached to the incoming name, i.e. EVENTS1.

[rmiao]

You can use sp_rename to change table name.

[wgjames]

Yes. I know how to simply rename a table.
What I am looking for is the capability when I auto-rename the table to concatenate a sequential digit/date to the name, inasmuch as the whole process needs to be automated, producing the desired result without any further intervention by me or anyone else.

[rmiao]

You can write script to rename table and set scheduled job to run your script.

[wgjames]

Thanks all.
I figured out a way to satisfy the user.
Let this be a closed issue.