ssis interview questions - copy
TRANSCRIPT
-
8/9/2019 Ssis Interview Questions - Copy
1/82
1. In BIDS, the SSIS project contain 10 packages. But How to deploy only 5
packages in Destination machine eenthough !ani"est "ile contains all 10
packages a"ter the Build#
-Open the Manifest file in any editor like BIDS or Notepad, keep the required 5 packages,
remoe remaining 5 packages!
-Sae and "lose the Manifest file!-Dou#le click on Manifest file to deploy the required 5 package!
$. How would you pass a %arent aria&le alue to 'hild %ackage#
$e can pass the parent aria#le to child package #y using %ackage configuration&
%arent %ackage 'aria#le!
(!) "reate parent aria#le
*ile%ath - %arent - String - "&++file!t.t
(!( Drag an /0.ecute %ackage 1ask/ in "ontrol *lo2 and configure it to start child
package!
-
8/9/2019 Ssis Interview Questions - Copy
2/82
(!3 4o to "hild %ackage and create a aria#le 2ith same name as your parent package
aria#le!
(! 6dd %ackage configurations
(!5 70na#le %ackage "onfiguration7, choose "onfiguration type as 7%arent %ackage
'aria#le7 and type the name of the aria#le!
(!8 click /Ne.t/ #utton and select the /'alue/ property of the child package aria#le!
(!9 click /Ne.t/ and /O/ #uttons
(!: 1o test the package, I added sample Script 1ask 2ith a messageBo. to sho2 the
alue of the parent package!
(. !y Source )a&le data as "ollows*
-
8/9/2019 Ssis Interview Questions - Copy
3/82
+utput Should &e as "ollows*
How to Implement#
Designed SSIS package like&
1he script component code&
-
8/9/2019 Ssis Interview Questions - Copy
4/82
.!y Source )a&le Data as "ollows*
-
8/9/2019 Ssis Interview Questions - Copy
5/82
+utput should &e as "ollows*
How to Implement#
-
8/9/2019 Ssis Interview Questions - Copy
6/82
5. )he data in the -lat -ile as "ollows*
7)3(7,7amakrishna7,7 7 ,7;ydera#ad7
7)3(7,7adhika7,7)97,7'angara7
How to remoe dou&le uotes "rom the "ile to process the data.In the *lat *ile "onnection Manager 0ditor, 0nter dou#le quotes in 1e.t
-
8/9/2019 Ssis Interview Questions - Copy
7/82
2. S
-
8/9/2019 Ssis Interview Questions - Copy
8/82
. hat are the di""erent types o" )rans"ormations you hae worked
64404610 -1he 6ggregate transformation applies aggregate functions to column alues
and copies the results to the transformation output! Besides aggregate functions, the
transformation proides the 4O?% B@ clause, 2hich you can use to specify groups to
aggregate across!
1he 6ggregate 1ransformation supports follo2ing operations&
4roup By, Sum, 6erage, "ount, "ount Distinct, Minimum, Ma.imum
6?DI1 - 6dds %ackage and 1ask leel Metadata - such as Machine Name, 0.ecution
Instance, %ackage Name, %ackage ID, etc!!
";66"10 M6% - $hen it comes to string formatting in SSIS, "haracter Map
transformation is ery useful, used to conert data lo2er case, upper case!"ONDI1ION6= S%=I1 A used to split the input source data #ased on condition!
"O%@ "O=?MN - 6dd a copy of column to the output, 2e can later transform the copy
keeping the original for auditing!
D616 "ON'0SION - "onerts columns data types from one to another type! It stands
for 0.plicit "olumn "onersion!
D616 MININ4
-
8/9/2019 Ssis Interview Questions - Copy
9/82
IM%O1 "O=?MN - eads image specific column from data#ase onto a flat file!
=OO?% - %erforms the lookup searchingC of a gien reference o#Gect set against a data
source! It is used to find e.act matches only!
M040 - Merges t2o sorted data sets of same column structure into a single output!
M040 HOIN - Merges t2o sorted data sets into a single dataset using a Goin!
M?=1I "6S1 - is used to createdistri#ute e.act copies of the source dataset to one ormore destination datasets!
O$ "O?N1 - Stores the resulting ro2 count from the data flo2 transformation into a
aria#le!
O$ S6M%=IN4 - "aptures sample data #y using a ro2 count of the total ro2s in
dataflo2 specified #y ro2s or percentage!
?NION 6== - Merge multiple data sets into a single dataset!
%I'O1 A ?sed for NormaliFation of data sources to reduce analomolies #y conerting
ro2s into columns
?N%I'O1 A ?sed for demoraliFing the data structure #y conerts columns into ro2s
incase of #uilding Data $arehouses!
2. hat are the di""erent types o" )ransaction +ptions
equired& If a transaction already e.ists at the upper leel, the current e.ecuta#le 2ill
Goin the transaction! If No transaction at the upper leel, a ne2 transaction is created
automatically!
Supported&In any e.ecuta#le, if there is a transaction at upper leel, the e.ecuta#le Goin
the transaction else do not create a ne2 transaction!
Not Supported&1he e.ecuta#le of the package do not honour any transaction ie do not
Goin other transaction nor creates ne2 transaction!
3. 4plain a&out 'heckpoints with properties
"heckpoint is used to restart the package e.ecution from the point of failure rather than
from initial start!Set the follo2ing %roperties&
"heckpoint*ileName& Specifies the name of the checkpoint file!
"heckpoint?sage& Neer, If0.ists, 6l2ays
Sae"heckpoints& indicates 2hether the package needs to sae checkpoints! 1his
property must #e set to 1rue to restart a package from a point of failure!
*ail%ackageOn*ailure& property needs to #e set to 1rue for ena#ling the task in the
checkpoint!
"heckpoint mechanism uses a 1e.t *ile to mark the point of package failure!
1hese checkpoint files are automatically created at a gien location upon the package
failure and automatically deleted once the package ends up 2ith success!
-
8/9/2019 Ssis Interview Questions - Copy
10/82
10. How to eecute SSIS %ackage "rom Stored %rocedure.
using .p>cmdshell command
-
8/9/2019 Ssis Interview Questions - Copy
11/82
11. How to ena&le 6p7'mdShell in Sl Serer#$e can ena#le through either 1-Sql or Sconfigure/sho2 adanced options/, )
4O-- 1o update the currently configured alue for adanced options!0"ON*I4?0
4O
-- 1o ena#le the feature!
00"sp>configure/.p>cmdshell/, )
4O
-- 1o update the currently configured alue for this feature!
0"ON*I4?0
4O
1$. %ackage con"iguration# Di""erent types o" 'on"iguration -iles
1he package can #e transferred across arious enironments like deelopment and unit
testing, system testing, ?61 and production! Most packages 2ill hae enironment
specific aria#les like connection string to a data#ase or path to a flat file, or user
defined aria#les etc! that 2ould #e impacted 2hile moing the package across
enironments as part of deployment process! ;ence, it is mandatory to change these
enironment dependent aria#les 2hen the package is transferred across enironments!
%ackage configurations help in managing such changes 2ithout actually opening and
editing the SSIS package in Business Intelligence Deelopment Studio BIDSC!
6fter deploying the package to a different machine using S
-
8/9/2019 Ssis Interview Questions - Copy
12/82
1(. 8ogging. Di""erent types o" 8ogging "iles
=ogging is used to log the information during the e.ecution of package!
6 log proider can #e a te.t file, the S
-
8/9/2019 Ssis Interview Questions - Copy
13/82
Select /=oad to sql 1a#le/ Data flo2 1ask! Naigate to /0en ;andlers/ 1a#!
Drag and Drop /0.ecute Sql 1ask/! Open the 0.ecute Sql 1ask 0ditor and in %arameter
Mapping/ section, select the system aria#les as follo2s&
-
8/9/2019 Ssis Interview Questions - Copy
14/82
"reate a ta#le in Sql Serer Data#ase 2ith "olumns as& %ackageID, %ackageName,
1askID, 1askName, 0rror"ode, 0rrorDescription!
1he package 2ill #e failed during the e.ecution!
1he error information is inserted into 1a#le!
-
8/9/2019 Ssis Interview Questions - Copy
15/82
1. How to con"igure 4rror +utput in SSIS
$e hae 3 options to configure error output in ssis!
)! Ignore *ailure
(! edirect o2
3! *ail "omponent
-or 4ample*
I hae comma-separated alue in a flat file 2ith t2o columns code,NameC! "ode is an integer alue and name is a archar(JC data typeconfigured in the flat file connection manager! Some of the codes in theflat files are characters! So, flat file reader component 2ill fail reading thecharacter alue! But, I 2ant to redirect the error data to separate ta#le!
12. hat is Ignore -ailure option in SSIS#
In Ignore *ailure option, the error 2ill #e ignored and the data ro2 2ill #e directed to
continue on the ne.t transformation! =etKs say you hae some H?N data2rong type of
data C flo2ing from source, then using this option in SSIS 2e can 0DI0"1 the Gunk
data records to another transformation instead of *6I=IN4 the package! 1his helps toMO'0 only alid data to destination and H?N can #e captured into separate file!
13. Incremental 8oad in SSIS
?sing Slo2ly "hanging Dimension
?sing =ookup and "ache 1ransformation
$0. How to migrate Sl serer $005 %ackage to $002 ersion
)! In BIDS, #y right click on the 7SSIS %ackages7 folder of an SSIS proGect and
selecting 7?pgrade 6ll %ackages7!
(! unning 7ssisupgrade!e.e7 from the command line default physical location
"&+%rogram *iles+Microsoft S
-
8/9/2019 Ssis Interview Questions - Copy
16/82
package upgrade 2iFard!
$1. Di""erence &etween Synchronous and 9synchronous )rans"ormation
Synchronous 1* process the input ro2s and passes them onto the data flo2 one ro2 at
a time!
$hen the output #uffer of 1ransformation created a ne2 #uffer, then it is 6synchronous
transformation! Output #uffer or output ro2s are not sync 2ith input #uffer!$$. hat are :ow )rans"ormations, %artially Blocking )rans"ormation, -ully
Blocking )rans"ormation with eamples.
In o2 1ransformation, each alue is manipulated indiidually! In this transformation,
the #uffers can #e re-used for other purposes like follo2ing&
O=0DB Datasource, O=0DB Data Destinations
Other o2 transformation 2ithin the package, Other partially #locking transformations
2ithin the package!
e.amples of o2 1ransformations& "opy "olumn, 6udit, "haracter Map
%artially Blocking 1ransformation&
1hese can re-use the #uffer space allocated for aaila#le o2 transformation and get
ne2 #uffer space allocated e.clusiely for 1ransformation!
e.amples& Merge, "onditional Split, Multicast, =ookup, Import, 0.port "olumn
*ully Blocking 1ransformation&
It 2ill make use of their o2n resere #uffer and 2ill not share #uffer space from other
transformation or connection manager!
e.amples& Sort, 6ggregate, "ache 1ransformation
$(. Di""erence &etween -ileSystem and Sl serer Deployment
*ile System Deployment& $e can sae the package on a physical location on hard drie
or any shared folder 2ith this option, and 2e should proide a full qualified path to
stored package in the *ileSystem option!Sql Serer Deployment& SSIS packages 2ill #e stored in the sysssispackages ta#le of
MSDB Data#ase!
$. Di""erence &etween 8ookup and -u;;y 8ookup trans"ormation
=ookup 1ransformation finds the e.act match!
*uFFy =ookup transformation matches input ta#le 2ith reference ta#le! It finds the
closest match and indicates the quality of the match!
-
8/9/2019 Ssis Interview Questions - Copy
17/82
$5. Di""erence &etween -ull 'ache and %artial 'ache
%artial "ache& 1he lookup cache starts off empty at the #eginning of the data flo2! $hen
a ne2 ro2 comes in, the lookup transform checks its cache for the matching alues! If
no match is found, it queries the data#ase! If the match is found at the data#ase, the
alues are cached so they can #e used the ne.t time a matching ro2 comes in!
*ull "ache& 1he default cache mode for lookup is *ull cache! 1he data#ase is queried
once during the pre-e.ecute phase of the data flo2! 1he entire reference set is pulled
into memory! 1his approach uses most of the memory! "aching takes place #efore any
ro2s are read from the data flo2 source! =ookup operations 2ill #e ery fast during
e.ecution!
-
8/9/2019 Ssis Interview Questions - Copy
18/82
$/. 'ache )rans"ormation
"ache 1ransformation& to cache the data used in the =ookup transform!
$hen to use *ull cache mode&
$hen you/re accessing a large portion of your reference set
$hen you hae a small reference ta#le
$hen your data#ase is remote or under heay load, and you 2ant to reduce the num#erof queries sent to the serer
$hen to use %artial cache mode&
$hen you/re processing a small num#er of ro2s and it/s not 2orth the time to charge the
full cache!
$hen you hae a large reference ta#le!
$hen your data flo2 is adding ne2 ro2s to your reference ta#le!
$hen you 2ant to limit the siFe of your reference ta#le #y modifying query 2ith
parameters from the data flo2!
$. 4plain Slowly 'hanging Dimesnion
1he S"D concept is #asically a#out ho2 the data modifications are a#sor#ed and
maintained in a Dimension 1a#le!
1he ne2 modifiedC record and the old recordsC are identified using some kind of a flag
like say Is6ctie, IsDeleted etc! or using Start and 0nd Date fields to
indicate the alidity of the record!
)ypes*
1ype ) - update the columns in the dimension ro2 2ithout presering any change
history!
1ype ( - presere the change history in the dimension ta#le and create a ne2 ro2
2hen there are changes!
1ype 3 - some com#ination of 1ype ) and 1ype (, usually maintaining multiple instancesof a column in the dimension ro2L e!g! a current alue and one or more preious alues!
$2. Di""erent types o" -ile 4numerators
Foreach ADO&
1he 6DO 0numerator enumerates ro2s in a ta#le! *or e.ample, 2e can get the ro2s in
the 6DO records!1he aria#le must #e of O#Gect data type!
Foreach ADO.NET Schema Rowset&
1he 6DO!Net 0numerator enumerates the schema information! *or e.ample, 2e can get
the ta#le from the data#ase!
-
8/9/2019 Ssis Interview Questions - Copy
19/82
Foreach File&
1he *ile 0numerator enumerates files in a folder! *or e.ample, 2e can get all the files
2hich hae the !t.t e.tension in a 2indo2s folder and its su# folders!
*oreach *rom 'aria#le&
1he 'aria#le 0numerator enumerates o#Gects that specified aria#les contain! ;ere
enumerator o#Gects are nothing #ut an array or data ta#le!Foreach Item&
1he Item 0numerator enumerates the collections! *or e.ample, 2e can enumerate the
names of e.ecuta#les and 2orking directories that an 0.ecute %rocess task uses!
Foreach Nodelist&
1he Node =ist 0numerator enumerates the result of an %ath e.pression!
Foreach SMO&
1he SMO 0numerator enumerates S
-
8/9/2019 Ssis Interview Questions - Copy
20/82
languageC! for eery day of the 2eek!
Sequence
"ontainer
4roups tasks as 2ell as
containers into "ontrol *lo2s
that are su#sets of the package
"ontrol *lo2!
1his container group tasks and containers
that must succeed or fail as a unit! *or
e.ample, a package can group tasks that
delete and add ro2s in a data#ase ta#le,
and then commit or roll #ack all the tasks
2hen one fails!
($. hat are precedence constraints
6 task 2ill only e.ecute if the condition that is set #y the precedence
constraint preceding the task is met! By using these constraints,it 2ill choose different
e.ecution paths depending on the success or failure of other tasks!
Success A $orkflo2 2ill proceed 2hen the preceding container e.ecutes successfully!
Indicated in control flo2 #y a solid green line!
*ailure A $orkflo2 2ill proceed 2hen the preceding containerKs e.ecution results in afailure! Indicated in control flo2 #y a solid red line!
"ompletion A $orkflo2 2ill proceed 2hen the preceding containerKs e.ecution completes,
regardless of success or failure! Indicated in control flo2 #y a solid #lue line!
0.pression"onstraint 2ith =ogical 6ND A $orkflo2 2ill proceed 2hen specified
e.pression and constraints ealuate to true! Indicated in control flo2 #y a solid color line
along 2ith a small Rf.K icon ne.t to it! "olor of line depends on logical constraint chosen
e!g! successPgreen, completionP#lueC!
0.pression"onstraint 2ith =ogical O A $orkflo2 2ill proceed 2hen either the specified
e.pression or the logical constraint successfailurecompletionC ealuates to true!
Indicated in control flo2 #y a dotted color line along 2ith a small Rf.K icon ne.t to it!
"olor of line depends on logical constraint chosen e!g! successPgreen,
completionP#lueC!
((. %er"ormance +ptimi;ation in SSIS
)! 6oid 6synchronous 1ransformation Sort 1*C 2hereer possi#le! Sort 1* required all
the incoming ro2s to #e arriaed #efore start processing! Instead of using Sort 1*, 2e
get sorted ro2s from datasource using OD0 By clause!
(! %ulling ;igh 'olumes of Data
Drop all Non-"lustered Inde.es and "lustered Inde. if e.ists, then 1ransfer and load the
data into Destination 1a#le! "reate "lustered Inde. and Non-clustered inde.es!
3! 6oid S0=0"1
Data*lo2 1ask uses #uffer oriented architecture for data transfer and transformation!
$hen data transfer from Source to Destination, the data first comes into the #uffer,
required transformations are done in the #uffer itself and then 2ritten to Destination!
1he siFe of #uffer depends on the estimated ro2 siFe! 1he estimated ro2 siFe is equal to
the ma.imum siFe of all columns in the ro2! So the more columns in a ro2 means less
num#er of ro2s in a #uffer! ;ence select only those columns 2hich are required at the
destination!
0en if 2e need all the columns from source, 2e should use the column name specifically
in the S0=0"1 statement, other2ise it takes another round for the source to gather
meta-data a#out the columns 2hen u are using S0=0"1 !
-
8/9/2019 Ssis Interview Questions - Copy
21/82
! 0ffect of O=0DB Destination Settings
1here are couple of settings 2ith O=0DB destination 2hich can impact the performance
of data transfer as listed #elo2!
Data 6ccess Mode A 1his setting proides the /fast load/ option 2hich internally uses a
B?= INS01 statement for uploading data into the destination ta#le instead of a simple
INS01 statement for each single ro2C as in the case for other options! So unless youhae a reason for changing it, don/t change this default alue of fast load! If you select
the /fast load/ option, there are also a couple of other settings 2hich you can use as
discussed #elo2!
eep Identity A By default this setting is unchecked 2hich means the destination ta#le if
it has an identity columnC 2ill create identity alues on its o2n! If you check this setting,
the dataflo2 engine 2ill ensure that the source identity alues are presered and same
alue is inserted into the destination ta#le!
eep Nulls A 6gain #y default this setting is unchecked 2hich means default alue 2ill #e
inserted if the default constraint is defined on the target columnC during insert into the
destination ta#le if N?== alue is coming from the source for that particular column! If
you check this option then default constraint on the destination ta#le/s column 2ill #e
ignored and presered N?== of the source column 2ill #e inserted into the destination!
1a#le =ock A By default this setting is checked and the recommendation is to let it #e
checked unless the same ta#le is #eing used #y some other process at same time! It
specifies a ta#le lock 2ill #e acquired on the destination ta#le instead of acquiring
multiple ro2 leel locks, 2hich could turn into lock escalation pro#lems!
"heck "onstraints A 6gain #y default this setting is checked and recommendation is to
un-check it if you are sure that the incoming data is not going to iolate constraints of
the destination ta#le! 1his setting specifies that the dataflo2 pipeline engine 2ill alidatethe incoming data against the constraints of target ta#le! If you un-check this option it
2ill improe the performance of the data load!
5 - 0ffect of o2s %er Batch and Ma.imum Insert "ommit SiFe Settings&
o2s per #atch&
1he default alue for this setting is -) 2hich specifies all incoming ro2s 2ill #e treated as
a single #atch! @ou can change this default #ehaior and #reak all incoming ro2s into
multiple #atches! 1he allo2ed alue is only positie integer 2hich specifies the ma.imum
num#er of ro2s in a #atch!
Ma.imum insert commit siFe&
1he default alue for this setting is /()9:389/ largest alue for #yte integer typeC2hich specifies all incoming ro2s 2ill #e committed once on successful completion! @ou
can specify a positie alue for this setting to indicate that commit 2ill #e done for those
num#er of records! "hanging the default alue for this setting 2ill put oerhead on the
dataflo2 engine to commit seeral times! @es that is true, #ut at the same time it 2ill
release the pressure on the transaction log and tempd# to gro2 specifically during high
olume data transfers!
1he a#oe t2o settings are ery important to understand to improe the performance of
tempd# and the transaction log! *or e.ample if you leae /Ma. insert commit siFe/ to its
default, the transaction log and tempd# 2ill keep on gro2ing during the e.traction
process and if you are transferring a high olume of data the tempd# 2ill soon run out of
-
8/9/2019 Ssis Interview Questions - Copy
22/82
memory as a result of this your e.traction 2ill fail! So it is recommended to set these
alues to an optimum alue #ased on your enironment!
9 - DefaultBufferSiFe and DefaultBufferMa.o2s &
1he e.ecution tree creates #uffers for storing incoming ro2s and performing
transformations!
1he num#er of #uffer created is dependent on ho2 many ro2s fit into a #uffer and ho2
many ro2s fit into a #uffer dependent on fe2 other factors! 1he first consideration is the
estimated ro2 siFe, 2hich is the sum of the ma.imum siFes of all the columns from the
incoming records! 1he second consideration is the DefaultBufferMa.SiFe property of the
data flo2 task! 1his property specifies the default ma.imum siFe of a #uffer! 1he default
alue is )J MB and its upper and lo2er #oundaries are constrained #y t2o internal
properties of SSIS 2hich are Ma.BufferSiFe )JJMBC and MinBufferSiFe 8 BC! It
means the siFe of a #uffer can #e as small as 8 B and as large as )JJ MB! 1he third
factor is, DefaultBufferMa.o2s 2hich is again a property of data flo2 task 2hich
specifies the default num#er of ro2s in a #uffer! Its default alue is )JJJJ!
If the siFe e.ceeds the DefaultBufferMa.SiFe then it reduces the ro2s in the #uffer! *or
#etter #uffer performance you can do t2o things!
*irst you can remoe un2anted columns from the source and set data type in each
column appropriately, especially if your source is flat file! 1his 2ill ena#le you to
accommodate as many ro2s as possi#le in the #uffer!
Second, if your system has sufficient memory aaila#le, you can tune these properties to
hae a small num#er of large #uffers, 2hich could improe performance! Be2are if you
change the alues of these properties to a point 2here page spooling see Best %ractices
:C #egins, it adersely impacts performance! So #efore you set a alue for theseproperties, first thoroughly testing in your enironment and set the alues appropriately!
: - ;o2 Delay'alidation property can help you
SSIS uses t2o types of alidation!
*irst is package alidation early alidationC 2hich alidates the package and all its
components #efore starting the e.ecution of the package!
Second SSIS uses component alidation late alidationC, 2hich alidates the
components of the package once started!
=et/s consider a scenario 2here the first component of the package creates an o#Gect i!e!
a temporary ta#le, 2hich is #eing referenced #y the second component of the package!During package alidation, the first component has not yet e.ecuted, so no o#Gect has
#een created causing a package alidation failure 2hen alidating the second
component! SSIS 2ill thro2 a alidation e.ception and 2ill not start the package
e.ecution! So ho2 2ill you get this package running in this common scenarioT
1o help you in this scenario, eery component has a Delay'alidation defaultP*6=S0C
property! If you set it to 1?0, early alidation 2ill #e skipped and the component 2ill #e
alidated only at the component leel late alidationC 2hich is during package e.ecution
U! Better performance 2ith parallel e.ecution
)J! $hen to use eents logging and 2hen to aoid!
-
8/9/2019 Ssis Interview Questions - Copy
23/82
))! Monitoring the SSIS %erformance 2ith %erformance "ounters
=aunch %erformance Monitor&
)! Start -V 6ll %rograms -V 6dministratie 1ools -V %erformance
(! =oad the SSIS related "ounters
In the %erformance O#Gect, select S
-
8/9/2019 Ssis Interview Questions - Copy
24/82
)3! "heckpoint features helps in package restarting
(. ?pgrade D)S package to SSIS
)! In BIDS, from the %roGect Menu, select /Migrate D1S (JJJ %ackage/
(! In the %ackage Migration $iFard, choose the Source, Sql Serer (JJJ Serer Name,
Destination folder!
3! Select the =ist of packages that needs to #e upgraded to SSIS! Specifty the =og file for %ackage Migration!
(5. 4ents in SSIS
On0rror & uns 2hen a task or container reports an error!
On0.ecStatus"hanged & uns for all tasks and containers 2hen the e.ecution status
changes to In %rocess, Success, or *ailed!
OnInformation & uns 2hen SSIS outputs information messages during the alidation
and e.ecution of a task or container!
On%ost0.ecute & uns after a container or task successfully completes!
On%ost'alidate & 0.ecutes after a container or task has successfully #een alidated!
On%re0.ecute & uns Gust #efore a container or task is e.ecuted!
On%re'alidate& uns #efore the component is alidated #y the engine!
On%rogress & 0.ecuted 2hen a progress message is sent #y the SSIS engine, indicating
tangi#le adancement of the task or container!
On
-
8/9/2019 Ssis Interview Questions - Copy
25/82
(/. Di""erence &etween 'ontrol -low and Data -low
)!"ontrol flo2 consists of one or more tasks and containers that e.ecute 2hen the
package runs! $e use precedence constraints to connect the tasks and containers in a
package! SSIS proides three different types of control flo2 elements& "ontainers that
proide structures in packages, 1asks that proide functionality, and %recedence"onstraints that connect the e.ecuta#les, containers, and tasks into an ordered control
flo2!
(!"ontrol flo2 does not moe data from task to task!
3!1asks are run in series if connected 2ith precedence or in parallel!
)! 6 data flo2 consists of the sources and destinations that e.tract and load data, the
transformations that modify and e.tend data, and the paths that link sources,
transformations, and destinations! 1he Data *lo2 task is the e.ecuta#le 2ithin the SSIS
package that creates, orders, and runs the data flo2! Data Sources, 1ransformations,
and Data Destinations are the three important categories in the Data *lo2!
(! Data flo2s moe data, #ut there are also tasks in the control flo2, as such, their
success or *ailure effects ho2 your control flo2 operates
3! Data is moed and manipulated through transformations!
! Data is passed #et2een each component in the data flo2!
(. Di""erent ways to eecute SSIS package
1. !sing the Execute Package Utility (DTEXECUI.EXE)graphical interface one can
e.ecute an SSIS package that is stored in a *ile System, S
-
8/9/2019 Ssis Interview Questions - Copy
26/82
1he first step to setting up the pro.y is to create a credential alternatiely you could use
an e.isting credentialC! Naigate to Security then "redentials in SSMS O#Gect 0.plorer
and right click to create a ne2 credential
Naigate to S
-
8/9/2019 Ssis Interview Questions - Copy
27/82
0. How to deploy a package
ight click on the Solution in Solution 0.plorer and choose properties in theMenu!
-
8/9/2019 Ssis Interview Questions - Copy
28/82
When the build /rebuild is successful, navigate to the directory is referred
in DeploymentOutputPath
Deploying the Package:
Double click the Manifest File to start the deployment. The Package Installation wizard
begins and Deploy SSIS Packages step is the first screen that is presented.
-
8/9/2019 Ssis Interview Questions - Copy
29/82
1his screen lets you select 2here shall the packages #e deployed, as mentioned in the
Dialog Bo., deploying in S
-
8/9/2019 Ssis Interview Questions - Copy
30/82
1. hat is the use o" %ercentage Sampling trans"ormation in SSIS#
-
8/9/2019 Ssis Interview Questions - Copy
31/82
%ercentage Sampling transformation is generally used for data mining! 1his
transformation #uilds a random sample of set of output ro2s #y choosing specified
percentage of input ro2s! *or e.ample if the input has )JJJ ro2s and if I specify )J
as percentage sample then the transformation returns )JW of the 6NDOM records from
the input data!
$. hat is the use o" )erm 4traction trans"ormation in SSIS#1erm 0.traction transformation is used to e.tract nouns or noun phrases or #oth noun
and noun phrases only from 0nglish te.t! It e.tracts terms from te.t in a transformation
input column and then 2rites the terms to a transformation output column! It can #e
also used to find out the content of a dataset!
(. hat is Data Ciewer and what are the di""erent types o" Data Ciewers in
SSIS#
6 Data 'ie2er allo2s ie2ing data at a point of time at runtime!
1he different types of data ie2ers are&
)! 4rid
(! ;istogram
3! Scatter %lot
! "olumn "hart
. Di""erence &etween !erge and ?nion9ll )rans"ormations
1he ?nion 6ll transformation com#ines multiple inputs into one output! 1he
transformation inputs are added to the transformation output one after the otherL no
reordering of ro2s occurs!
Merge 1ransformations com#ines t2o sorted data sets of same column structure into a
single output!1he ro2s from each dataset are inserted into the output #ased on alues in
their key columns!
1he Merge transformation is similar to the ?nion 6ll transformations! ?se the ?nion 6ll
transformation instead of the Merge transformation in the follo2ing situations&
-1he Source Input ro2s are not need to #e sorted!
-1he com#ined output does not need to #e sorted!
-
8/9/2019 Ssis Interview Questions - Copy
32/82
-2hen 2e hae more than ( source inputs!
5. !ulticast, 'onditional Split, Bulk Insert )asks
Multicast 1ransformation is used to e.tract output from single source and places onto
multiple destinations!
"onditional Split transformation is used for splitting the input data #ased on a specific
condition! 1he condition is ealuated in 'B Script!
Multicast 1ransformation generates e.act copies of the source data, it means each
recipient 2ill hae same num#er of records as the source 2hereas the "onditional Split
1ransformation diides the source data #ased on the defined conditions and if no ro2s
match 2ith this defined conditions those ro2s are put on default output!
Bulk Insert 1ask is used to copy the large olumn of data from te.t file to sql serer
destination!
/. 4plain 9udit )rans"ormation #
It allo2s you to add auditing information! 6uditing options that you can add to
transformed data through this transformation are &
)! 0.ecution of Instance 4?ID & ID of e.ecution instance of the package
(! %ackageID & ID of the package
3! %ackageName & Name of the %ackage
! 'ersionID & 4?ID ersion of the package
5! 0.ecution Start1ime
8! MachineName
9! ?serName
:! 1askName
U! 1askID & unique identifier type of the data flo2 task that contains audit
transformation!. what are the possi&le locations to sae SSIS package#
)!*ile System& $e can sae the package on a physical location on hard drie or any
shared folder 2ith this option, and 2e should proide a full qualified path to stored
package in the *ileSystem option!
(! Sql Serer& SSIS packages 2ill #e stored in the MSDB data#ase, in the
sysssispackages ta#le!
SSIS %ackage Store is nothing #ut com#ination of S
-
8/9/2019 Ssis Interview Questions - Copy
33/82
)! DonotSaeSensitie& any sensitie information is simply not 2ritten out tothe package M= file 2hen you sae the package!(! 0ncryptSensitie$ith?serey& encrypts sensitie information #ased on thecredentials of the user 2ho created the package! It is the default alue forthe %rotection=eel property!3! 0ncryptSensitie$ith%ass2ord& requires to specify a pass2ord in thepackage, and this pass2ord 2ill #e used to encrypt and decrypt the sensitie
information in the package!! 0ncrypt6ll$ith%ass2ord& allo2s to encrypt the entire contents of the SSISpackage 2ith your specified pass2ord!5! 0ncrypt6ll$ith?serey& allo2s to encrypt the entire contents of the SSISpackage #y using the user key!8! Serer Storage& allo2s the package to retain all sensitie information2hen you are saing the package to S
-
8/9/2019 Ssis Interview Questions - Copy
34/82
6t run time, the *1% task connects to a serer #y using an *1% connection manager! 1he
*1% connection manager includes the serer settings, the credentials for accessing the
*1% serer, and options such as the time-out and the num#er of retries for connecting to
the serer!
1he *1% connection manager supports only anonymous authentication and #asic
authentication! It does not support $indo2s 6uthentication!
%redefined *1% Operations&
Send *iles, eceie *ile,
"reate =ocal directory, emoe =ocal Directory,
"reate emote Directory, emoe emote Directory
Delete =ocal *iles, Delete emote *ile
"ustomer =og 0ntries aaila#le on *1% 1ask&
*1%"onnecting1oSerer
*1%Operation
51.
-
8/9/2019 Ssis Interview Questions - Copy
35/82
1O0N& 1his function allo2s you to return a su#string #y using delimiters to separate a
string into tokens and then specifying 2hich occurrence to
return& 1O0Ncharacter>e.pression, delimiter>string, occurrenceC
1O0N"O?N1& 1his function uses delimiters to separate a string into tokens and then
returns the count of tokens found 2ithin the string& 1O0N"O?N1character>e.pression,delimiter>stringC
8! 0asy "olumn emapping in Data *lo2 Mapping Data *lo2 "olumnsC -$hen modifying
a data flo2, column remapping is sometimes needed -SSIS (J)( maps columns on name
instead of id -It also has an improed remapping dialog
9! Shared "onnection Managers& 1o create connection managers at the proGect leel that
can shared #y multiple packages in the proGect! 1he connection manager you create at
the proGect leel is automatically isi#le in the "onnection Managers ta# of the SSIS
Designer 2indo2 for all packages! -$hen conerting shared connection managers #ack
to regular packageC connection managers, they disappear in all other packages!
:! Scripting 0nhancements& No2 Script task and Script "omponent support for !J! -
Breakpoints are supported in Script "omponent
U! ODB" Source and Destination - -ODB" 2as not natiely supported in (JJ: -SSIS
(J)( has ODB" source X destination -SSIS (JJ: could access ODB" ia 6DO!N01
)J! educed Memory ?sage #y the Merge and Merge Hoin 1ransformations A 1he old
SSIS Merge and Merge Hoin transformations, although helpful, used a lot of system
resources and could #e a memory hog! In (J)( these tasks are much more ro#ust and
relia#le! Most importantly, they 2ill not consume e.cessie memory 2hen the multiple
inputs produce data at uneen rates!
))! ?ndoedo& One thing that annoys users in SSIS #efore (J)( is lack of support of
?ndo and edo! Once you performed an operation, you canKt undo that! No2 in SSIS
(J)(, 2e can see the support of undoredo!
5$. Di""erence &etween Script )ask and Script 'omponent in SSIS.Script )ask Script 'omponent
'ontrol-low@Date
-low
1he Script task is configured on the "ontrol *lo2 ta# ofthe designer and runs outside the data flo2 of the
package!
1he Script component is configured on theData *lo2 page of the designer and
represents a source, transformation, ordestination in the Data *lo2 task!
%urpose 6 Script task can accomplish almost any general-purposetask!
@ou must specify 2hether you 2ant to createa source, transformation, or destination 2iththe Script component!
:aising:esults
1he Script task uses #oth the 1askesult property andthe optional 0.ecution'alue property of the Dts o#Gect tonotify the runtime of its results!
1he Script component runs as a part of theData *lo2 task and does not report resultsusing either of these properties!
-
8/9/2019 Ssis Interview Questions - Copy
36/82
:aising4ents
1he Script task uses the 0ents property of the Dtso#Gect to raise eents! *or e.ample&Dts!0ents!*ire0rrorJ, 70ent Snippet7, e.!Message X"ontrol"hars!"r=f X e.!Stack1race
1he Script component raises errors, 2arnings,and informational messages #y using themethods of the ID1S"omponentMetaData)JJinterface returned #y the"omponentMetaData property! *or e.ample&
Dim myMetadata asID1S"omponentMetaData)JJmyMetaData P Me!"omponentMetaData
myMetaData!*ire0rror!!!C
4ecution 6 Script task runs custom code at some point in thepackage 2orkflo2! ?nless you put it in a loop containeror an eent handler, it only runs once!
6 Script component also runs once, #uttypically it runs its main processing routineonce for each ro2 of data in the data flo2!
4ditor 1he Script 1ask 0ditor has three pages& 4eneral, Script,and 0.pressions! Only the eadOnly'aria#les andead$rite'aria#les, and Script=anguage propertiesdirectly affect the code that you can 2rite!
1he Script 1ransformation 0ditor has up tofour pages& Input "olumns, Inputs andOutputs, Script, and "onnection Managers!1he metadata and properties that youconfigure on each of these pages determinesthe mem#ers of the #ase classes that areautogenerated for your use in coding!
Interactionwith the%ackage
In the code 2ritten for a Script task, you use the Dtsproperty to access other features of the package! 1heDts property is a mem#er of the ScriptMain class!
In Script component code, you use typedaccessor properties to access certain packagefeatures such as aria#les and connectionmanagers! 1he %re0.ecute method can accessonly read-only aria#les! 1he %ost0.ecutemethod can access #oth read-only andread2rite aria#les!
?singCaria&les
1he Script task uses the 'aria#les property of the Dtso#Gect to access aria#les that are aaila#le through thetaskKs eadOnly'aria#les and ead$rite'aria#les
properties! *or e.ample& string my'arLmy'ar P
Dts!'aria#lesY7MyString'aria#le7Z!'alue!1oStringCL
1he Script component uses typed accessorproperties of the autogenerated #ased class,created from the componentKs
eadOnly'aria#les and ead$rite'aria#lesproperties! *or e.ample&
string my'arL my'ar Pthis!'aria#les!MyString'aria#leL
?sing'onnections
1he Script task uses the "onnections property of the Dtso#Gect to access connection managers defined in thepackage! *or e.ample& stringmy*lat*ile"onnectionL my*lat*ile"onnection PDts!"onnectionsY71est *lat *ile"onnection7Z!6cquire"onnectionDts!1ransactionC asStringCL
1he Script component uses typed accessorproperties of the autogenerated #ase class,created from the list of connection managersentered #y the user on the "onnectionManagers page of the editor! *or e.ample&
ID1S"onnectionManager)JJconnMgrLconnMgr Pthis!"onnections!My6DON01"onnectionL
53.Di""erence &etween D)S and SSIS#
D)S&
=imited num#er of transformations!
=imited error handling!
Message #o. in actie-. scripts!
SSIS&
More num#er of transformations!
Better error handling!
-
8/9/2019 Ssis Interview Questions - Copy
37/82
Message #o. in !N01 scripting!
5. How to eecute package "rom command line
)! 1o e.ecute an SSIS package saed to S
-
8/9/2019 Ssis Interview Questions - Copy
38/82
6DO enumerator saes the alue from each column of the current ro2 into a separate
package aria#le! 1hen, the tasks that you configure inside the *oreach =oop container
read those alues from the aria#les and perform some action 2ith them!
/1. Delay Calidation, -orced 4ecution
Delay 'alidation& 'alidation take place during the package e.ecution!
0arly 'alidation& 'alidation take place Gust #efore the package e.ecution!/$. )rans"er Data&ase )ask
used to moe a data#ase to another S
-
8/9/2019 Ssis Interview Questions - Copy
39/82
/. How to Fenerate an 9uto Incremental
-
8/9/2019 Ssis Interview Questions - Copy
40/82
temporary ta#le created in one "ontrol *lo2 task can #e retained in another task!
etainSame"onnection means that the temp ta#le 2ill not #e deleted 2hen the task is
completed!
(! "reate a data-flo2 task that consumes your glo#al temp ta#le in an O=0 DB Source
component!
3! Set Delay'alidationP1?0 on the data-flo2 task, means that the task 2ill not check ifthe ta#le e.ists upon creation!
/2. How to 8ock a aria&le in Script )ask#
pu#lic oid MainC
[
'aria#les ars P null L
#ool fire6gain P trueL
Dts!'aria#leDispenser!=ockOne*oread7arName7, ref arsCL
Do something 2ith the alue!!!
ars!?nlockCL
Dts!1askesult P intCScriptesults!SuccessL
\
/3. How to pass property alue at :un time#
6 property alue like connection string for a "onnection Manager can #e passed to the
package using package configurations!
0. How to skip "irst 5 lines in each Input "lat "ile#
In the *lat file connection manager editor, Set the /;eader ro2s to skip/ property!
1. %arallel processing in SSIS
1o support parallel e.ecution of different tasks in the package, SSIS uses ( properties&
)!Ma."oncurrent0.ecuta#les& defines ho2 many tasks can run simultaneously, #y
specifying the ma.imum num#er of SSIS threads that can e.ecute in parallel perpackage! 1he default is -), 2hich equates to num#er of physical or logical processor ] (!
(! 0ngine1hreads& is property of each Data*lo2 task! 1his property defines ho2 many
threads the data flo2 engine can create and run in parallel! 1he 0ngine1hreads property
applies equally to #oth the source threads that the data flo2 engine creates for sources
and the 2orker threads that the engine creates for transformations and destinations!
1herefore, setting 0ngine1hreads to )J means that the engine can create up to ten
source threads and up to ten 2orker threads!
$. How do we conert data type in SSIS#
1he Data "onersion 1ransformation in SSIS conerts the data type of an input column
to a different data type!
-
8/9/2019 Ssis Interview Questions - Copy
41/82
SS:S Interiew uestions and9nswers
1. How do u implement 'ascading parameter#
1he list of alues for one parameter depends on the alue chosen in preceding
parameter!
0g& "ountry --V State --V "ity
$. How to pass parameter "rom :eport Ciewer 'ontrol to su& report#
(. How to open another report in a new window "rom eisting report#
?se a little Gaascript 2ith a customiFed ?= in the 7Hump to ?=7 option of the
Naigation ta#!
Non-parameteriFed Solution
1o get started, let/s pop up a simple non parameteriFed report! *ollo2 these instructions&
1. Instead of using the 7Hump to eport7 option on the Naigation ta#, use the
7Hump to ?=7 option!
2. Open the e.pression screen *. #uttonC!
3. 0nter the follo2ing&
P7Gaascript&oid2indo2!open/http&serernameTW(freportsererW(fpathto
W(freportXrs&"ommandPender/CC7
4. "lick O t2ice, then sae and deploy the report!
%arameteriFed Solution
6ssume you hae a field called %roduct"ode! Normally, you might hard code that like
this&
http&serernamereportsererTW(fpathto
W(freportXrs&"ommandPenderX%roduct"odeP)(3
In this case, you 2ant to pass aria#les dynamically, using an aaila#le alue from the
source dataset! @ou can think of it like this&
http&serernamereportsererTW(fpathto
W(freportXrs&"ommandPenderX%roduct"odeP*ields^%roduct"ode!'alue
1he e.act synta. in the 7Hump to ?=7 *.C e.pression 2indo2 2ill #e&
P7Gaascript&oid2indo2!open/http&serernamereportsererTW(fpathto
W(freportXrs&"ommandPenderX%roduct"odeP7]*ields^%roduct"ode!'alue]7/CC7
. How to pass parameter "rom chart to )a&le in same report#
5. How to apply custom 'olors o" chart report#
S10%)&
"reate your custome color palette in the report using "ustom "ode in your report! 1o do
so, click eport PV eport %roperties PV "ode and copy #elo2 code&
-
8/9/2019 Ssis Interview Questions - Copy
42/82
%riate color%alette 6s StringC P [ 7Blue7, 7ed7, 71eal7, 74old7, 74reen7,765UDU37,
7B:3)B7, 735(*(87, 7*)09D87, 70)8"587, 7"*B6UB7\
%riate count 6s Integer P J
%riate mapping 6s Ne2 System!"ollections!;ashta#leC
%u#lic *unction 4et"olorBy'al grouping'alue 6s StringC 6s StringIf mapping!"ontainseygrouping'alueC 1hen
eturn mappinggrouping'alueC
0nd If
Dim c 6s String P color%alettecount Mod color%alette!=engthC
count P count ] )
mapping!6ddgrouping'alue, cC
eturn c
0nd *unction
S10%(&
In the %ie "hart, select Series %roperties and select the *ill option from left side!
No2 2rite follo2ing e.pression in the "olor e.pression&
Pcode!4et"olor*ields^@ear!'alueC
;ere *ields^@ear!'alue is a field of dataset 2hich is used as "hart "ategory fields!
/. 'an we hae )a&le within a )a&le in SS:S report#
@es! $e can hae Nested 1a#les!
. How to apply stylesheet to SS:S :eports
select eport-Veport %roperties from the menu and then click the "ode ta#!
*unction Style0lement By'al 0lement 6s StringC 6s String
Select "ase ?"ase0lementC
"ase 716B=0>;06D0>B47
eturn 7ed7
"ase 716B=0>*OO10>B47
eturn 74reen7
"ase 716B=0>;06D0>1017
eturn 7$hite7tL
"ase e 0lsese
eturn 7Blackk
0nd Select0nd *unction
No2 apply this function to the style property of an element on the report!
Pcode!Style0lement716B=0>;06D0>1017C
If you 2ant apply dynamic styles to report, then create ta#les in sql serer and insert
style information into the ta#les!
"reate a Dataset, specify the Stored %rocedure!
e.ample& P*ields^16B=0>;06D0>101!'alue
2here 16B=0>;06D0>101 is a alue in the ta#le!
2. Dynamic sorting, Dynamic Frouping in SS:S
-
8/9/2019 Ssis Interview Questions - Copy
43/82
"an #e done using e.pressions!
3. Di""erent types o" -ilters
1he ( types of filters in SSS are&
Dataset -ilter* *iltering 2ithin the source query! $hen you implement a filter 2ithin
the data set, less data is sent from the source data#ase serer to the eport Serer -
usually a good thing!
:eport -ilter* 1his includes filtering after the source query has come #ack A on a data
region like the 1a#li.C, or a data grouping! $hen you implement a filter 2ithin the
report, 2hen the report is re-e.ecuted again 2ith different parameter choices, the eport
Serer uses cached data rather than returning to the data#ase serer!
?sing a Dataset *ilter is the most efficient method!
10. Di""erence &etween -ilter and %arameter# hich one is &etter#
In case of *ilters, first the data 2ill #e fetched from the data#ase, then the *ilters are
applied on the fetched data! *ilters are applied at run time first on the dataset, and then
on the data region, and then on the group, in top-do2n order for group hierarchies!
1o add a filter, 2e must specify a filter equation e.pressionC! 1he data type of filtered
data and alue must match!
%arameters are applied at the data#ase leel! 1he Data 2ill #e fetched #ased on
parameters at the data#ase leel using $;00 condition in the query!
%arameters are #etter than *ilters in performance!
11. +ptimi;ation o" :eport
eport can #e optimiFed in terms of 4rouping, *ilters!eport can #e optimiFed through "aching, Snapshot and su#scriptions!
)! 1he total time to generate a report D=C can #e diided into 3 elements&
1ime to retriee the data 1imeDataetriealC!
1ime to process the report 1ime%rocessingC
1ime to render the report 1imeenderingC
1otal time P 1imeDataetriealC ] 1ime%rocessingC ] 1imeenderingC
1hese 3 performance components are logged eery time for 2hich a deployed report is
e.ecuted! 1his information can #e found in the ta#le 0.ecution=ogStorage in theeportSerer data#ase!
S0=0"1 1O% )J Itempath, parameters,
1imeDataetrieal ] 1ime%rocessing ] 1imeendering as Ytotal timeZ,
1imeDataetrieal, 1ime%rocessing, 1imeendering,
Byte"ount, Yo2"ountZ,Source, 6dditionalInfo
*OM 0.ecution=ogStorage
OD0 B@ 1imestart D0S"
(! ?se the S
-
8/9/2019 Ssis Interview Questions - Copy
44/82
reports! "heck if all datasets are still #eing used! *or instance, datasets for aaila#le
parameter alues! emoe all datasets 2hich are not used anymore!
3! Sometimes a dataset contains more columns than used in the 1a#li.+list! ?se only
required columns in the Dataset!
! OD0 B@ in the dataset differs from the OD0 B@ in the 1a#li.+list! @ou need todecide 2here the data 2ill #e sorted! It can #e done 2ithin S
-
8/9/2019 Ssis Interview Questions - Copy
45/82
Image Source & 0.ternal
$hen you use an e.ternal image in a report, the image source is set to 0.ternal and the
alue for the image is the ?= to the image!
$hen to ?se&
$hen images are stored in a *ile System, 0.ternal *ile Share or $e# Site!
Image Source & Data#ase
If 2e add images that are stored in a data#ase to report then such image is kno2n as a
data-#ound image! Data-#ound images can also #e displayed from #inary data B=OBC
stored in a data#ase!
$hen to use&
$hen image is stored in a Data#ase!
$hen you specify a dataset field that is #ound to a data#ase field that contains an
image!
15. :ole o" :eport !anagerDeploying the reports onto the 2e# serer!
Deliering the reports through 0-mail or *ile Share using the su#scriptions!
"reating the "ached and Snapshot eports!
%roiding the Security to the reports!
1/. How to upload a report to report serer
In the eport Manager, 2e hae upload option to upload the reports!
1. hat is a Shared Dataset
Shared datasets retriee data from shared data sources that connect to e.ternal data
sources! 6 shared dataset contains a query to proide a consistent set of data for
multiple reports! 1he dataset query can include dataset parameters!
Shared datasets use only shared data sources, not em#edded data sources!
-
8/9/2019 Ssis Interview Questions - Copy
46/82
1o create a shared dataset, you must use an application that creates a shared dataset
definition file !rsdC! @ou can use one of the follo2ing applications to create a shared
dataset&
)! eport Builder& ?se shared dataset design mode and sae the shared dataset to a
report serer or Share%oint site!(! eport Designer in BIDS& "reate shared datasets under the Shared Dataset folder in
Solution 0.plorer! 1o pu#lish a shared dataset, deploy it to a report serer or Share%oint
site!
?pload a shared dataset definition !rsdC file! @ou can upload a file to the report serer or
Share%oint site! On a Share%oint site, an uploaded file is not alidated against the
schema until the shared dataset is cached or used in a report!
1he shared dataset definition includes a query, dataset parameters including default
alues, data options such as case sensitiity, and dataset filters!
12. How do u display the partial tet in &old "ormat in tet&o in :eport# =eg*
-irst
?se %lace;older
13. How to eep Headers Cisi&le hen Scrolling )hrough a :eport#
)! ight-click the ro2, column, or corner handle of a ta#li. data region, and then click
1a#li. %roperties!
(! On the 4eneral ta#, under o2 ;eaders or "olumn ;eaders, select ;eader should
remain isi#le 2hile scrolling!
-
8/9/2019 Ssis Interview Questions - Copy
47/82
3! "lick O!
)o keep a static ta&li mem&er =row or column> isi&le while scrolling
)! On the design surface, click the ro2 or column handle of the ta#li. data region to
select it! 1he 4rouping pane displays the ro2 and column groups!(! On the right side of the 4rouping pane, click the do2n arro2, and then click6danced
Mode! 1he o2 4roups pane displays the hierarchical static and dynamic mem#ers for
the ro2 groups hierarchy and the "olumn groups pane sho2s a similar display for the
column groups hierarchy!
3! "lick the static mem#er ro2 or columnC that you 2ant to remain isi#le 2hile
scrolling! 1he %roperties pane displays the 1a#li. Mem#er properties!
! In the %roperties pane, set *i.edData to 1rue!
$0. How to add %age Break)! On the design surface, right-click the corner handle of the data region and then click
1a#li. %roperties!
(! On the 4eneral ta#, under %age #reak options, select one of the follo2ing options&
6dd a page #reak #efore&Select this option 2hen you 2ant to add a page #reak #efore
the ta#le!
6dd a page #reak after&Select this option 2hen you 2ant to add a page #reak after the
ta#le!
*it ta#le on one page if possi#le&Select this option 2hen you 2ant the data to stay on
one page!
-
8/9/2019 Ssis Interview Questions - Copy
48/82
$1. 9 main report contain su&report also. 'an we eport &oth main report and
su&report to 4cel#
@es! 1he e.ported report contains #oth the mail report and su# report!
$$. how to conert %D- report "rom %ortrait to 8andscape "ormat#
In eport %roperties --V
Set the 2idth of the report to the landscape siFe of your 6 paper& (U!9 cm
Set the height of the report to () cm!
1o aoid e.tra #lank pages during e.port, the siFe of the #ody should #e less or equal to
the siFe of the report - margins!
Set the 2idth of the #ody to (8!9 cm (U!9 -)!5 - )!5C
Set the height of the #ody to ): cm () - )!5 -)!5C
$(. 4rror handling in :eport
Step )& 6ll the data sets of the report should contain one addition input parameter 2hich
should pass a unique information for eery request for eery click of 'ie2 eport#uttonC made #y the user!
Step (& Need to implement 1@ "61"; #locks for all the Stored procedures used in the
SSS reports through datasets! 1he "61"; section of eery procedure should hae the
proision to sae the error details into DB ta#le, if any error occurred 2hile e.ecution of
that procedure!
Step 3& 6dd one more additional dataset 2ith the name 70rrorInfo7 2hich should call the
store procedure ?S%>0O>IN*OC! 1his procedure should #e accepting a unique alue!
1his unique alue should #e passed to all the data sets for eery click of /'ie2 eport/
#utton made #y the user! 1his dataset 2ill return the error information aaila#le in the
data #ase ta#le #y erifying records 2ith the unique id 2hich has passes as input
parameter!Step &0na#le the ?se Single 1ransaction $hen %rocessing
-
8/9/2019 Ssis Interview Questions - Copy
49/82
source properties, 2hich makes all the query e.ecutions through a single transaction!
Step 5& 6fter successful completion of all the a#oe mentioned steps, insert ne2 ta#le on
SSS report 2ith custom error information 2hich 2ill #e sho2n to the report user if the
user gets any error during e.ecution of the report!
$. Hae u worked on any (rd party :eport )ools
1here are fe2 third party eport 1ools like Neron, iFenda!
$5. Di""erent ways o" Deploying reports
)! $e can deploy the reports using rs!e.e tool
(! In the Solution 0.plorer,
(!)!ight-click the report proGect, and then click %roperties!
(!(!In the %roperty %ages dialog #o. for the proGect, select a configuration to edit from
the "onfiguration list! "ommon configurations are De#ug=ocal, De#ug, and elease!
(!3!In StartItem, select a report to display in the preie2 2indo2 or in a #ro2ser
2indo2 2hen the report proGect is run!
(!!In the +erwriteDataSourceslist, select 1rue to oer2rite the shared data source
on the serer each time shared data sources are pu#lished, or select *alse to keep the
data source on the serer!
(!5!In the )argetDataSource-olderte.t #o., type the folder on the report serer in
2hich to place the pu#lished shared data sources! 1he default alue for
1argetDataSource*older is Data Sources! If you leae this alue #lank, the data sources
2ill #e pu#lished to the location specified in 1argeteport*older!
(!8! In the )arget:eport-olderte.t #o., type the folder on the report serer in 2hich
to place the pu#lished reports! 1he default alue for 1argeteport*older is the name of
the report proGect!
(!9! In the )argetSerer?:8te.t #o., type the ?= of the target report serer! Before
you pu#lish a report, you must set this property to a alid report serer ?=!
-
8/9/2019 Ssis Interview Questions - Copy
50/82
3! 1here are ( options for deploying the reports that you create 2ith eport Builder 3!J&
)! eport Manager
(! Share%oint document li#rary
$/. Di""erence &etween 'ached :eport and Snapshot :eport
"ached eport is a saed copy of processed report!
1he first time a user clicks the link for a report configured to cache, the report e.ecution
process is similar to the on-demand process! 1he intermediate format is cached and
stored in eportSerer1empDB Data#ase until the cache e.piry time!
If a user request a different set of parameter alues for a cached report, then the report
processor treats the requests as a ne2 report e.ecuting on demand, #ut flags it as a
second cached instance!
eport snapshot contains the
-
8/9/2019 Ssis Interview Questions - Copy
51/82
It is important to protect reports as 2ell as the report resources! 1herefore, eporting
Serices implement a fle.i#le, role-#ased security model!
(1. Di""erent type o" :eports
=inked report&6 linked report is deried from an e.isting report and retains the original/s
report definition! 6 linked report al2ays inherits report layout and data source properties
of the original report! 6ll other properties and settings can #e different from those of theoriginal report, including security, parameters, location, su#scriptions, and schedules!
Snapshot reports& 6 report snapshot contains layout information and query results that
2ere retrieed at a specific point in time! eport snapshots are processed on a schedule
and then saed to a report serer!
Su#report& 6 su#report is a report that displays another report inside the #ody of a main
report! 1he su#report can use different data sources than the main report!
"ached reports& 6 cached report is a saed copy of a processed report! "ached reports
are used to improe performance #y reducing the num#er of processing requests to the
report processor and #y reducing the time required to retriee large reports! 1hey hae a
mandatory e.piration period, usually in minutes!
Drill Do2n eport& Means naigate from the summary leel to detail leel in the same
report!
Drill 1hrough eport& Naigation from one report to another report!
6d hoc reports&6d ;oc reporting allo2s the end users to design and create reports on
their o2n proided the data models!
3 components& eport Builder, eport Model and Model Designer
?se /Model Designer/ tool to design /eport Models/ and then use /eport Model/ tool to
generate reports!
eport Builder
- $indo2s $inform application for 0nd users to #uild ad-hoc reports 2ith the help of
eport models!
($. 4plain the :eport !odel Steps.
)! "reate the report model proGect
select 7eport Model %roGect7 in the 1emplates list
6 report model proGect contains the definition of the data source !ds fileC, the definition
of a data source ie2 !ds fileC, and the report model !smdl fileC!
(! Define a data source for the report model
3! Define a data source ie2 for the report model
6 data source ie2 is a logical data model #ased on one or more data sources!
S
-
8/9/2019 Ssis Interview Questions - Copy
52/82
control #ecause eport 'ie2er control does not perform any data processing in =ocal
processing mode, #ut used data that the host application supplies!
(5. Di""erence &etween Sorting and Interactie Sorting#
1o control the Sort order of data in report, you must set the sort e.pression on the data
region or group! 1he does not hae control oer sorting!
@ou can proide control to the user #y adding Interactie Sort #uttons to toggle #et2een
ascending and descending order for ro2s in a ta#le or for ro2s and columns in a matri.!
1he most common use of interactie sort is to add a sort #utton to eery column header!
1he user can then choose 2hich column to sort #y!
(/. hat is :eport Builder
$indo2s $inform application for 0nd users to #uild ad-hoc reports 2ith the help of
eport models!
(. Di""erence &etween )a&le report and !atri :eport
6 1a#le eport can hae fi.ed num#er of columns and dynamic ro2s!
6 Matri. eport has dynamic ro2s and dynamic columns!
(2. hen to use )a&le, !atri and 8ist
)! ?se a 1a#le to display detail data, organiFe the data in ro2 groups, or #oth!
(! ?se a matri. to display aggregated data summaries, grouped in ro2s and columns,
similar to a %iot1a#le or crossta#! 1he num#er of ro2s and columns for groups is
determined #y the num#er of unique alues for each ro2 and column groups!
3! ?se a list to create a free-form layout! @ou are not limited to a grid layout, #ut can
place fields freely inside the list! @ou can use a list to design a form for displaying many
dataset fields or as a container to display multiple data regions side #y side for grouped
data! *or e.ample, you can define a group for a listL add a ta#le, chart, and imageL and
display alues in ta#le and graphic form for each group alue
(3. :eport Serer 'on"iguration -iles
)! SeportSerer!config&
Stores configuration settings for feature areas of the eport Serer serice& eport
Manager, the eport Serer $e# serice, and #ackground processing!
(! SSr%olicy!config
Stores the code access security policies for the serer e.tensions!
3! SMgr%olicy!config
Stores the code access security policies for eport Manager!
! $e#!config for the eport Serer $e# serice
Includes only those settings that are required for 6S%!N01!5! eportingSericesSerice!e.e!config
8! egistry settings
9! $e#!config for eport Manager
Includes only those settings that are required for 6S%!N01
:! SeportDesigner!config
U! S%reie2%olicy!config
0. Di""erence &etween a :eport and adhoc :eport
6d ;oc reporting allo2s the end users to design and create reports on their o2n
proided the data models!
6dhoc eport is created from e.isting report model using eport Builder!
-
8/9/2019 Ssis Interview Questions - Copy
53/82
1. How do u secure a :eport
)! 6uthoriFation is proided through a role-#ased security model that is specific to
eporting Serices!
Different 1ypes of oles proided #y SSS &
- Bro2sers
- "ontent Manager- My eports
- %u#lishers
- eport Builder
(! IIS security controls access to the report serer irtual directory and eport Manager!
$.How to 'om&ine Datasets in SS:S =1 Dataset gets data "rom +racle and
other dataset "rom Sl Serer>
?sing =ook?% function, 2e can com#ine ( datasets in SSS!
In the follo2ing e.ample, assume that a ta#le is #ound to a dataset that includes a field
for the product identifier %roductID! 6 separate dataset called 7%roduct7 contains the
corresponding product identifier ID and the product name Name!
P=ookup*ields^%roductID!'alue, *ields^ID!'alue, *ields^Name!'alue, 7%roduct7C
In the a#oe e.pression, =ookup compares the alue of %roductID to ID in each ro2 of
the dataset called 7%roduct7 and, 2hen a match is found, returns the alue of the Name
field for that ro2!
(. Di""erence &etween :eport Serer and :eport !anager
eport Serer handle authentication, data processing, rendering and deliery operations!
1he configuration settings of eport Manager and the eport Serer $e# serice are
stored in a single configuration file rsreportserer!configC!
eport Manager is the 2e#-#ased application included 2ith eporting Serices that
handles all aspects of managing reports deploying datasources and reports, caching a
report, su#scriptions, snapshotC!
. Steps to repeat )a&le Headers in SS:S $002#
)! Select the ta#le
(! 6t the #ottom of the screen, select a dropdo2n arro2 #eside column groups! 0na#le
76danced Mode7 #y clicking on it!
3! under o2 4roups,select the static ro2 and choose properties press *!
! Set the follo2ing attri#utes for the static ro2 or header ro2! Set epeatOnNe2%ageP 1rue for repeating headers
Set eep$ith4roupP 6fter
Set *i.edDataP1rue for keeping the headers isi#le!
5. How to add assem&lies in SS:S
-
8/9/2019 Ssis Interview Questions - Copy
54/82
5. :eport 4tensions#
/. parent grouping, child grouping in SS:S
. How to show A
-
8/9/2019 Ssis Interview Questions - Copy
55/82
2. hat is the G?se single transaction when processing theueriesG in the Datasource# Dataset 4ecution +rder#
By default, datasets are e.ecuted in parallel!
1his option used to reduce the amount of open connections to thedata#ase! *or e.ample, if you hae a report 2ith 3 datasets and youdonKt hae this option checked, a ne2 connection is made to the data#asefor eery single dataset! ;o2eer, if you hae it checked, then only oneconnection 2ill #e open to the data#ase and all the datasets 2ill returnthe data and the connection 2ill #e closed! 1his can #e used to reducenet2ork traffic and potentially increase performance!
Open the data source dialog in report designer, and select the 7?se Single1ransaction 2hen processing the queries/ check #o.! Once selected,datasets that use the same data source are no longer e.ecuted in parallel!1hey are also e.ecuted as a transaction, i!e! if any of the queries fails toe.ecute, the entire transaction is rolled #ack!
1he order of the dataset e.ecution sequence is determined #y the top-do2n order of the dataset appearance in the D= file, 2hich alsocorresponds to the order sho2n in report designer!3. :eportSerer and :eportSerer)empDB Data&aseseportSerer& hosts the report catalog and metadata!
*or eg& keeps the catalog items in the "atalog ta#le, the data source information in the
Data-Source ta#le of eportSerer Data#ase!
eportSerer1empDB& used #y S for caching purposes!
*or eg& once the report is e.ecuted, the eport Serer saes a copy of the report in the
-
8/9/2019 Ssis Interview Questions - Copy
56/82
eportSerer1empDB data#ase!
What do we mean by datafow in SSIS?
Data flow is nothing but the flow of data from the corresponding sources to the referred
destinations. In this process, the data transformations make changes to the data to make it ready
for the data warehouse.
What is a breakpoint in SSIS? How is it setup? How do you disable
it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
opportunity to review the status of the data, variables and the overall status of the I package.
!" uni#ue conditions e$ist for each breakpoint.
Breakpoints are setup in BID. In BID, navigate to the control flow interface. %ight click on the
ob&ect where you want to set the breakpoint and select the '(dit Breakpoints)* option.
Can you name 5 or more o the native SSIS connection manaers?
!+ -(DB connection /sed to connect to any data source re#uiring an -(DB connection
0i.e.,
1- erver 2"""+
-
8/9/2019 Ssis Interview Questions - Copy
57/82
2+ 3lat file connection /sed to make a connection to a single file in the 3ile ystem. %e#uired
for reading information from a 3ile ystem flat file
4+ AD.5et connection /ses the .5et 6rovider to make a connection to 1- erver 2""7 or
other
connection e$posed through managed code 0like 89+ in a custom task
:+ Analysis ervices connection /sed to make a connection to an Analysis ervices database or
pro&ect. %e#uired for the Analysis ervices DD- Task and Analysis ervices 6rocessing Task
7+ 3ile connection /sed to reference a file or folder. The options are to either use or create a file
or folder
;+ ($cel
What is the use o !ulk Insert "ask in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into #l erver. It supports
only -( DB connections for destination database.
What is Conditional Split transormation in SSIS?
This is &ust like I3 condition which checks for the given condition and based on the condition
evaluation, the output will be sent to the appropriate /T6/T path. It has 5( input and
e the transform for the students in a class who
have marks greater than :" to one path and the students who score less than :" to another path.
How do you eliminate #uotes rom bein uploaded rom a fat $le
to S%& Server?
This can be done using T(?T 1/A-I3I(% property. In the I package on the 3lat 3ile
8onnection
-
8/9/2019 Ssis Interview Questions - Copy
58/82
SaveCheckpoints 8hoose from these options@ True or 3alse 0default+. =ou must select
True to implement the 8heckpoint behavior.
What are the di+erent values you can set or Checkpoint*sae
property ?There are three values, which describe how a checkpoint file is used during package e$ecution@
!+ )ever@ The package will not use a checkpoint file and therefore will never restart.
2+ I ,'ists@ If a checkpoint file e$ists in the place you specified for the 8heckpoint3ilename
property, then it will be used, and the package will restart according to the checkpoints written.
4+ -lways@ The package will always use a checkpoint file to restart, and if one does not e$ist,
the package will fail.
What is the .)&/ 0roperty you need to set on "-S1S in order to
con$ure CH,C10.I)"S to 2,S"-2" packae rom ailure?
The one property you have to set on the task is (ail0ackae.n(ailure. This must be set
for each task or container that you want to be the point for a checkpoint and restart. If you do not
set this property to true and the task fails, no file will be written, and the ne$t time you invoke the
package, it will start from the beginning again.
Where can we set the CH,C10.I)"S3 in 4ata(low or Control(low ?
8heckpoints only happen at the 8ontrol 3lowC it is not possible to checkpoint transformations or
restart inside a Data 3low. The Data 3low Task can be a checkpoint, but it is treated as any other
task.
Can you e'plain di+erent options or dynamic con$urations in
SSIS?
!+ ?ed database with all variables
What is the use o 0ercentae Samplin transormation in SSIS?
6ercentage ampling transformation is generally used for data mining. This transformation builds
a random sample of set of output rows by choosing specified percentage of input rows. 3or
e$ample if the input has !""" rows and if I specify !" as percentage sample then the
transformation returns !" of the %A5D< records from the input data.
What is the use o "erm ,'traction transormation in SSIS?
Term ($traction transformation is used to e$tract nouns or noun phrases or both noun and noun
phrases only from (nglish te$t. It e$tracts terms from te$t in a transformation input column and
-
8/9/2019 Ssis Interview Questions - Copy
59/82
then writes the terms to a transformation output column. It can be also used to find out the
content of a dataset.
What is 4ata iewer and what are the di+erent types o 4ata
iewers in SSIS?A Data Eiewer allows viewing data at a point of time at runtime. If data viewer is placed before
and after the Aggregate transform, we can see data flowing to the transformation at the runtime
and how it looks like after the transformation occurred. The different types of data viewers are@
!. Frid
2. Gistogram
4. catter 6lot
:. 8olumn 8hart.
What is Inore (ailure option in SSIS?
In Ignore 3ailure option, the error will be ignored and the data row will be directed to continue on
the ne$t transformation. -et*s say you have some H/5 data0wrong type of data or H/5 data+
flowing from source, then using this option in I we can %(DI%(8T the &unk data records to
another transformation instead of 3AI-I5F the package. This helps to
-
8/9/2019 Ssis Interview Questions - Copy
60/82
What is SSIS 4esiner?
It is a graphical tool for creating packages. It has : tabs@ 8ontrol 3low, Data 3low, (vent
Gandlers and 6ackage ($plorer.
What is Control (low tab?It is the tab in I designer where various Tasks can be arranged and configured. This is the tab
where we provide and control the program flow of the pro&ect.
What is 4ata (low tab?
This is the tab where we do all the work related to (T- &ob. It is the tab in I Designer where
we can e$tract data from sources, transform the data and then load them into destinations.
What is the unction o control fow tab in SSIS?
n the control flow tab, the tasks including dataflow task, containers and precedence constraints
that connect containers and tasks can be arranged and configured.
What is the unction o ,vent handlers tab in SSIS?
n the (vent handlers tab, workflows can be configured to respond to package events.
3or e$ample, we can configure Jork 3low when A5= task 3ailes or tops or tarts ..
What is the unction o 0ackae e'plorer tab in SSIS?
This tab provides an e$plorer view of the package. =ou can see what is happening in the package.
The 6ackage is a container at the top of the hierarchy.
What is Solution ,'plorer?
It is a place in I Designer where all the pro&ects, Data ources, Data ource Eiews and other
miscellaneous files can be viewed and accessed for modification.
How do we convert data type in SSIS?
The Data 8onversion Transformation in I converts the data type of an input column to a
different data type.
How are variables useul in ssis packae?
Eariables can provide communication among ob&ects in the package. Eariables can provide
communication between parent and child packages. Eariables can also be used in e$pressions and
scripts. This helps in providing dynamic values to tasks.
,'plain -reate "ransormation in SSIS?
It aggregates data, similar you do in applying T1- functions like Froup By,
-
8/9/2019 Ssis Interview Questions - Copy
61/82
same datagrid. ome of operation functions listed below @
L Froup By
L Average
L 8ount
L 8ount Distinct @ count distinct and non null column value
L e transformation cache to certain number of key
threshold. If you set it low, optimi>ation will target to 7"",""" keys written to cache, medium can
handle up to 7 million keys, and high can handle up to 27 million keys, or you can specify
particular number of keys here. Default value is unspecified. imilar to number of keys for 8ount
Distinct scale option. It is used to optimi>e number of distinct value written to memory, default
value is unspecified. Auto ($tend 3actor is used when you want some portion of memory is used
for this component. Default value is 27 of memory.
,'plain -udit "ransormation ?
It allows you to add auditing information as re#uired in auditing world specified by GI66A and
arbanes$ley 0?+. Auditing options that you can add to transformed data through this
transformation are @
!. ($ecution of Instance F/ID @ ID of e$ecution instance of the package2. 6ackageID @ ID of the package
4. 6ackage5ame
:. EersionID @ F/ID version of the package
7. ($ecution tartTime
;.
-
8/9/2019 Ssis Interview Questions - Copy
62/82
;. Giraganakatakanatraditional 8hinesesimplified 8hinese
K. -inguistic casing
,'plain Conditional split "ransormation ?
It functions as if)then)else construct. It enables send input data to a satisfied conditional
branch. 3or e$ample you want to split product #uantity between less than 7"" and greater or
e#ual to 7"". =ou can give the conditional a name that easily identifies its purpose. (lse section
will be covered in Default utput 8olumn name.
After you configure the component, it connect to subse#uent transformationdestination, when
connected, it pops up dialog bo$ to let you choose which conditional options will apply to the
destination transformationdestination.
,'plain Copy column "ransormation?
This component simply copies a column to another new column. Hust like A-IA 8olumn in T
#l.
,'plain 4ata conversion "ransormation?
This component does conversion data type, similar to T1- function 8AT or 85E(%T. If
you wish to convery the data from one type to another then this is the best bet. But please make
sure that you have 8
-
8/9/2019 Ssis Interview Questions - Copy
63/82
,'plain 6ere 7oin "ransormation?
-
8/9/2019 Ssis Interview Questions - Copy
64/82
What is the di+ between control fow Items and data fow Items?
The control flow is the highest level control process. It allows you to manage the runtime
process activities of data flow and other processes within a package.
Jhen we want to e$tract, transform and load data within a package. =ou add an I dataflow
task to the package control flow.
What are the main component o SSIS8pro9ect:architecture;?
I archItecture has : main components
!.ssis service
2.ssis runtime engine Q runtime e$ecutables
4.ssis dataflow engine Q dataflow components
:.ssis clients
4i+erent components in SSIS packae?
8ontrol flow
Data flow
(vent handler
6ackage e$plorer
What are Connection 6anaers?
It is a bridge bw package ob&ect and physical data. It provides logical representation of a
connection at design time the properties of the connection mgr describes the physical connection
that integration services creates when the package is run.
What is environment variable in SSIS?
An environment variable configuration sets a package property e#ual to the value in an
environment variable.
(nvironmental configurations are useful for configuring properties that are dependent on the
computer that is e$ecuting the package.
How to provide securIty to packaes?
Je can provide security in two ways
!. 6ackage encryption
2. 6assword protection.
What are 0recedence constraints?
8onstraints that link e$ecutable, container, and tasks wIthin the package control flow and specify
condItion that determine the se#uence and condItions for determine whether e$ecutable run.
What is 4esin time 4eployment in SSIS ?
-
8/9/2019 Ssis Interview Questions - Copy
65/82
Jhenyou run a package from with in BID,it is built and temporarily deployed to the folder. By
default the package will be deployed to the BI5 folder in the 6ackage*s 6ro&ect folder and you
can configure for custom folder for deployment. Jhen the 6ackage*s e$ecution is completed and
stopped in BID,the deployed package will be deleted and this is called as Design Time
Deployment.
= What is a workfow in SSIS?a).`a workow is a set of instructions on how to execute Tasks.!t is a set of instructions on how to execute Tasks such as sessions, emailsand shell commands. a workow is created form work ow mgr.)
= What is the 4i+erence between control fow Items and datafow Items?a).the control ow is the highest level control process. !t allows "ou tomanage the run#time process the run time process activities of data owand other processes within a package.
$hen we want to extract, transform and load data within a package. %ouadd an &&!& dataow Task to the package control ow.
@= What are the main components o SSIS 8pro9ect:architecture;?').&&!& architecture has ( main components.&&!& service*.&&!& runtime engine + runtime executables.&&!& dataow engine + dataow components(.&&!& clients-
5=di+erent components in SSIS packae?
. ontrol ow*.data ow.event handler(.package explorer
Containers/ provide structure and scope to "our package"ypes o containersAi. "ask host containerAthe Taskhost container services a single Task.ii. Se#uence containerA!t can handle the ow of subset of a packageand can help "ou drive a package into smaller more manageable process.0ses/#
-
8/9/2019 Ssis Interview Questions - Copy
66/82
. 1rouping Tasks so that "ou can disable a part of the package that nolonger needed.*. 2arrowing the scope of the variable to a container.. 3anaging the propert" of multiple Tasks in one step b" setting theproperties of the container.
iii. (or loop containerAevaluates an expression and repeats !ts workowuntil the expression evaluates to false.iv. (or each loop containerAde4nes a control ow repeatedl" b" usingan enumerator.(or each loop container repeats the control ow for each member of aspeci4ed enumerator.
"asksA !t provides the functionalit" to "our package.
!t is a individual unit of work.,vent handler/ !t responds to raised events in "our package.
0recedence constraintsA It provides ordinal relationship b5w various!tems in "our package.
B= How to deploy the packae?To deplo" the package 4rst we need to con4gure some properties.
1o to project tab#6package properties#6we get a window, con4guredeplo"ment 0tilit" as 7"rue7
&pecif" the path as 7bin5deplo"ment7
= Connection manaerAa).!t is a bridge b5w package object and ph"sical data. !t provides logicalrepresentation of a connection at design time the properties of theconnection mgr describes the ph"sical connection that integrationservices creates when the package is run.
D= "ell the *tility to e'ecute 8run; the packae?
a) !n 8!9& a package that can be executed in debug mode b" using thedebug menu or toolbar or from solution explorer.!n production, the package can be executed from the command line orfrom 3icrosoft windows 0tilit" or !t can be scheduled for automatedexecution b" using the &:; server agent.i). 1o to#6debug menu and select the start debugging buttonii).press
-
8/9/2019 Ssis Interview Questions - Copy
67/82
. To open command prompt#6run#6t"pe dtexecui#6press enter*. The execute package 0tilit" dialog box opens.. in that click execute to run the package.$ait until the package has executed successfull".
b).9T>xec 0tilit".open the command prompt window.*. ommand prompt window#6t"pe dtexec 5followed b" the 9T&, &:;, or4le option and the package path, including package name.. !f the package encr"ption level is encr"pting sensitive with password orencr"pt all with password, use the decr"pt option to provide the password.!f no password is included, dtexec will prompt "ou for the password.(. ?ptionall", provide additional command#line options=. @ress enter.
A. ?ptionall", view logging and reporting information before closing thecommand prompt window.
The execute package 0tilit" dialog box opens.B. !n the execute package 0tilit" dialog box, click execute package.$ait until the package has executed successfull".v).using &:; server mgmt studio to execute package. !n &&3& right click a package, and then click run package.>xecute package 0tilit" opens.*. >xecute the package as described previousl".
E= How can u desin SC4 in SSIS?a) 9ef/#&9 explains how to capture the changes over the period of time.
This is also known as change data capture.type1:!t keeps the most recent values in the target. !t does not maintainthe histor".type2:!t keeps the full histor" in the target database.
-
8/9/2019 Ssis Interview Questions - Copy
68/82
package.
= What is environment variable in SSIS?a) 'n environment variable con4guration sets a package propert" equal tothe value in an environment variable.
>nvironmental con4gurations are useful for con4guring properties that aredependent on the computer that is executing the package.
xecute 9ts *FFF package TaskG. >xecute package Tas