CODEWITHSUNDEEP
ssis
WhoamI
WhoamI

Reputation: 416

SSIS - Dynamic Creation of three log files

da@[User::Log_path] + @[System::PackageName] + " " + SUBSTRING( (DT_WSTR,29) GETDATE(),1,10)
+  " "+
 ((LEN((DT_WSTR, 2) DATEPART("Hh", GETDATE()))>1)?
(DT_WSTR, 2) DATEPART( "Hh", GETDATE() ) : "0" +(DT_WSTR, 2) DATEPART( "Hh", GETDATE()    )) + "h "+ 

((LEN((DT_WSTR, 2)DATEPART( "mi", GETDATE()))>1)?
(DT_WSTR, 2) DATEPART( "mi", GETDATE() ) :"0" + (DT_WSTR, 2) DATEPART( "mi", GETDATE() )) + "m " +

 ((LEN((DT_WSTR, 2)DATEPART( "ss", GETDATE()))>1)?
(DT_WSTR, 2) DATEPART( "ss", GETDATE() ) :"0" + (DT_WSTR, 2) DATEPART( "ss", GETDATE() )) + "s  " +

 SUBSTRING(@[System::UserName],5,100) +".txt"

I have used the above in the Property Expression Editor. Upon executing the package, it creates three log files: one containing the entire information and the other two contains just the heading. Sometimes, all the three contains log details. Quite confused , whats happening.

Upvotes: 2

Views: 550

Answers (2)

user7964228
user7964228

Reputation:

I was going crazy with this. I was able to get the System StartTime to work based on billinkc's suggestion above.

I wonder if it's dependent on the number of variables in the string.

Here's what I ended up doing. For my logfile connection manager, I'm using an expression which is a user variable. In my user variable named logFileName, it's the following expression. You can see I'm only calling one variable in this. Maybe if you used multiple variables that built on each other, it would only create one file?

I hope this helps.

    "\\\\hard-coded-server-name\\Interfaces\\DailyLogFiles\\logoutput_" +
    REPLACE(REPLACE( (DT_STR,256,1252) @[System::StartTime], "/", "_"), 
    ":", "_") +".txt"

Just to provide more info, I wanted to change the path based on the server name, but I never could get it to be dynamic, tried using MachineName with no luck.

Good luck.

Upvotes: 0

billinkc
billinkc

Reputation: 61211

If I were a guessing man, my bet is that GETDATE is being evaluated multiple times during your package execution. My read of your code says you're building out a path based on package name, date and hours/minutes/seconds. Every time GETDATE is called, it returns the current value. Instead of using GETDATE, what I think you really want is the current time when the package starts. You would be able to deduce the rest of the timing information by examining the contents of the log file.

If this is the case, the simple fix is to replace all of your GETDATE() with the System variable StartTime, @[System::StartTime]

Upvotes: 3

Related Questions