SQL SERVER: SSIS - Rename and move files from source folder to destination folder

With SSIS, most of the time we need to process all the files from the folder. Once file has been processed, we need to move the file to archive folder, so we should know that file has been processed and we have the file in archive folder. Here, We are going to process the files and then will move the file from source folder to archive folder by appending date and time to the filename, so we can use it for future reference. SSIS will do both of these things, Rename a file and move a file, with File System Task with operation “Rename file”. Let’s review how it works: 1. Add Variables:To configure Source folder and Archive Folder by variable Name Scope Data Type Value FileName [Package Scope] string SourceFolder [Package Scope] string [Source Folder Path] (example :- c:\Source_Folder) SourcePath [Package Scope] string TargetFolder [Package Scope] string [Target Folder Path] (example :- c:\Archive_Folder) ArchivePath [Package Scope] string  2. Set variable’s properties expression:Go to Variable->properties and set EvaluateAsExpression and Expression, so we will have Source Path and Archive Path. Here, we are adding Current Date time to the filename by expression as mentioned below. Variable Name EvaluateAs Expression Expression SourcePath true @[User::SourceFolder]+"\\"+ @[User::FileName] ArchivePath true @[User::TargetFolder] +"\\"+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),FINDSTRING(REVERSE(@[User::FileName] ),".",1)+1,LEN(@[User::FileName])- FINDSTRING(REVERSE(@[User::FileName] ),".",1))) +"_"+ Right("0" +(DT_STR,4,1252) datepart("yyyy", getdate()),2)  + Right("0" +(DT_STR,2,1252) datepart("mm", getdate()) ,2) + Right("0" +(DT_STR,2,1252) datepart("dd", getdate()),2)+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) + Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2)+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),1,FINDSTRING(REVERSE(@[User::FileName] ),".",1))) 3. Add For each Loop Container and set properties :Now, lets loop thru the folder and process each file from the folder by “For Each loop”. Here, we can setup the folder by Expressions->Directory use Expression @[User::SourceFolder]. We should also specify which type of files we are going to process, like “txt”, “csv” etc..by specifying the same in “Files”. We are fetching the file name with extension so that option needs to be selected as displayed in the following screenshot.  We need to assign each file name to the variable, by Variable Mappings->set variable [User::FileName] and Index as 0 4. Add File System Task:Add “File System Task” inside “For each Loop Container” 5. Set properties for File System Task:This is the place where we need to setup the operation, which will do our job. A. Configure SourceVariable B. Configure DestinationVariable C. Select operation: “Rename File”, which will rename the file and move it to the Archive Folder as we have specified in variable.  6. Run package and check With SSIS, it is much simple to process multiple files as mentioned above. Reference: Tejas Shah (www.SQLYoga.com)

SQL SERVER: SSIS - Conditional Split Data Flow Transformation Task

Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups. 1. Create sample text file: This text file is piped delimited and last row  in text Total Row count EmployeeNumber|Employeename 242|Lorem ipsum 239|dolor sit 225|amet consectetur 242|adipisicing elit 142|seddo eiusmod 222|tempor incididunt 142|ut labore 143|dolore magna 144|Ut enim Total Row count|9 2. Create sample table: Create sample destination table in Test database 1: CREATE TABLE [dbo].[Employee]( 2: [EmployeeId] [int] IDENTITY(1,1) NOT NULL, 3: [EmployeeNumber] [int] NULL, 4: [EmployeeName] [varchar](50) NULL, 5: CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 6: ( 7: [EmployeeId] ASC 8: ) 9: ) 10: GO 3. Add Data Flow Task: Add data flow task in Package for transfer data source text file “Employee.txt” to sql server table “dbo.Employee”   4. Add new Flat File Connection: Right click on Connection Manager and add new Flat File connection   5. Set Flat file source properties: Flat file source properties set Connection manager Name, File Name (Source full filepath), column names in the first data row as a true 6. Add Flat file source: Add “Flat File Source” in “Data flow Task” and set properties Flat file connection manager and retain null values from the source as null values in the data flow as true and click button on preview and see in this file extra row include in text file 7. Add Conditional Split Transformation in Data Flow Task: Add Conditional Split Transformation in data flow task for split row 8. Set Condition Split Properties: Set properties Condition split where EmployeeNumber equal to “Total Row count” then this rows not use and other row use to process. 9. Add new OLE DB Connection: Right click on Connection Managers and add new OLE DB Connection   10. Set OLE DB Connection properties OLE DB Connection properties set Server Name, Connection to a database and click on ok button   11. Add OLE DB Destination: Add “OLE DB Destination” in “Data flow Task” and set properties OLE DB Connection Manager and Name of the table or the view 12. Set Input output selection : Set Input output selection between Condition spit and OLE DB Destination 13. Run package and Check : In this way we get split the information and use as per the requirement, Reference: Tejas Shah (www.SQLYoga.com)