I have configured my DataReader to use an ADO.net (ODBC) connectivity (entered Select * from AMPFM) in Sqlcommand and can see my database columns listed in the Advanced Editor / Column mappings window. My process needs to perform a straight column to column population from AMPFM table into my dbo.visitfinancials table. How do I point the output to the above table?
Add an OLE DB destination. Select the DataReader source, connect its green arrow to the OLE DB destination. Double click on the OLE DB destination and configure it for the appropriate connection manager (will have to click "New..." to create a new one) and table name. Be sure to use "Table or view - fast load" in the Data access mode drop down list.
Then select mappings on the left. Map your columns by dragging one to the other. When done, click OK. Run it. If your data types match, this is as easy as it gets.|||
That worked great, thanks Phil. Now of course, you knew my data types would not all match. I am getting the error "Column "ID" cannot convert between unicode and non-unicode string data types. I believe I came across one of your posts that stated to use a derived column to cast the field. My field (ID) is varchar (254) not null. Where would I enter this derived casting procecure?
|||In a derived column, add a new column.
This expression should do the trick.
(DT_STR,254,1252)[your_input_field]|||
Looks like I have ran into a snag. I navigated to DataReader Source / show advanced editor / Input and Output properties / Datareader Output / Output columns / clicked on the add column radio button and received the following error message:
Error at Data Flow task[DataReader Source[1]]: The component does not allow adding columns to this input or output. Additional information: Pipeline component has returned HRESULT error code 0xC0208019 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
Any ideas?
|||You need to use Derived Column transformation for that...it it an item in the toolbox in the data flow.|||
OK, I have added a derived column object from the toolbox, connected it to the datareader and then tried to connect it to my OLEDB data source but get an error saying there are no available inputs. I deleted the mapping for "ID" field between DataReader source and destination OLEDB and then tried to connect "ID" from derived column to the OLE DB data source adn get the same message. I feel like I'm getting close but not quite there yet. I need additiona assistance please.
|||You have the wrong OLE DB component. You have a source on there and you need the OLE DB DESTINATION component instead. It's toward the bottom of the list in the toolbox.|||
I believe I used the wrong terminology in my previous posts and confused the issue. I do have a DataReader source, Derived column control and OLEDB destination. Thanks.
|||The data reader source should be connected to the Derived Column, which should be connected to the OLEDB destination. Three boxes, two lines.
