Hi all,

 

I'm hoping for some guidance on importing a flat file into a new SQL database table, specifically dealing with date columns.

 

On the source flat file I have several date fields; some of these fields have date values for every record, while some fields have values for records only when an event occurred for a particular record.  Each record is, for example, a client.  One of the columns in the data is a date on which the last statement was sent to the client and in some cases there have not been  statements sent to a client and the value in this field is null.  When populated, the value on the flat file is a string that reads "MM/DD/YYYY".  I have tried three methods of importing the data.

 

First, when using the import wizard to create a new table from the flat file, I have specified that these date columns can contain null values, yet the process errors out.  The error states it can not create the new table because data might be lost.

 

Second, I actually created a sql table with the same specifications as the flat file source, specifying that each date column can contain null values.  Using the import wizard I mapped the flat file fields to the sql table columns I am trying to import the data to.  Again, the process errors out.

 

Finally, frustrated with these two attempts, I simply created a a new sql table from the source data, but treating the date columns as string fields on the import.  After importing the data, I changed the columns containing date values from string to date.  This worked fine, except that on records that had a null date string on the flat file, when changing the data type to date in the sql table it automatically assigned dates of  "1/1/1900 12:00:00 AM".  Not exactly what I would have preferred, since I then have to run queries to null these particular values out.

 

I have a fairly good MS Access background, and this would never have given me trouble using that program to import data such as this.  Howerver, being new to SQL, I'm obviously having some troubles here.  Any suggestions would be appreciated.

Todd,

 

In your post you have clearly defined a workaround. Running a querie to cleanup a database after an import is not that bad if you don't have a lot of them. I have seen the same problems as you have and never bothered about the workaround...

Hi Mariette,

 

Agree that it's not that bad, considering I have only three date type columns to clean up after the import.  The only downside is that this particular import process is going to take place on a daily basis (and it's not the only flat file that I will have to import each day).  Over the long run, it would be a lot easier if the import could be modified such that it doesn't error out when it encounters a null date field on the flat file.

 

All of the actual production data resides on another server in an RPGII database (), and to at least web-enable this stuff I am going to be taking nightly snapshots of that data an importing into SQL.  It may be months before I can rewrite all of the production processes in SQL before I can do away with the legacy system.

 

Thanks for you feedback, though.  Certainly appreciate it.

 

Todd

Mariette,

 

Here it is:

 

 

Column 7 is a date field on the source file, where some records contain null values.  Data row 19 is the first record the import encounters with a null value.

 

Todd

Tod,

 

Have a look here:

 

Sql Server 2005 Import Wizard - keep NULLS - Database Forum:
http://www.mydatabasesupport.com/forums/sqlserver-dts/338931-sql-server-2005-import-wizard-keep-nulls.html

Mariette,

 

Thanks for the link.  I've encountered the same situation that the user on that forum faced, in that the SQL 2005 SSIS wizard doesn't allow the user to keep nulls.  I tried working around that by importing the data into an existing table that specified nulls were acceptable, but that didn't work either.

 

As to the person in that forum who suggested saving the SSIS package and manually editing it, I have looked into that already.  When I open the package however, to resuse it, I can't seem to find anywhere in the opened utility that allows one to edit the specifications.

 

Thanks, again, for the help.  I have a couple more things that I'm going to try.  Perhaps I'll stumble on something.

 

Todd

The dates in Access are just that; in the Access table they are specified as dates and Null values are present in some of the records where appropriate.

 

When I imported the Access table to SQL, I am guessing that the Jet OLE engine used in the SSIS wizard recognized the Access fields as dates, and imported them correctly, resulting in a SQL table with a column for that data taking on the date data type, and allowed Null values to be present.

I did find the ultimate solution thanks to one of the folks on TechNet.  Create the package using the SSIS Wizard, and save it.  Then, open the package in Visual Studio or Management Studio and edit the properties of the Data Flow to stop the package from erroring out on the conversion if null values are present, and to accept null values.

 

Life is good.