September 14, 2007 at 12:53 pm
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.
September 16, 2007 at 11:38 pm
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
September 17, 2007 at 3:59 am
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])
September 17, 2007 at 8:04 am
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
September 18, 2007 at 7:12 am
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))
October 5, 2011 at 3:19 am
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!
October 7, 2011 at 7:34 am
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)
October 7, 2011 at 8:10 am
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.
October 7, 2011 at 8:15 am
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.
October 17, 2011 at 8:47 am
Open the Flat File Source and Click the check box in the attached picture. Compile and run.
April 26, 2013 at 12:13 pm
hi,
i ran into same issue. I spent sometime trying to understand and fix it by redesigning tables and other ways, but to no avail.
So I included a Execute SQL Task in my SSIS which updates the table, updates all the records with '1753-01-01 00:00:00.000' to NULL.
I know this is quite a while ago, hope this helps people in the future.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply