How to create flat files dynamically in SSIS [Resolved]

Posted by Sriharim under Sql Server on 2/23/2015 | Points: 10 | Views : 3083 | Status : [Member] | Replies : 7
Hi Team,
I have attended interview and i don't know answers for below questions

I have a employee attendance table (single table which has all departments employee attendance) in sql server database. I want to move this employee attendance table to a new flat files, for each department 1 flat need to be created. New flat files name should be "date of package execution_department-name_attendance" . suppose, if i have 3 departments, 3 flat file should be created everyday 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: 3 flat file name is like 22-02-2015_sales_attendance.txt , 22-02-2015_order_attendance.txt , 22-02-2015_production_attendance.txt

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

---
Srihari



Responses

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

Up
0
Down

Resolved
•       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

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

Posted by: Sriharim on: 2/24/2015 [Member] Starter | Points: 25

Up
0
Down

Resolved
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"


---
Srihari

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

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

Up
0
Down

Resolved
hi Sriharim,

No need to do lot of changes to get the data for flat files.... find the below underlined changes so that you can generate flatfiles with data

 Create one Object variable User::FileNamesObject and two   String variables User::FileName, User::DeptName  
• Execute SQL Task
Connect: SQL Server Connection
Result Set: Full Result Set
SQL Statement:
SELECT DISTINCT CONVERT(varchar(50), GETDATE(), 105)+'_'+[DeptName]+'_Attendance' as FileName, [DeptName]
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” ; set variable as User::DeptName and Index as "1"

• 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::DeptName 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

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

Posted by: Sriharim on: 2/24/2015 [Member] Starter | Points: 25

Up
0
Down
Hi,
Thank u for your reply. as per your reply, i got flat file name as required, but flat files are empty, because


1.User::FileName variable will have the content of date+departmentname+Attendance. Example:24-02-2014_sales_attendance,24-02-2014_production_attendance

2.In tblEmpAttendance table, DeptName column will have only department name. example: sales,prouction (but not 24-02-2014_sales_attendance)

so, in the satement SELECT [EMPNAME] ,[SALRY] FROM [SSISExercise].[dbo].[tblEmpAttendance] WHERE DeptName = ?
If map ? with variable User::FileName , no matching records will found. and flat files have empty data.


so, ? is need to map with another variable which will have only department name, then only i will get matched record with department name and those records will damped into new flat files.

how can i have another variable (where in each loop i should get only 1 one department name )

please help me

---
Srihari

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

Posted by: Sriharim on: 4/9/2015 [Member] Starter | Points: 25

Up
0
Down
Hi ,
Please help to resolve another question and please click below link for question

http://www.dotnetfunda.com/forums/show/19506/how-to-remove-empty-columns-from-excel-import-to-sql-server-in-ssis

---
Srihari

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

Posted by: Sriharim on: 4/24/2015 [Member] Starter | Points: 25

Up
0
Down
Hi
Please help me to solve below query

1. Table will log the websites traffic, table has website_name,page name,IP address, visited date columns. Write a query to get website names visited in last 30 days,total number of visit for each website,total number of unique page view, total number of unique visitors (using IP address).

2. Employee table has Employee ID,Employee name,Joining Salary columns. Salary_hike table columns are Employee ID,Salary Hike percentage,Year .
Write a query to get total salary paid to employee during 2000 to 2015 year.

Please help me

---
Srihari

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

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
1.

select websiteName, count(visit_date), count(distinct page_name), count( distinct IP_address)
from website traffic
where visit_date >= dateadd( dd, -30, getdate())
group by websiteName


2.

select employeeID, SUM( JoiningSalary*salaryHikePercantage)
from employee e
join emploSalary_Hike eh on e.employeeID= eh.employeeID
where year between 2000 and 2015

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

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

Login to post response