create date variable

i want to create a variable in SSIS that contains a date. The format is "/vaftp1/NewFile/data/2014-06-08 02;01;38 (Full).zip" , "/vaftp1/NewFile/data/2014-07-08 02;01;38 (Full).zip", "/vaftp1/NewFile/data/2014-08-08 02;00;42 (Full).zip" this file will dump on ftp site every day and I want to unzip it. so my question is I created a variable "/vaftp1/NewFile/data/"+ (DT_WSTR, 20) (DT_DBDATE) GETDATE()+?????????????()+""(full)"+".zip" but its not working.i don't know want to ignore ??????? . all i want is to get the date + anything(means the time)+(full)+.zip like for word we say (*.txt)to get any thing end by .txt so please help me

more ▼

asked Aug 08, 2014 at 02:24 PM in Default

avatar image

31 3 4 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This might help. It shows how to gather a list of files in a folder and check a date in the file. You ahould be able to adjust it to suit your situation.

 ' check target folder for files
 ' get target folder from user variable
 Dim targetFolder As String = CStr(Dts.Variables("User::LoadFileFolder").Value)
 ' create array of files within the folder 
 Dim fileEntries As String() = Directory.GetFiles(targetFolder)
 ' set file count
 ' array indexes from 0, so add 1 to find number of files in folder
 ' note that an empty array has ubound = -1 and so no additional handling is required
 Dim fileCount As Integer = UBound(fileEntries) + 1
 ' check for sequence start file
 ' declare working variables
 ' loop counter
 Dim i As Integer = 0
 ' check values
 Dim dateToCheck As Date
 Dim formattedDate As String
 ' decision control
 Dim noValidFile As Boolean = True
 ' set check values - change this to suit your circumstances
 dateToCheck = DateAdd(DateInterval.Day, 1, CDate(Dts.Variables("User::LastGoodRunDate").Value))
 ' format the check date to match the expected format in the filename
 ' uppercase to avoid case sensitivity issues
 formattedDate = UCase(Format(dateToCheck, "ddMMMyyyy"))
 ' loop through the array checking file dates
 For i = 0 To UBound(fileEntries)
     If InStr(UCase(CStr(fileEntries(i))), formattedDate) > 0 Then
         ' set your file path variable here
     End If
 Next i
more ▼

answered Aug 08, 2014 at 03:40 PM

avatar image

3k 4 7 11

(comments are locked)
10|1200 characters needed characters left

I usually set variables like this with a script task. You'll need to declare two variables, one to hold the template string and one to hold the actual string that you want to use:

User::MyDateStringTemplate = "/vaftp1/NewFile/data/<<-PLACEHOLDER->>(full).zip"

User::MyDateString = "Whatever, it doesn't matter"

Pass MyDateStringTemplate in as a read only variable, pass MyDateString as read/write.

Air code example:

 dim MyDateString as string
 dim MyDateStringTemplate as string = dts.variables("User::MyDateStringTemplate").value
 set MyDateString =  = replace(MyDateStringTemplate,"<<-PLACEHOLDER->>",format(now(),"dd-MM-yyyy hh:ss:mm")
 dts.variables("User::MyDateStringTemplate").value = MyDateString 

Edit - doesn't like angle brackets for some reason?

more ▼

answered Aug 08, 2014 at 03:23 PM

avatar image

3k 4 7 11

Oh dear, I've completely failed to answer the question again. Silly me. See next entry for some further bits that might help...

Aug 08, 2014 at 03:39 PM iainrobertson
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 08, 2014 at 02:24 PM

Seen: 1994 times

Last Updated: Aug 08, 2014 at 03:40 PM

Copyright 2017 Redgate Software. Privacy Policy