Updated March 15, 2023
Introduction to SSIS File System Task
In SSIS, the File System Task is used to conduct various operations on files and folders (or Directories). For example, the System Files Task in SSIS uses the File Connection Manager to communicate to the Files and Folders. For example, this SSIS File System Task can be used to move directory content from one location to another.
SSIS file system task overviews
Reading data from flat files or extracting data from a traditional system into flat files are shared data integration cases. When loading data to and from flat files, knowing how to set up and then use the SQL Server Integration Services (SSIS) File System Task will help. SSIS is a software development platform that we need to know. The File System task operates on the file system’s files and directories. A package could, for instance, use the File System task to create, transfer, or delete directories and files. The File System task can also set attributes on files and directories. The File System job, for particular, can hide or render documents read-only. This operation on the sender and receiver requires using a file connection manager setting or a variable containing the critical path.
Configuring SSIS file system task
Below we have shown how to configure SSIS File System Task:
File System Task Features:
- Wildcard file operations for multiple files or folders.
- Folder actions like removing and creating a new folder.
- Bulk rename support (for example, giving % name per cent. % ext per cent. old as the new name would rename all chosen files to *.old).
- Create a missing target directory automatically.
There are four actions in the Professional File Transfer Task:
Send, delete, create and remove.
Send Files: This will copy all files and directories from a Source Path to a Destination Path.
Configure the Source Directory Options as follows:
-Manager of Connections
-Path to the directory
Set the Destination Directory Settings to the location wherever you want to relocate the source content(s):-
Manager of Connections
-Path to the directory
Drag and drop the SSIS File System Task into the Control Flow section to configure it in SSIS.
Name and description are the two most important qualities. Set the following properties for a source file while using the file system job to set attributes:
-hidden -Read-only -Archive -System
We create an option when we set each property to True or False. The source is updated to match the File System Task’s settings. No changes are made to the source file if the source file Properties match the values in the File System Task.
SSIS’s File System Task provides ten different file and folder functions. We could set multiple columns in the editor to accomplish various tasks. The operation attribute specifies what the task does when it executes. All properties are accessible to work on depending on what we choose from the menu. Watch the SSIS snapshot below for the possible options.
Copy Directory: This command replicates a folder from one location to another.
Copy File: This command duplicates a document from one area to another.
Create Directory: We can create a directory at the specified location using this option.
Delete Directory: This command deletes the given folder from the provided location.
Delete Directory Material: This command removes all content from a chosen folder.
Delete File: This command deletes the given file from the provided location.
Move Directory: This command moves a directory from one location to another.
Move File: This command transfers a file from one location to another.
Rename File: This command moves a file from one location to another while also renaming it.
Set Attributes: This option allows us to change the attributes of files and folders. This option consists of a hidden, Archive.
File System Task provides several features, but it also has some drawbacks that we must be aware of. The File system tasks can access only a single file or directory. As a result, it can’t even handle multiple files or directories.
SSIS file system task Connection
The Source and Destination Connection values are no longer visible, but the Source and Destination Variable properties are. For simplicity, we won’t use SSIS Properties to specify this post’s source and destination paths. Set the False values for the IsSourcePathVariable and IsDestinationPathVariable attributes. Click “New connection…>” in the Source Connection property dropdown, as seen here:
Setting a new connection for the File System Task
Connection Manager Editor
Displays a Usage Type of file system
Then, tap the Destination Connection property inside the SSIS File System Task Editor. Next, choose “New connection…>” from the selection, just as we did with the Source Connection property. Next, pick the “Existing folder” Usage type when the File Connection Manager Editor appears, as shown.
SSIS file system task examples
Different examples are mentioned below:
Step-1
For our example, create a Directory and add a file system task and open it to add a source connection.
Step-2
Created a folder
Step-3
Make a directory called Current Date. Using variables. Next, add a task to the file system. For example, present Date is a new variable with the type String. Following this, visit the Expression Builder page.
SUBSTRING( (DT_STR,52, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 12)
This code will create a new directory with the current date as the name.
Add another variable; by concatenating the two variables; we get a new one that will be used in the file connection manager for the file system task: @[User:DirectoryPath]+ @[User::TodaysDate]
- make a variable
- To open the Expression Builder, select the dots.
3.To acquire the final variable, join these two variables @[User::DirectoryPath]+ @[User::TodaysDate].
Finally, execute the task.
Conclusion
The File System Job is a beautiful and extensively used task in the SSIS control flow that we explored in this post. The majority of SSIS developers choose to deal with file systems for these activities. Then we reviewed the tasks and components introduced to SQL Server Integration Services (SSIS). Next, we reviewed how to use the SSIS connection manager and file system job in great detail. Finally, we tried copying data from a local system file to a different location.
Recommended Articles
This is a guide to SSIS File System Task. Here we discuss how to use the SSIS connection manager and file system job in great detail. You may also look at the following articles to learn more –
