April 7, 2008 at 10:55 am
Hello ,
I'm running an SSIS import package that inserts data into an OLE DB destination, after a lookup.
The basic procedure is (And I've attached a screen shot of it as well)
1] Extract from a table, from datasource residing on one server(lets call it d1)
2] Run the lookup against a reference dataset(d2), and here is the logic
if d1.id = d2.id then i insert the data from d1(city, state, zipcode etc) into d2.
So the problem here is , the lookup is all fine, BUT when i insert into d2 it seems to APPEND the data instead of inserting it on the fields where d1.id = d2.id
So although i have about 180 K rows in d2, after doing the insert i have 360 K rows (because it appends instead of joining it across the id).
I'm not sure how to work around the problem, especially since i am pulling data from 2 different servers....do let me know
Thanks guys for reading this!!
April 7, 2008 at 11:16 am
You have specified:
1) Get data from D1
2) See if data already exists in D2
3) If it already exists, INSERT it into D2 (add new rows)
What you seem to want is:
1) Get data from D1
2) See if data already exists in D2
3) If it already exists, UPDATE the data already in D2 (update existing rows)
You probably want to use an OLEDB Command object rather than an OLEDB destination object.
April 7, 2008 at 12:01 pm
Yeah, that makes sense, now wherein do i put in the command to update the data, is it in the sqlcommand section of the component properties tab? So basically the i need to join the output of lookup with the id in the destination table...
April 7, 2008 at 12:14 pm
In your data flow task - right where you attached the OLEDB destination component. Remove the destination component and from the toolbox in the Transformations section, drag out an OLEDB Command component.
The OLEDB Command component is a transformation because it has an output as well as an input, but you do not have to connect the output to anything if you do not want to.
You will have to configure a command with something like "UPDATE MyTable SET MyField = ? WHERE MyPKID = ?" and then wire up the parameter mapping correctly. I am sure you can find some articles on this site with detailed instructions if you have any trouble.
April 7, 2008 at 12:25 pm
Andy Leonard wrote an article on how to do incremental load. In it is a nice method for doing the updates in a set-based (putting the updates in the dataflow task - you end up with RBAR updates).
You might care to peruse the bottom half of the article (fine - scan the top half, too - it's also got some good stuff).
http://www.sqlservercentral.com/articles/SSIS/62063/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 1:00 pm
Thanks for the direction folks! i am getting what has to be done, just still trying to figure out how to update the destination tables with data from the lookup output and do a join.
April 7, 2008 at 6:27 pm
I get an error when i try to insert, i have attached the data flow and the error output message is as given below:(It's when its trying to convert the data from the source to destination.
SOURCE DESTINATION
city-varchar(255) varchar(max)
state-varchar(255) varchar(max)
zip-varchar(255) varchar(max)
Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [1997]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [1997]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1997)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
Please let me know , if anyone has any input..have been trying to figure this thing out for the past couple of hours with no breakthrough.................
I prolly would have avoided SSIS if i knew how to copy data from two different servers.
April 7, 2008 at 7:20 pm
I set the ole db command task to ignore errors, however i still get the following error on execution of the data flow task....it seems to me some error conversion mismatch..i will keep investigating...
Error: 0xC0202009 at Data Flow Task, OLE DB Command [1024]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".
Error: 0xC0202009 at Data Flow Task, OLE DB Command [1024]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".
Error: 0xC0202009 at Data Flow Task, OLE DB Command [1024]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".
January 20, 2009 at 5:35 am
How can i fix the insert anomaly
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply