How to create flat files dynamically in SSIS [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

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

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

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

Mark as Answer if its helpful to you
---
Srihari

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

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

Mark as Answer if its helpful to you
---
Srihari

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

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

Mark as Answer if its helpful to you
---
Srihari

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

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