October 10, 2013 at 3:13 pm
I have a data flow task, which should update records in one environment (in this case, UAT) from records in a different environment ( in this case, Production). The data flow task shows a little red circle with an "X" in it. When I hover over the X, I get part of an error message indicating that an OLE DB error has occurred and it mentions error number 0X80040E0C. Funny thing is, when I run the package in the development environment, all tasks show green and no errors are reported in the Execution Results tab. But, the data in the UAT database is not updated. So, obviously something is wrong.
The data flow task contains two components, an OLE DB Source and an OLE DB Command. The OLE DB Source uses a command to retrieve records from the source (i.e. Production) environment. When I preview the source, I get the three records I expect back. Also, when debugging, the UI shows that three records were output from this task. The OLE DB Command is a parameterized UPDATE statement, which should update only the three records in UAT, which match the records from Production. I think I've got this configured correctly... I'm using the connection to UAT. The SqlCommand property is set, and input columns are mapped to the parameters.
I've looked around, and I didn't see anybody having a similar problem. Most others reporting the same error message were doing dynamic queries with variables, which I'm not doing. So, does anybody have a clue what I'm doing wrong?
Thanks,
MKE Data Guy
October 22, 2013 at 6:19 am
So, I thought, I'd post a reply to my own question. 'Cause I think I figured out what was going on, and I wanted to share it...
I believe that the OLE DB Error was coming from the fact that I wasn't using all the input columns to a OLE DB Command. The runtime must have noticed that I had columns coming into the command that I was ignoring. So, it raised errors... well, warnings really... that there were input columns which weren't being mapped.
Then, I wasn't seeing the results of the OLE DB Command because the parameters in the WHERE clause were being mapped to the wrong input columns. Doh! Once I figured out the correct mapping I got the results I was looking for. The parameter names used in OLE DB Commands really aren't very intuitive at all. Param_0, Param_1, ... Really?
So, there you have it. Once I fixed those two issues, everything works fine without warnings or errors.
I hope anybody else who encounters this scenario will find this useful.
Thanks,
MKE Data Guy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply