Sometimes, we need to copy files from one drive to another. One approach is you can perform a manual copy-paste. Now since it is not automated and in future you may need to do same task, it will kill your time. Other approach is to use a File System Task and develop a small ETL package to perform File Transfer.
Let’s proceed with a simple example. Create\ Populate a table in SQL Server using following script:
|–Creating a table
CREATE TABLE [FileInfoTable]
–Populating a table
INSERT INTO [FileInfoTable]
Create 2 folders in the F drive S66 and S77 and create some text files say DG.txt, HR.txt, AB.txt. This is just an example; you can create folders in any drive and have different files in it. Be sure to populate the table with the proper file paths.
Similarly create destinations where you want to dump these files. In my case, I have created 3 folders D67, D68, D69 under F: drive
Building SSIS package:
Below is the screenshot of SSIS package:
‘GetFileInfoTable’ will fetch the records from table created previously, store it in an Object and provide the same to For Each Loop Container via Object in the format below:
One by one these records will be fetched by For Each Loop (stored in 2 SSIS variables SourceName and DestinationName) and be passed to File System Task.
Note: For passing an Object and configuring For Each Loop container please see https://ssisblog.wordpress.com/2013/03/06/working-with-loops-in-ssis/
Double click the FileSystemTask and set the properties as:
Run the package and check the destination. The files will be copied to the destination.
Leave a comment if it is helping you in any aspect.
Cheers! Happy Coding!!