-1

I have the following format to account for weekends while naming an excel output. How do I get only the date and not the full startime formatted like 20150429?

  ((DT_I4)DATEPART("weekday",@[System::StartTime]) ==2 ? 

    Replace((DT_STR, 20, 1252)(DATEADD( "D", -3,@[System::StartTime])),":","") + 
".xlsx" :   

    Replace((DT_STR, 20, 1252)(DATEADD( "D", -1,@[System::StartTime])),":","") + 

".xlsx")
0

1 Answer 1

4

Getting a date

To get the YYYYMMDD, sometimes written as CCYYMMDD, I would use an expression like

(DT_WSTR,4)YEAR(@[System::StartTime]) 
+ RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2) 
+ RIGHT("0" + (DT_WSTR,2)DAY(@[System::StartTime]), 2)

Reading that, I use the Year function to extract the 4 digit year and then cast that as a unicode string.

I then use the Month function to return the month number. which I then cast to a string. The challenge with this and day is that they are returned as integers and leading zeros are display artifacts. So I convert a 1 or 2 character integer value into a string and prepend a leading zero to it. At this point, I either have a value like 01 or 012 75% of the time, this works but it's the remaining 25% that we need to fix so I wrap the resultant value with a RIGHT call an retain only the trailing 2 values.

Lather, rinse repeat but instead I use the DAY function.

Take all three values and combine as desired

My personal preference is to have many Variables in my SSIS packages, so I would likely create a variable of type String, using the above Expression named Date_YYYMMDD. Or I might have created 3 individual entities as strings and then concatenated them together as maybe I need both YYYYMMDD and YYYY-MM-DD and would hate to maintain that concatenate/trim logic.

That's the general approach to this problem. Let's get down to brass tacks and solve your specific problem.

Ready for the weekend

I don't really get what an expression for the weekend is but it looks like you either want one day back or 3 days back based on today. That's a Variable in my world.

I create @[User::SourceDate], of type DateTime and set EvaluateAsExpression to true and use this formula

(DT_I4)DATEPART("weekday",@[System::StartTime]) ==2 ?
DATEADD( "D", -3,@[System::StartTime]):
DATEADD( "D", -1,@[System::StartTime])

Now my logic is nice and encapsulated and if I discover that I have another case to factor in (today is 1 or 3 and I need to back off 4 or 2 days), then I just fix and test this one thing.

Using SourceDate

Now that we have SourceDate, we'll apply the first set of expressions to make it into a YYYYMMDD string. Create a second Variable, @[User::SourceDate_YYYYMMDD] of type String, set EvaluateAsExpression to true and use the following expression

(DT_WSTR,4)YEAR(@[User::SourceDate])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@[User::SourceDate]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@[User::SourceDate]), 2

See how we simply use our variable SourceDate in here. Nothing complex at all

Make our file name

The last bit will be using the string version of our SourceDate and appending the file extension onto it. The third Variable is @[User::NewFileName] of type String, EvaluateAsExpression = true

@[User::SourceDate_YYYYMMDD] + ".dtsx"

Here we simply apply the .dtsx extension to our string but it's a much simpler problem to preface our file with ClientName_YYYYMMDD.xlsx when we break it down into tiny little problems.

Biml all the things

Finally, it wouldn't be an answer from me without the corresponding Biml. Biml, is the business intelligence markup language. It is the operating system for business intelligence. We're going to use it to generate an SSIS package. All you simply need to do is download the free addin for Visual Studio/BIDS/SSDT-BI called BIDS Helper

  1. Right click on an SSIS project and select Add new Biml File
  2. Double click the BimlScript.biml file and replace the contents with the following
  3. Right click on BimlScript.biml and select "Generate SSIS Packages"
  4. See the resultant SSIS package added to your project
  5. Take a break
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="dba_99131" ConstraintMode="Linear">
            <Variables>
                <Variable 
                    DataType="DateTime" 
                    Name="SourceDate" 
                    EvaluateAsExpression="true">
    (DT_I4)DATEPART("weekday",@[System::StartTime]) ==2 ?
            DATEADD( "D", -3,@[System::StartTime]):
            DATEADD( "D", -1,@[System::StartTime])
                    <Annotations>
                        <Annotation AnnotationType="Description">
                            Calculate 1 or 3 days back depending on day of week
                        </Annotation>
                    </Annotations>
                </Variable>
                <Variable 
                    DataType="String" 
                    Name="SourceDate_YYYYMMDD" 
                    EvaluateAsExpression="true">
    (DT_WSTR,4)YEAR(@[User::SourceDate])
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::SourceDate]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::SourceDate]), 2)
                    <Annotations>
                        <Annotation AnnotationType="Description">
                            Convert our date into a YYYYMMDD format
                        </Annotation>
                    </Annotations>
                </Variable>
                <Variable 
                    DataType="String" 
                    Name="NewFileName"
                    EvaluateAsExpression="true"
                    >@[User::SourceDate_YYYYMMDD] + ".dtsx"
                    <Annotations>
                        <Annotation AnnotationType="Description">
                            Generate our final file name
                        </Annotation>
                    </Annotations>
                </Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>

Sample package

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.