Using File System Task

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]

(

[Source] varchar(50),

[FileName] varchar(20),

[Destination] varchar(50),

)

 

–Populating a table

INSERT INTO [FileInfoTable]

VALUES

(‘F:\S66\SOURCE’,’DG.txt’,’F:\D67′),

(‘F:\S66\SOURCE’,’HR.txt’,’F:\D67′),

(‘F:\S66\SOURCE’,’AB.txt’,’F:\D67′),

(‘F:\S66\SOURCE’,’DG.txt’,’F:\D68′),

(‘F:\S66\SOURCE’,’HR.txt’,’F:\D68′),

(‘F:\S66\SOURCE’,’AB.txt’,’F:\D68′),

(‘F:\S67\SOURCE’,’DG.txt’,’F:\D69′),

(‘F:\S67\SOURCE’,’HR.txt’,’F:\D69′),

(‘F:\S67\SOURCE’,’AB.txt’,’F:\D69′)

 

 

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:

Untitled1

‘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:

SourceName Destination
F:\S66\SOURCE\DG.txt F:\D67
F:\S66\SOURCE\HR.txt F:\D67
F:\S66\SOURCE\AB.txt F:\D67
F:\S66\SOURCE\DG.txt F:\D68
F:\S66\SOURCE\HR.txt F:\D68
F:\S66\SOURCE\AB.txt F:\D68
F:\S67\SOURCE\DG.txt F:\D69
F:\S67\SOURCE\HR.txt F:\D69
F:\S67\SOURCE\AB.txt F:\D69

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:

Untitled2

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!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s