You are writing a SQL Server Integration Services (SSIS) package that transfers data from a
legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package
but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the
amount of development effort.
What should you do?
A.
Add a data tap on the output of a component in the package data flow.
B.
Deploy the package by using an msi file.
C.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
D.
uses the dtutil /copy command.
E.
Deploy the package to the Integration Services catalog by using dtutil and use SQL
Server to store the configuration.
F.
Create an OnError event handler.
G.
uses the Project Deployment Wizard.
H.
Use the gacutil command.
I.
Create a reusable custom logging component.
J.
Run the package by using the dtexec /rep /conn command.
K.
Run the package by using the dtexec /dumperror /conn command.
Explanation:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html
http://msdn.microsoft.com/en-us/library/hh230989.aspx
http://msdn.microsoft.com/en-us/library/jj655339.aspx
Data Taps are a new feature in SQL Server 2012 for troubleshooting package execution. With data taps, one can capture a copy of the data from a specific data path in the Data Flow Task into a comma-delimited file at run time.
Data taps are only available when executing a package deployed to the SSISDB catalog.
Maybe a combination between A and I. “..You need to establish the best technique to log these invalid rows..” A only visualize data – valid/invalid
This question makes no sense. If youre writing the ssis package and it’s discarding invalid rows, rewrite the package so it puts the invalid rows into another destination. Depending on how the rows have been deemed invalid, this is trivial to do.
In order to use a data tap, you have to separate out the invalid rows into a data flow anyway and put the tap on that flow. Then, when its running, I think there needs to be human intervention as its a debug tool, not a production level reporting tool. Since the data flow has been made anyway, might as well direct that flow in to a destination of some sort. This is why datatap can’t reasonably be the answer. Custom logger on the other hand works for nearly any answer but for the fact that it’s not easy. That’s why data taps is the answer. It’s just a stupid one.
Agreed with Henry. There’s no other option to choose than A.
In real world project, I wld never do that. rather use some splitting component and Create a reusable custom logging component.