none
creating log file with current timestamp

    Question

  • how do i create a log file with current timestamp, so it is unique else the logs will be appended to the same file and will get confusing?
    Thursday, July 30, 2009 11:08 AM

Answers

  • Create a log connection manager and change its connection string using expressions.
    Example: Chnage the connection string with the folowing expression
    "Log_" +  SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ ".txt".

    Usage type is create file while adding the log connection manager


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:19 PM
    Thursday, July 30, 2009 11:27 AM
  • Hi Joseph,
    Lots of post from u today ;)

    First setup a flat file conncetion for the below file.
    Create a log file by name, LogFile.txt in the folder C:\Logs\

    once the conncetion is setup goto the properties of the flat file conncetion.
    Set the expression, select conncetion string and set the expression as "C:\\Logs\\LogFile" + (DT_WSTR,30) Month(getdate()) + (DT_WSTR,30) DAY(getdate()) + (DT_WSTR,30) YEAR(getdate())+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    evaluate the expression and see if its what you want.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:19 PM
    Thursday, July 30, 2009 11:44 AM

  • Dont use \ in ur expression use the following expression:

    "C:\\Logs\\LogFile" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    The months & days & year will be separated by "-" for clarity..

    Also check the folder name, change it wo the folder name u have.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:18 PM
    Thursday, July 30, 2009 12:26 PM
  • while configuring the SSIS logging have you set the events for which u wish to log??

    GOTO SSIS->Logging

    Select the package check box on the left hand side,
    I guess u have added the log provider & the provider type.
    Now goto the details tab on the right pane.

    You will see a number of events.

    Select the check box for all the events u wish to log. Dont check every thing as it will take time for logging those.
    Select only those that are required.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Proposed as answer by Nitesh Rai Thursday, July 30, 2009 1:17 PM
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:18 PM
    Thursday, July 30, 2009 1:15 PM

All replies

  • Create a log connection manager and change its connection string using expressions.
    Example: Chnage the connection string with the folowing expression
    "Log_" +  SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ ".txt".

    Usage type is create file while adding the log connection manager


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:19 PM
    Thursday, July 30, 2009 11:27 AM
  • how do i create a log connection manager?
    Thursday, July 30, 2009 11:33 AM
  • go to SSIS menu.
    There you will get the Logging option. Select the provider type (SSIS Log Provider for Text Files).
    Then Click add. Then in same window you will get an entry for the log provider you have just selected.
    Under configuration select New Connection.
    Make usage type as Create.
    Then change the connection string of this connection manager
    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, July 30, 2009 11:38 AM
  • Hi Joseph,
    Lots of post from u today ;)

    First setup a flat file conncetion for the below file.
    Create a log file by name, LogFile.txt in the folder C:\Logs\

    once the conncetion is setup goto the properties of the flat file conncetion.
    Set the expression, select conncetion string and set the expression as "C:\\Logs\\LogFile" + (DT_WSTR,30) Month(getdate()) + (DT_WSTR,30) DAY(getdate()) + (DT_WSTR,30) YEAR(getdate())+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    evaluate the expression and see if its what you want.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:19 PM
    Thursday, July 30, 2009 11:44 AM
  • Cheers.... getting a bit muddled up at the end...

    The following i took

    I select provide type as "SSIS Log Provider for Text files".

    in the Configurations bit i selected a new connection, the Usage type is "Create File"

    The file path is set to my existing LogFile location.... where do i enter the following expression?

    "Log_" +  SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ ".txt"

    thanks

    Thursday, July 30, 2009 11:49 AM


  • select the file conncetion click F4 properties window will open. there expand the expressions property.

    then follow my last post.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Thursday, July 30, 2009 11:51 AM
  • You could use this expression:

    "C:\\Logs\\LogFile" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    Why I tell u to use this expression is that if the package is run more than once it will capture the seconds as well so this file will be unique.


    as far as package configurations are concerned check his link:
    http://msdn.microsoft.com/en-us/library/cc895212.aspx



    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Thursday, July 30, 2009 11:55 AM
  • Yes, Sudeep is correct if the package will execute daily more than once.
    I am assuming that the package will be scheduled at a daily basis through SQL Agent.
    Depends on your requirement.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, July 30, 2009 12:01 PM
  • i've ammended the code slightly,

    "C:\\Logs\\LogFile" + "_"+(DT_WSTR,30) YEAR(getdate()) +"/"+ (DT_WSTR,30) Month(getdate()) +"/"+ (DT_WSTR,30) DAY(getdate()) +"_"+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    but i get the following error message

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Package Test [Connection manager "LogFile.txt"]: The file name "C:\Logs\LogFile_2009/7/30_13:16:35.txt" specified in the connection was not valid.

    Error at Package Test: The result of the expression ""C:\\Logs\\LogFile" + "_"+(DT_WSTR,30) YEAR(getdate()) +"/"+ (DT_WSTR,30) Month(getdate()) +"/"+ (DT_WSTR,30) DAY(getdate()) +"_"+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    Error at Package Test [Log provider "SSIS log provider for Text files1"]: The SSIS logging provider has failed to open the log. Error code: 0x80070003.
    The system cannot find the path specified.

    Error at Package Test [Connection manager "LogFile.txt"]: The file name "C:\Logs\LogFile_2009/7/30_13:16:35.txt" specified in the connection was not valid.

    Error at Package Test: The result of the expression ""C:\\Logs\\LogFile" + "_"+(DT_WSTR,30) YEAR(getdate()) +"/"+ (DT_WSTR,30) Month(getdate()) +"/"+ (DT_WSTR,30) DAY(getdate()) +"_"+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + ":"+SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Thursday, July 30, 2009 12:16 PM

  • Dont use \ in ur expression use the following expression:

    "C:\\Logs\\LogFile" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+ SUBSTRING((DT_WSTR,30) getdate(),12,2 ) + SUBSTRING((DT_WSTR,30) getdate(),15,2 ) + SUBSTRING((DT_WSTR,30) getdate(),18,2 )+  ".txt"

    The months & days & year will be separated by "-" for clarity..

    Also check the folder name, change it wo the folder name u have.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:18 PM
    Thursday, July 30, 2009 12:26 PM
  • is your file name LogFile_2009/7/30_17:56:05.txt?

    why dont you change it to LogFile_2009730_12:33:44.txt? or you can use "-" for seperating year and date

    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, July 30, 2009 12:26 PM
  • its worked :-)

    it created 2 texts files, but the file is empty... it contains the following headers

    #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
    Thursday, July 30, 2009 1:10 PM
  • while configuring the SSIS logging have you set the events for which u wish to log??

    GOTO SSIS->Logging

    Select the package check box on the left hand side,
    I guess u have added the log provider & the provider type.
    Now goto the details tab on the right pane.

    You will see a number of events.

    Select the check box for all the events u wish to log. Dont check every thing as it will take time for logging those.
    Select only those that are required.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Proposed as answer by Nitesh Rai Thursday, July 30, 2009 1:17 PM
    • Marked as answer by SQL_1980 Thursday, July 30, 2009 1:18 PM
    Thursday, July 30, 2009 1:15 PM
  • You need to select the events that you want to capture.
    You can do that by goign to Loggiong (SSIS menu), check the connection and go to details. There you need to select the events
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Nitesh Rai Thursday, July 30, 2009 1:17 PM
    Thursday, July 30, 2009 1:16 PM
  • execellent you a STAR :-)
    Thursday, July 30, 2009 1:18 PM