.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

I am having trouble importing a datetime from a text file.

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

In my Derived Column Transform editor I have the following

(DT_DBTIMESTAMP)(SUBSTRING(TRIM(Admit_Date),1,4) + "-" + SUBSTRING(TRIM(Admit_Date),6,2) + "-" + SUBSTRING(TRIM(Admit_Date),9,2) + " " + SUBSTRING(TRIM(Admit_Date),11,2) + ":" + SUBSTRING(TRIM(Admit_Date),14,2) + ":00")

The data type column has string [DT_STR] as the value. 

The string looks like 2011-04-04 23:00 

The connection manager has the datatype set to string [DT_STR) with the OutputColumnWidth set to 18

The Admit_Date field is in a temp table created like this [ADMIT_DATE] datetime NULL, 

And below is the error I get

[Derived Column [203]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (203)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Admit_Date" (251)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

Any ideas. Everything I have read tells me to parse the string out like I have, and it works for all my date only fields. e.g. (DT_DBDAT

View Complete Post

More Related Resource Links

Importing text file datetime string into SQL datetime column



Using SSIS 2008 R2 to import text files.  Two of the fields contained are datetime values in the format "2010-09-24 18:58:29.220".  Ultimately both the date and time need to be extracted with precision to the second, at the very least.  When I import the text file, however, the time value seems to get truncated before the date value's being imported, for an imported result of "2010-09-24 00:00:00.000".

Is my problem with the Flat File Connection Manager?  Regardless of which DataType choice I use for the properties of my datetime values--I've tried every form of date datatype--the imported column values contain the correct date but the wrong time value.

Is there an easy way to import a text file datetime value straight into a datetime column?  Is it possible, or am I trying to shortcut what should be a proper process?


Importing text file datetime string into SQL datetime column



I'm importing a text file into SQL Server 2008 R2 using SSIS 2008 R2.  Included in the text file is a datetime column with values like "2010-09-24 18:58:29.220".  However, when I import the file, the stored value has the time portion replaced with what I assume is the default, such as "2010-09-24 00:00:00.000".

Is there an easy way to import a datetime value from a text file into a datetime column in my table?  I've tried every form of date datatype in the Flat File Connection Manager, but I still keep getting the correct date portion with the incorrect time portion.

Am I trying to do something that wasn't intended?  I see Todd McDermid's page (http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html) and am starting to wonder if perhaps I'm trying to shortcut something that I shouldn't.


Trouble importing .dbf file into SQL server 2008 express edition


Hey all,

I'm having trouble exporting my .dbf file into Microsoft SQL server 2008 express edition. I set up an ODBC using a couple different drivers and different versions of the drivers and still got the same error. Below is the error message I am receiving.


TITLE: SQL Server Import and Export Wizard

Could not connect source component.

Error 0xc02020ff: Source - HOSPSITE [1]: The component "Source - HOSPSITE" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [HYS12] [Microsoft][ODBC dBase Driver] Index file not found.


Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)



If any one can help that would be greatly appreciated! Thanks!

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Writing to a text file



I have the following  subroutine in an asp.net application class. The subroutine writes data to a text file upon application login.   My question is that if this function was called at the same time by two different  users would it cause any kind of error. Is there a need for a try catch?

 Public Shared Sub writeToLogFile(ByVal UserName  As String)
        Dim strLogMessage As String = String.Empty
        Dim strLogFile As String = System.Web.HttpContext.Current.Server.MapPath("~/Log.txt") Dim swLog As StreamWriter
        strLogMessage = DateTime.Now.ToShortDateString().ToString()  " ==> " & UserName

        If Not File.Exists(strLogFile) Then
            swLog = New StreamWriter(strLogFile)
            swLog = File.AppendText(strLogFile)
        End If


The file reached the maximum download limit. Check that the full text of the document can be meaning



I'm facing an issue with the indexing.

I have 1 WFE+Index server+DB server.

Index server is not installed with MS FIlter pack 1.0

When crawling, the there will be document with warning in crawl log:
The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

Documents that with warning are such as doc, ppt, xls, docs, ppts and many others.
However, I view into the successful crawled document, there are doucments with ext doc and ppt.

For large file index, there are MaxGrowthFactor + MaxDownLoadSize required to be added into the index server.

As my understanding is, MS Filter Pack should installed into index server(already did, correct me if i'm wrong).

I looked into the Office SharePoint Seach(CA>Services in farm), if the server is appointed to "Use this server as indexing server", then MS Filter Pack is suppose to be installed into that particular server as well.

At the bottom, there also has another option is "Use all web front end for crawling".

The question here is, IF the option "Use all web front end for crawling" is selected.
Does the WED FRONT END Server required to installed the Ms Filte

Update Profile Database from text file using BDC



I have a requirement to import the profile properties from a text file in sFTP to update the MOSS Profile Database. The objective is to create a single repository for the Staff Directory in MOSS.

Can I use BDC to connect and update the Profile database?

What is the alternate approach?


Deleting Last Line of Text in a text file

Hi, I needed some help on the best way to delete the last line of  text in a text file. I have a subroutine that looks like this, but it is missing some code. Could anyone advise me on this? Dim strLogFile As String = System.Web.HttpContext.Current.Server.MapPath("~/Log.txt")         Dim fs As New FileStream(strLogFile, FileMode.Open, FileAccess.ReadWrite, FileShare.None)         Dim swLog As New StreamReader(fs)         Dim array As String()         Dim value As String = swLog.ReadToEnd ''Array that holds the lines of the text file.         array = value.Split(vbCrLf)        ''code for deleting last line: . . .  fs.Close()

Data loading from text file

Hi, I am loading the data from a flat file into a sql table. The values in one of the column have spaces at the end of the value. I want to remove those spaces and load into the table. I am thinking of using derived column and use rtrim on that column, how can I use that as an expression. Thanks.sqldev

Consistently running out of page file memory with full text indexer

Using MS SQL Server 2008 SP1 x64 Standard Edtion on Windows 2008 R2 Enterprise, I'm about to full-text index for the first time 1 table and 2 views. The table contains about 250'000 entries with a data space of 180 MB. As soon as I activate the full text indexing, the fdhost.exe task starts to consume slowly but surely all the available page file space (this can be easily watched using the Resource Monitor and the Commit Charge graph on the memory tab). Once all the virtual memory has been consumed, the server becomes unusable since it can't open any new windows any more, and RDP stops working. The machine specs are as follows: 12 GB of RAM 80 GB free on hard disk out of 136 GB 8 CPUs Custom size paging file with sizes between 24 GB - 60 GB (originally, this was system managed size, but then the server ran out of memory sooner) Max SQL server memory set to 6 GB (first 10 GB, then 8 GB) I've set the max fulltext crawl range to 8. During the indexing, the 8 CPUs are bit busy for a while, but not excessively. What is astonishing is that there is almost no use of physical memory during the indexing (I can see an increase from 2 GB to 3 GB which still leaves plenty of RAM available). Does anybody have an idea how I can convince fdhost.exe to consume physical memory and leave the paging memory alone? Or what else can I try?

Read a text file on another server from a web page

How do I read/write a text file on another server from a web page. I get the error "Access to the path '//Server2/mydatafiles/test.txt' is denied". I do not get the error if I am running the browser on the server where the files exist. I think I need to set permissions on the destination server in some way.   Can you help? Thanks  

importing .csv file into MS SQL 2005

Greetings :-) I need to import data from my excel .csv file into a database in MS SQL server 2005. Can someone show me the steps? Is there a way to do this with a wizard since I'm not a DBA? Thanks in advance for your assistance   .

convert xml file as text file using xml document

hi i want to convert to xml file to text file using xml document.. M not much aware of xml..so please guide me.... my boss told me don use xmltextreader and using xml document convert to txt file.....  

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

How to put text from an external text file into a text box on a report.

Hi all, We're moving from our legacy reporting app to SQL Server Reporting Services 2008 and I'm converting our reports slowly but surely.  In our organization, one of our departments sends out letters that have standard marketing messages and disclaimers that they put into .rtf files.  Currently, our reporting application has an RTF box control that can do that fairly easily.  I've not found a similar control in SSRS 2008.  My thought, then, was to use a piece of embedded code in the report to grab the necessary text from the file and reference it in the Value of a Placeholder (using a Code.<FunctionName>.ToString() statement) - using HTML tags for the necessary formatting for those files. I don't care if I have to convert the existing RTF files to HTML files to do this, that's not an issue.  The code I'd come up with is this:  Public Function GetTextFile(ByVal strFileName as String) As String Dim oFile as System.IO.File Dim oRead as System.IO.StreamReader oRead = oFile.OpenText(strFileName) GetTextFile = oRead.ReadToEnd() End Function And the Expression I was using to get the text from the file is:  =Code.GetTextFile("HTMLTest.txt").ToString() Where "HTMLTest.txt" is the file name that I'm using.  The text itself is just a line of text with some HTML tags included for formatting - bold, color,

Saving SSIS results to Log or text file using dtexec

Hi, How to save the SSIS package results to log file using dtexec command............please help regaridng this...........   Thanks in advance,

Trouble importing strange column format report from Excel 2007 to SQL 08

Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend