Having issues with simple VB script

Question

I am having issues running the following VB script to create a new directory in my SSIS package. It should create the following new path D:\customers\documents\orders\20171205.

Public Sub Main()
    'set dir path
    Dts.Variables("var_DirPath").Value = 
        "D:\customers\documents\orders\" + Date.Today.Year.ToString + 
        Date.Today.Month.ToString + Date.Today.Day.ToString
    Dts.TaskResult = ScriptResults.Success
End Sub

The ReadWriteVariables of the script task editor is configured to User:var_DirPath. The value of the variable is “D:\customers\documents\orders\"

The file task is configured for

UseDirector/Exists = True
Operation = Create directory
IsSourcePathAvailable = True
Source variable = User:var_DirPath

It resolves in the package during debugging, but I do not know why this simple script does not work.

Any assistance is appreciated.

Something seems fishy. The code you provided _should_ work, if not as nicely as @jmcihinney's suggestion. For me it sets the var to "D:\customers\documents\orders\2017125". Can you alter your path or something to prove this code is running? — GMan80013, Dec 06 '17 at 03:48
I think that using Variable with expression is more simpler than using Script task — Yahfoufi, Dec 06 '17 at 15:02
@DavidF i provided some suggestions in my answer, but please clarify what did you mean by `but I do not know why this simple script does not work.` ... *How it is not working? Are you receiving an Error message or what?* — Hadi, Dec 06 '17 at 18:44

score 0 · Answer 1 · answered Dec 05 '17 at 23:33

I'm not sure why you wouldn't see any date subfolder but that code has other issues. For instance, January 11 and November 1 would produce the same folder name for the same year. Try this:

Dts.Variables("var_DirPath").Value = String.Format("D:\customers\documents\orders\{0:yyyyMMdd}", Date.Now)

That is better than concatenation and the format ensures unique folder names for all dates.

Hadi · Accepted Answer · 2018-01-04T13:48:45.413

Some Suggestions

You have to make sure that the variable name is typed correctly, because the name is case sensitive. Also you can use Date.Now.ToString("yyyyMMdd") instead of concatenating the date parts.

Public Sub Main()
    'set dir path
    Dts.Variables("var_DirPath").Value = 
    "D:\customers\documents\orders\" & Date.Now.ToString("yyyyMMdd")
    Dts.TaskResult = ScriptResults.Success
End Sub

Also make sure to set the File System Task Delay Validation property to True

Simpler Solution - Using Expression

By the way you can achieve this using a expressions, Just go to the variable list, click on the variable var_DirPath, press F4 to show the properties Tab, Set Evaluate as expression property to True, click on the Expression property and use the following expression:

 "D:\\customers\\documents\\orders\\" + (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("MM" , GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

Having issues with simple VB script

2 Answers2

Some Suggestions

Simpler Solution - Using Expression