SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Importing A Null DateTime Problem


SSIS Importing A Null DateTime Problem

Author
Message
markwnorsoft
markwnorsoft
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 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.





teligaurav
teligaurav
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
DanKennedy
DanKennedy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1696 Visits: 389
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
Janet Keith-489067
Janet Keith-489067
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 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


Dave23
Dave23
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 1806

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))


ashwinraghu001
ashwinraghu001
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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!
dg227
dg227
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 819
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)
DanKennedy
DanKennedy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1696 Visits: 389
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
dg227
dg227
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 819
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.
gregor.aj
gregor.aj
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 133
Open the Flat File Source and Click the check box in the attached picture. Compile and run.
Attachments
nulldates.JPG (206 views, 30.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search