March 15, 2010 at 3:48 pm
The IS package is simple:
* One Exec SQL Task: it truncates the destination table
* One Data Flow Task: One OLE DB Source with an SQL Command which feeds an OLE DB Destination
The problem is that the Source fails to feed the Destination: the code in the Source was tested in SQL Management Studio with success, then it was copy/pasted into the OLE DB Source SQL Command Text window and the Preview button was pressed to successfully retrieve the desired data.
When the Start Debugging button is pressed, after an appropriate delay, all the boxes turn green as if successful, but no data passed from the OLE DB Source to the OLE DB Destination.
I have reviewed the code, & the connection manager, I had someone else review the package, I had a second person review the package, I created the package in Visual Studio 2008, and re-created it in VS 2005... I am at wits end!(';-)');
Anybody have this problematic experience? and have a solution?
March 15, 2010 at 3:54 pm
Trace your SQL instance and find out what the SQL statement coming from the source adapter looks like.
March 16, 2010 at 9:23 am
I agree with John. Also are there any row counts showing on the dataflow during the run or does it just go green..
CEWII
March 16, 2010 at 3:19 pm
Elliot, the connection between the Source and the Destination does not reflect a count, the task boxes just turn green and the insert count for the Destination is Zero.
Other similar tasks in the same package that execute an SQL Command work properly and show counts along paths between Source and Destination.
We have had this problem one other time in the past, it seemed to resolve itself when I removed comment lines but we were not confident that it was the real reason, this incident confirms that it was not the problem since there are no comments in this code.
March 16, 2010 at 7:34 pm
John,
The trace was started and the SQL statement looks like the same statement that I pasted into the OLE DB Source.
I have developed an alternate solution: instead of using a preferred Data Flow Task, I switched to an Execute SQL Task and used an Insert/Select. The Select portion of the statement was copy/pasted from the OLE DB Source.
Instead of a single Data Flow Task, I now have an Execute SQL Task (Insert/Select) to load the data into a temp table on the source server followed by a Data Flow Task that uses a Table View in the OLE DB Source (instead of the original SQL Command) to grab the data from the source server, and a Table View in the OLE DB Destination to insert the data into the destination server.
It will stay a mystery for awhile longer!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply