SSIS - Testing the source in your data flow
When building an SSIS package, which I seem to be doing
more and more of lately, I am invariably using a data flow. Whilst the data
flows often end up with complicated transforms in them my approach to building
it is very much bit by bit.
The first step is to get your source component working. To do that drop the
source component you want on and configure it appropriately. Next you need to
test it. I find the simplest way is to drop a derived column component on and
connect the source component to it.
You now have a valid data flow that can be run. A data flow only has to have
a source component, using the derived column transform allows you to put a data
viewer on the flow of data coming out of your source component.
This component can be removed later on, however I often find I leave it there
to perform data cleansing or calculating new fields etc.
If you are doing looping in your control flow you can set that up at
this point to verify that your looping is working correctly. Its a lot easier to
test at this stage when it is quick to do so rather than when you have lots more
components in the data flow.