I have attended interview and i don't know answers for below questions

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 

•	Create one Object variable User::FileNamesObject and one String variable User::FileName
•	Execute SQL Task
           Connect: SQL Server Connection
           Result Set: Full Result Set
           SQL Statement: 
             SELECT  DISTINCT CONVERT(varchar(50), GETDATE(), 105)+'_'+[DeptName]+'_Attendance'
  FROM [SSISExercise].[dbo].[tblEmpAttendance]
           Go to Result Set Pane, set the Result set name as “0” and the mapping variable name as  User::FileNamesObject

•	Foreach Loop Container
     1.	Go to Collection pane, select enumerator as “Foreach ADO Enumerator”
     2.	Set enumerator configuration as follows:
        a.	ADO Object Source variable : User::FileNamesObject
        b.	Enumeration Mode: Rows in the first table
     3.	Go to Variable Mappings Pane,  set variable as User::FileName and Index as “0”

•	Drag Data Flow Task onto Foreach Loop container
        o	OLE DB Source:
          ?	Under connection manager, use SQL command text as below,
                SELECT [EMPNAME]  ,[SALRY] FROM [SSISExercise].[dbo].[tblEmpAttendance] WHERE DeptName = ?
          ?	Click on Parameters button on right pane
•	Give User::FileName as Variable for parameter named as “Parameter0”
        o	Flat File Destination
          ?	Create flat file connection manager by having connection to one sample test file
          ?	Then set the properties of Flat File connect Manager as follows:
"D:\\Sample Folder\\SSIS Excercise\\Generate Dynamic Flat Files\\"+ @[User::FileName]+".txt"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

1. Two variables:

    User:: DestinationSharedPath: \\\\anotherServer\\FileShare\\XXXXXX\\

    User:: SourceFilePath: "D:\\Sample Flat Files\\SSIS Excercise\\Generate Dynamic Flat Files\\TestFile.txt"



2. Create Destination File Connection for destination file path ( example: \\anotherServer\FileShare\XXXXXX\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
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif