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

Posted by Sudheep.Grandhe under Sql Server on 2/23/2015 | Points: 10 | Views : 551 | Status : [Member] | Replies : 3
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.

3) for above package, If i choose XML parameter configuration, what changes need to do in ".dtsConfig" configuration file

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

Best,
Sudheep.



Responses

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

Up
0
Down
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

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

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

Up
0
Down
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.


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

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

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

Up
0
Down
For 3rd question,

Go throgh the Setting up a package configuration section of below link
http://www.sqlis.com/sqlis/post/Easy-Package-Configuration.aspx

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

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

Login to post response