Post reply

In control flow: how to create date time stamp to use in downstream tasks.

  • July 13, 2014 at 12:24 am

    #296438

    I have a control flow which unzips 14 unique files, drops them into a process folder, and then after they're loaded into 14 different oledb tables, rezips them with a new datetime stamp appended and stores them in an archive folder.

    thanks to this video I have made progress. https://www.youtube.com/watch?v=7mPKorfSxYo

    But I am unable to reproduce the creating of the date time stamp with a script task (minute 5:47) and would like to know if there's another way to create a date time stamp that I can use to append to processed zip files, or insert as values in error logs so that errors can be mapped to the files that spawned them, etc.

    I'd really like a non-scripting solution for creating the date time stamp which Keshav creates with a script task/using VB, in the control flow.

    What control flow task can I use?

  • July 13, 2014 at 5:07 am

    #1728977

    You can use an ExecuteSql task with a query that returns the date time string, here are two of quite few options.

    😎

    SELECT CONVERT(VARCHAR(32),GETDATE(),112)

    + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(10))

    + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR(10))

    SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(32),GETDATE(),121),'-',''),':',''),'.',''),' ','')

    Result 1

    20140713124

    Result 2

    20140713120443143

  • July 13, 2014 at 5:57 am

    #1728982

    may be this will give you some ideas

    http://www.bidn.com/blogs/mikedavis/ssis/153/using-expression-ssis-to-save-a-file-with-file-name-and-date

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 13, 2014 at 9:11 am

    #1728989

    J Livingston, what's unique about my question is that I need the date time stamp created in the control flow.

    I'd really like a non-scripting solution for creating the date time stamp which Keshav creates with a script task/using VB, in the control flow.

    J Livingston, your link is for data flow and I've used that link before even....it is excellent for what it's for. Also, I like your quote.

    EE Thanks for answering my question.

  • July 13, 2014 at 12:44 pm

    #1729001

    thanks to EE, I googled "execute sql task datetime parameter" to find out how to configure the Execute SQL Task to apply the result of the query to the variable, so I could pass it as a parameter downstream.

    Used this url

    https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/.

    General

    ResultSet Single row

    Connection Type: OLE DB

    Connection: (my named oledb conection)

    SQLSourceType: Direct Input

    SQL statement : SQL Query

    Parameter Mapping

    variablename: user:: DateStamp

    Direction: Input

    data Type: VARCHAR

    Parameter name : 0

    Parameter size : -1

    Result Set

    Result Name SQLDateTime

    VariableName User::DateStamp

    Expressions

    N/A

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

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