Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Importing A Null DateTime Problem Expand / Collapse
Author
Message
Posted Friday, September 14, 2007 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:21 AM
Points: 143, Visits: 68

Hi,

Im running SQL Server 2005 and having a problem importing a text file using SSIS.

I was using DTS to import an ASCII text file into a table in SQL 2000. The table has 2 fields: a primary key varchar(10), and a DateTime field which can be null. Everything was working fine with DTS, if the text file's date field was emtpy, i.e. (~20070914BM~, ), a Null value was placed in the table.

After I migrated to SSIS, the table shows a value of 1753-01-01 00:00:00.000 instead of a null value using the same text file as before. Scouring the net, I found that this value is the lowest possible value for a DateTime field.

To hopefully solve the problem, I tried making a new table with the same structure as the other table and manually entering in a record leaving the datetime null. Then using SSIS to export this value (from the new table) to a text file and importing that into the original table. It still loaded as 1753-01-01 00:00:00.000.

On the table structure, the default value of the DateTime field is set to use Null.

My question is why is it defaulting to this value and not using the null from the text file.

Thank you in advance.




Post #399430
Posted Sunday, September 16, 2007 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 23, 2012 10:31 PM
Points: 6, Visits: 15
hi

i am in team of ERP soft. for Construction domain , most of search is done by me in our team.

i am having the same problem and still not able to solve it , the solution i found is

private DateTime myTime;
public object MyTime
{
get
{
if (myTime.Equals(DateTime.MaxValue))
{
return null;
}
else
return myTime;
}
set
{
if (value == null)
this.myTime = DateTime.MaxValue;
else
this.myTime = (DateTime)value;
}
}

and use this method , this will give you date 1/1/0001

you have to consider it as NULL.

i think this will help you .

if you find better solution then pls. send me on teligaurav@gmail.com
Post #399597
Posted Monday, September 17, 2007 3:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:21 AM
Points: 1,588, Visits: 387
I agree working with dates from text files is not particularly great in SSIS but I have done the following in the past.

Set the text file connection manager to import the column as a string, then add a derived column transformation to your data flow. Finally add an expression to replace the value of the date column something like the following which converts emtpy strings to nulls.

(DT_STR, 50, 1252)(LEN([Datefield]) == 0 ? NULL(DT_WSTR, 50) : [Datefield])




Dan
www.firstcs.co.uk
Post #399627
Posted Monday, September 17, 2007 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2008 11:53 AM
Points: 46, Visits: 85

I gave up trying to import a text file using SSIS.  I found OpenRowSet to be more efficient and easier to program around.  I prefer Stored Procedures and TSQL over SSIS.

Here's a sample import statement.

Select * into TmpAPP_BRF FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=\\db3\ImportCSV\APP_DATA\APP_BRF\;', 'SELECT * FROM 606.csv')

\\db3\ImportCSV\APP_DATA\APP_BRF\  = the directory location of the import file 606.csv

This creates the table tmpApp_BRF. 

You won't need a schema.ini file if your registry has the correct default parameters.

HKEY_Local_Machine\Software\Microsoft\Jet\4.0\Engines\Text\

Registry Keys and Values defined here:

http://office.microsoft.com/en-us/access/HP010321591033.aspx

 

 

 

Post #399671
Posted Tuesday, September 18, 2007 7:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:59 AM
Points: 252, Visits: 1,728

I pull a lot of dates in the format of MMDDYYYY in from text files in SSIS.  I typically use something like the following as a new DT_DATE (rather than DT_DBTIMESTAMP) column in a Derived Column transformation:

LEN(TRIM(FromDate)) == 0 ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(FromDate,1,2) + "/" + SUBSTRING(FromDate,4,2) + "/" + SUBSTRING(FromDate,6,4))

 

Post #399964
Posted Wednesday, October 5, 2011 3:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 9:32 AM
Points: 1, Visits: 43
DanKennedy (9/17/2007)
I agree working with dates from text files is not particularly great in SSIS but I have done the following in the past.

Set the text file connection manager to import the column as a string, then add a derived column transformation to your data flow. Finally add an expression to replace the value of the date column something like the following which converts emtpy strings to nulls.

(DT_STR, 50, 1252)(LEN([Datefield]) == 0 ? NULL(DT_WSTR, 50) : [Datefield])


Brilliant! Been struggling with this, this works great, thanks!

Post #1185732
Posted Friday, October 7, 2011 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:24 AM
Points: 254, Visits: 754
Also, you should see an option bit in file source connection in the data flow to retain NULLs ... this will retain the NULL "value" from your file and not assign those arbitrary/bogus dates. (this also lets you not have to monkey with the date field in a derived column task to NULL-out that field in the data flow, as mentioned above - either way should work, though)
Post #1187091
Posted Friday, October 7, 2011 8:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:21 AM
Points: 1,588, Visits: 387
I think the retain NULLs option is to prevent column defaults being applied on the destination table during import, not for converting empty strings to dates within the pipeline.



Dan
www.firstcs.co.uk
Post #1187130
Posted Friday, October 7, 2011 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:24 AM
Points: 254, Visits: 754
Try it out: if the source text file has an empty string (per the original post), the Retain NULLs option will treat that empty string as a NULL and not add/convert to that dummy date. If the string field in question is white space, and not actually an empty string, then yes, the field would need to be NULLed in a derived column task. I ran into this recently myself (with the odd dates in a field that I know was empty in the source file), and this bit fixed it.

This bit has nothing to do with the destination, which is what you're referring to - as long as the destination allows NULLs, which the OP said it does, enabling the retention of NULLs at the source will pass a NULL to the destination ... a derived column task would do the trick, too, but it is extra work/unnecessary with this bit.
Post #1187134
Posted Monday, October 17, 2011 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:27 AM
Points: 16, Visits: 114
Open the Flat File Source and Click the check box in the attached picture. Compile and run.

  Post Attachments 
nulldates.JPG (111 views, 30.76 KB)
Post #1191392
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse