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 |
|
paulie
Starting Member
3 Posts |
 Posted - 2004-07-16 : 11:39:24
|
| I have created a dts package to import a .txt file every night. The package drops the table then creates a new one before it imports the data. I used the datetime datatype for the date field (not sure if it should be smalldatetime).I have a couple of issues with the dates which are formatted like this "mm/dd/yyyy". the default value is "00/00/0000".I changed the transformation of the date field from "copy column" to "date time string". I think this should work but I'm not sure because the 00/00/0000 dates are causing an error, when I execute the package.I want to enter a null value for every invalid date.I would appreciate any info that might help me. ThanksPaul |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-16 : 13:03:37
|
| You should import the data into a staging table that has varchar as the data type. Then move the data from the staging table to your destination table. Use CASE in the SELECT to modify the date to NULL.Tara |
 |
|
|
paulie
Starting Member
3 Posts |
Posted - 2004-07-16 : 13:17:53
|
| tduggan,I can't thank you enough for your reply.I have created the staging table with all the fields as varchar. So far so good, but being new at this I don't think I'll be able to complete the package without an example to follow.Do you have some sample code that would be similar to this that you could copy and paste for me.Thank you for any additional help you can offer.Paul |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-16 : 13:23:21
|
| Step 1: Execute SQL Task that deletes from staging table (I would not suggest dropping it each time, just clear it out)Step 2: Move data from text file into staging table, you'll need a text file source and SQL Server destination with a transform task in between. Just do a copy column between each of the fields.Step 3: Execute SQL Task that moves data from staging table to destination table. Here is some sample code on that (Column3 will be the datetime column):INSERT INTO DestTable (Column1, Column2, Column3)SELECT Column1, Column2, CASE WHEN Column3 = '00/00/0000' THEN NULL ELSE Column3 ENDFROM StagingTableTara |
|
|
|
paulie
Starting Member
3 Posts |
Posted - 2004-07-16 : 16:55:12
|
| Your advise really helped me alot.I did the same data validation on a bunch of other fields and now I have a package that creates a very usable table for reports ect.Thanks AgainHave a great weekend. |
|
|
|
|
|
|
invalid dates causing error in .txt import