SSIS - Debugging

Refer Source:

SQL Server Integration Services (SSIS) Part 8 - Debugging

Run-time Errors:

Data Flow: Data Viewers

Control Flow: Break Points , Watching Variable

Package: Loading from excel file.

Control Flow:

       Data Flow:

                            Excel Source

                            Row Count Package: Loading from excel file.

Control Flow:

       Data Flow:

                            Excel Source

                            Row Count  (  It keeps  the Rowcount in a User variable say User:NumberRows , this is passed to script task)

    Script Task:

Note:

Script

ScriptLanguage: Microsoft Visual C# 2010

...

ReadOnlyVariables:User::NumberRows

----------------------

Runtime errors:

 View Menu - Output  ( show output window the error message )

----------------------

Error Message  shown in the output window:

"Excel Connection Manager":

The requested OLE DB  provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64 - bit 

driver is not installed , run the package in 32-bit mode.

...
The OLE DB  provider "Microsoft.Jet.OLEDB.4.0" has not been registed.



Solution:

Project Properties: 

               Debugging page:

                           Run64BitRuntime Property: false


Notes:

1. There is no 64 bit Jet driver, so that means no Excel or Access support on 64bit OS.
... To Resolve this issue run SSIS package using 32bit version of  DTSExec.

---

Data Flow:-> Right Click on any pipe of data ->Enable Data Viewer

In this one can choose which column values to see in "metadata", also see all the columns and their data type.

DISABLE when not needed, do not delete it as it.

---

Control Flow:->--

Control Flow:

Right click on "Control Flow Task" -> Edit Breakpoints

It would show all the events "OnPreExecute" or "OnPostExecute" event etc. ,  "Hit Count Type"  say after 10th file got loaded you want it to run in debug mode.

---

--

From Menu Debug -> Windows -> ( Breakpoints , Output , Immediate )

--

--

When the package runs it will have the Break Point on the first event say "OnPreExecute"  , now if one click on 

"Output" window one will see the messages. At this point if  again one choose Menu->Debug ( many options - used for very large system , typically)

will show.Not to choose any just click run the package runs.

--But at this point it's no use

--

Watch Variables:

Menu ->Debug->Local Window ( to view all variables.)

Note: This shows all variables the system variables & User variables too. Difficult to find the User variable.

--

Menu->Debug->Attach to Process

to do  tha above first choose Menu->Debug->Watch Window->Watch Window1

Click on the "Variable" ,  drag the variable to watch window.

--

Menu -> Quick Watch->Shift +F9

choose which variable to watch and even can add to the watch window.

--