importing date fields 2

[GShen]

Hi,
Having a problem importing with DTS. I made it as simple as possible and I have it narrowed down but it still doesn't work. I have a ID field (identity) key to the file.
I have a field with a date. Every time I try and map this with a date field in my table I get an error (input field too narrow for output). Something like that. I tried formatting my input field 6 positions, 8, yyyymmdd, mmddyyyy, mm/dd/yy, mm/dd/yyyy. It only works if I change my table to be a character field. If I bypass the field it loads (Eg. remove it from the transformation). At that point I cannot add it back in again. If I change the date field in the table to a char (8 long), it maps. The problem is the field is not a date field. I know this can be done because I do it in another file. You will all laugh here, it is exactly the same. I tried recreating the import with that input file and I get the error again. Do I have a MS bug somewhere???????????
Thanks, I am pulling my hair out.

Remember when... everything worked and there was a reason for it?

 

[gradley]

Try importing that field using an ActiveX script. In the script use the CDate() function to convert the value to a date.

See if this solves the problem!

 

[yksvaan]

What do ya mean:
The problem is the field is not a date field.

One thing is for sure, you can't put such data to a sql server table date column, that is not date data.

I recall there are many threads about in what format the input data must be, I just don't remember the solution by heart.

And if it is not in the right format, you must reassemble the string with ActiveX transformation as gradley wrote.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[GShen]

Thanks,
One question. I was in active(x) also before I wrote this. I couldn't get anywhere. I will read Books online and try and get this to work. I was having a problem getting anything to map. I figured I could fix it once I did but like I said, I never did this before.

Remember when... everything worked and there was a reason for it?

 

[gradley]

BOL will definately guide you through.

To give you a jump start, on the "Transformations" tab, select [Delete All] followed by [Select All]. Then select the [New] button select Active X script and click the [Properties] button. You should now have a basic alignment of the fields.

Manipulate the date field as suggested above and you should be on your way.

Good luck!

 

[GShen]

gradley,
THANKS! It is a little screwed up the way it is but I got through it. I deleted all the transformations and created a NEW. I then went into the source and clicked everything over. I then went into destination and clicked everything over as well. If I said ok, I received an error. I then went into properties and exited and everything was done like magic. (I also had an identity key). I ran it. Bingo. Done.

On my way to put out the next fire. Thanks again. Let me STAR that one before I forget.

Remember when... everything worked and there was a reason for it?

 

[gradley]

Your welcome!

Glad to help..

 

[yksvaan]

So, never be afraid to start from scratch.

What still puzzles me is the question word being the main question word in the Mika Waltari's novel Sinuhe, the Egyptian...

Another star to gradley for spelling out BOL, I'm mostly too tired to do that.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]