How to change server name and create flat files dynamically in SSIS [Resolved]

Posted by Sriharim under Interview Questions on 2/22/2015 | Points: 10 | Views : 2296 | Status : [Member] | Replies : 1
Hi Team,
I have attended interview and i don't know answers for below questions

I have a table in sql server database. I want to move employee attendance table details (single table which has all departments employee attendance) to a new flat files, below are

1) dynamically new flat files name should be "date-of-execution-package_department-name_attendance" . suppose, if i have 3 departments, 3 flat file should be created for each department, in each file attendance of respective department data should be there. so everyday 3 flat files will be created after execution of package.
Example: 22-02-2015_sales_attendance.txt , 22-02-2015_order_attendance.txt , 22-02-2015_production_attendance.txt

2) How can Server names can be changed dynamically.
I want to create flat/excel files (as in above operation 1), then i want to move this flat/excel files from one server to different servers, dynamically.

Can please explain, how package can be created for this tasks ?

Mark as Answer if its helpful to you


Posted by: Bandi on: 2/24/2015 [Member] [MVP] Platinum | Points: 50


1. Two variables:
User:: DestinationSharedPath: \\\\RemoteServer\\FileShare\\Folder\\
User:: SourceFilePath: "D:\\Sample Flat Files\\SSIS Excercise\\Generate Dynamic Flat Files\\TestFile.txt"

2. Create Destination File Connection for destination file path ( example: \\RemoteServer\FileShare\XXXXX\TestFile.txt)

3. Take File System Task:
Destination Connection:
• IsDestinationPathVariable: True
• DestinationVaraible: User:: DestinationSharedPath
• OverwriteDestination: True
Operation: Copy File
Source Connection:
• IsSourcePathVariable: True
• SourceVaraible: User:: SourceFilePath

Mark This Response as Answer

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response