
Hi,
With your previous reply input, i got the required output with below process:
Create one Object variable User::DepartmentNamesObject and two String variable User::FileName,User::departmentName
• Execute SQL Task
Connect: SQL Server Connection
Result Set: Full Result Set
SQL Statement:
SELECT DISTINCT [DeptName] FROM [SSISExercise].[dbo].[tblEmpAttendance]
Go to Result Set Pane, set the Result set name as “0” and the mapping variable name as User::DepartmentNamesObject
• 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::DepartmentNamesObject
b. Enumeration Mode: Rows in the first table
3. Go to Variable Mappings Pane, set variable as User::departmentName 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::departmentName 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"
• Go to properties of variable User::FileName and set expression as follows:
(DT_STR,2,1252) DAY(GETDATE())+"-"+ (DT_STR,3,1252) MONTH(GETUTCDATE() )+"-"+(DT_STR,4,1252) YEAR( GETDATE() ) +"_"+@[User::Department_name] +"_Attendance"
Mark as Answer if its helpful to you
---
Srihari
Sriharim, if this helps please login to Mark As Answer. | Alert Moderator