Hi All, We get 5 flat files daily to a shared location \\ODCDEV01\LOAD\. The sample file is \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER. The second file would be \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER.Using SSIS 2008 I load the data from these flat files in to OLEDB destination. Inside the For each loop I am using data flow task to load the data from source to destination. after the data flow task I am trying to use file system task to move the flat files in to archive folder. Every day I get the previous date's file. My question is how to declare the variable to dynamically capture the date and store the file name. Archive folder is constant though. So I have declared the global variable and it's working fine. Please suggest me if there are any other approaches. Please let me know if you have any questions. Thanks for your time and any help is appreciated. Thanks, Naveen.

Hi Naveen I hope I understood your question properly. To get the current date, declare a variable as datetime. Select the variable and in the properties window, set EvaluateAsExpression = True. Set the Expression = GETDATE(). That will give you the current date/time. The filename can be inserted into a variable by the For Each Loop container. Edit the for each Loop container, go to the Collection tab (I assume you have selected a Foreach file enumerator). you will see a radio group called "Retrieve file name". Select the option that best suits you here. Normally it will just be "name" or "name and extension". Once you've done that, go to the Variable Mappings tab. Here you select the variable that you want to hold your file name with Index 0. HTHCraig Bryden - Please mark correct answers

can you be more specific. are you trying to get the date from the file i mean "2011-03-30-22-00-01"

Hi Craig, I'm new to SSIS, and your post seems like the closest answer to a similar problem I am having. I have to create a package to import a file into SQL and then do some transformations and splits to the file. I am totaly stuck on setting up this variable for the file name. For instance I will have a file thats gets sent to me once a month. The file name will look something like this Client1_04012011.txt the next month will be similar the only thing that changes is the date Client1_05042011.txt I need help with setting up the variable for the filename and then how do I use that to set up the import process?Sorry if this is something really basic, but like I said I'm new to SSIS and I am really having a hard time finding a resource to walk me throught the proper process. If you can point me to a resource that will have some step by step exmaples on how to set up the variable for the file name and then how to utilize that variable in SSIS to begin the import process I would be greatly appreciative. Thanks in advance for any advice you an provide. Luis

see the following link to loop trhough the files http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx if you want your file to be dynamic use the following steps: highlight the flat file connection manager source and in the properties window use expressions. property = conenctionstring expression = filepath+filename this way your can load whichever file comes in.

Hi Naveen, You said "My question is how to declare the variable to dynamically capture the date and store the file name. Archive folder is constant though. " so what I understood is you need to keep the folder constant and create sub folders for each day to hold that days' files, correct? what I do when I archive files: folder: 2011 - sub folder: 4-2-2011 and inside that sub folder is the files. Rafael Salas , wrote an article about moving and renaming in one step, you can use this logic. http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html Hope that works for you, please post your outcome. Thanks JasonMCITP - BI 2008 http://asqlb.blogspot.com/

Hi All, Thanks a lot for your responses. I will be more specific If suppose I have 2 flatfiles: \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER Archive folder is \\ODCDEV01\LOAD\Archive I get these flatfiles daily..monday through friday. On Monday I get previous friday's file. On Tuesday I get Monday's file to process.The date on filename i.e (2011-03-30-22-00-01) gets this way.( Night batch runs and the max load date is loaded in a sql table and that date is included in the filename by the application team..) I am creating two packages. One for loading the below flatfile \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER second package for loading below flat file \\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER Package logic I use is (same logic for two packages) 1.implementing business logic to select the row inserted by the application team to get the load date. 2. using sequence container a. In that I am using For Each loop . I am trying to create 2 variables. One to hold the archive folder that I could do (\\ODCDEV01\LOAD\Archive). Secong variable is to hold the "\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER". As the date in this path changes dynamically I have to capture this and assign to a variable. I am unable to write the expression and use this variable in the for each loop container. I am not sure what datatype I have to give while declaring this variable as it has date and string data in the filename. Inside the foreach loop, I am giving dataflow task to load data from flatfile to OLEDB destination. Here again in the flatfile connection string , I dont know how to write the expression. After dataflow task I am using File System Task to move flatfile to archive folder. Here again I am unable to give the source connection. Destination connection I could give in the expression. So my question is how to write the expression to declare the source variable? Please let me know if you have any questions? Thanks a lot for your time and help. Thanks, Naveen.

I'm not sure if I understand what you're trying to do... But Just create a "STRING" Variable and store the file name in it. You can use SUBSTRING to capture the date Substring(@[User::VAR],5,10) <---example showing variable called "VAR"MCITP - BI 2008 http://asqlb.blogspot.com/

Naveen Did the last post from Jason answer your question?Craig Bryden - Please mark correct answers

Personally I use a script task to do this as you have a lot more flexibility - on successful completion of the package I run this: It is a script task held within a For Each Loop using the "For Each File Enumerator" type. The file name is assigned to a variable "ArchiveFileName" 2 further variables are used: ArchiveSourceFilePath = folder path of initial file location ArchiveDestFilePath = folder path of archive location Public Sub Main() '************************************************************************************************* '**** Code to move files from landing zone to archive area - Geoff Barraclough **** '**** Archive area is always a subfolder of landing zone and called _archive **** '************************************************************************************************* Dim SourceFile As String Dim DestFile As String Dim DestFileZip As String 'Set result to success in the 1st instance. Only change on failure Dts.TaskResult = ScriptResults.Success Try 'Assign variables as the source / destination for the file move SourceFile = Dts.Variables("User::ArchiveSourceFilePath").Value.ToString + Dts.Variables("User::ArchiveFileName").Value.ToString DestFile = Dts.Variables("User::ArchiveDestFilePath").Value.ToString + Dts.Variables("User::ArchiveFileName").Value.ToString 'check if the file already exists - shouldn't do but it is possible if re-run is occurring If File.Exists(DestFile) Then 'If file exists in destination folder, delete it as we want the latest version to be in there 'and move file method will not overwrite existing files File.Delete(DestFile) End If 'check if zipped version of the file exists as well 'find the "." in the file name and strip off from that point - replace with ".gz" which is the 'extension used in the compression process 'Requires a -1 as instr returns the position of the "." and we want the characters up to but not 'including the "." DestFileZip = Left(DestFile, InStr(DestFile, ".") - 1) & ".gz" If File.Exists(DestFileZip) Then 'If zipped file exists in destination folder, delete it as we want the latest version to be in there 'and creation of new zip file with the same name as an existing one will cause errors File.Delete(DestFile) End If 'physically move the file from the landing zone to archive File.Move(SourceFile, DestFile) 'Compress the file Compress(DestFile) 'Delete the non compressed file File.Delete(DestFile) Catch ex As Exception Dts.TaskResult = ScriptResults.Failure End Try End Sub '***************************************************************************************************** '**** USES GZip COMPRESSION TO COMPRESS FILES - REQUIRES WINZIP / 7-ZIP TO OPEN BUT DOES **** '**** NOT REQUIRE 3RD PARTY SOFTWARE ON THE SERVER - ONLY THE CLIENT **** '***************************************************************************************************** Public Sub Compress(ByVal FilePath As String) Dim UncompressedData As Byte() = System.IO.File.ReadAllBytes(FilePath) Dim CompressedData As New MemoryStream() Dim GZipper As New GZipStream(CompressedData, CompressionMode.Compress, True) GZipper.Write(UncompressedData, 0, UncompressedData.Length) GZipper.Dispose() System.IO.File.WriteAllBytes(Left(FilePath, InStr(FilePath, ".") - 1) + ".gz", CompressedData.ToArray) CompressedData.Dispose() End Sub Rgds Geoff