name/date out of a txt file

  • I have a text file that I need to import. The text file is named '20090930_Virtual_Daily_Extract_test_delimited.txt'

    My first step - Using an SSIS package, I need to strip out the date (20090930) convert it to (2009-09-30) and then insert it into a date field.

    I'm new to SSIS and have no idea how to do this...

  • You have more issues, I think. For instance, is this the only file in the DIR you read it from, or is this the most recent file, 1 of several?

    But OK, to extract the date I would simply use the T-SQL statement SELECT CONVERT(datetime,LEFT('20090930abcd', 8),111); this can be done in an ExecuteSQL Task. There are many other ways to do this.

    I hope you know how to read the name of the file and put it in a package variable?

    Greetz,
    Hans Brouwer

  • no I don't know how to do that can you explain?

  • I'm fairly confident that the format you have, if imported as string not integer, will directly convert to a SQL Server DateTime data type.

    CREATE TABLE #date (theDate DATETIME)

    INSERT INTO #date

    (

    theDate

    )

    VALUES

    (

    '20090930'

    )

    SELECT

    theDate

    FROM

    #date AS D

    DROP TABLE #date

  • Have a look here, you'll probly learn from this article:

    http://www.sqlservercentral.com/articles/SSIS/67871/

    Greetz,
    Hans Brouwer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply