February 4, 2009 at 5:28 am
Hi there,
I have a very simple package which runs perfectly well on my client pc through BIDS, but only partially through BIDS or anything else (agent, SSIS package store) on the server. By 'partially runs ' I mean only 13,194 rows are imported instead of the total 16,733???
Now this is not as simple as it sounds as I am importing data from a very old system called DataEase through a linked table in an Access db via ODBC (LinkEase drivers). (I couldn't get it to work directly through ODBC in SSIS).
Anyway this is irrelevant since apart from the operating system everything (as far as I can tell)is the same as on my client PC.
I think I have tried everything including several workarounds without success, the error menage I getting is pretty generic and pretty unhelpful. Hopefully somebody may be able to help!?
Thanks in advance...
Error:
[DE_Accessdb_Written [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DE_Accessdb_Written" (1) returned error code 0xC0202009. 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.
[DTS.Pipeline] Error: 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.
etc...
February 4, 2009 at 9:56 am
Do you have SSIS installed on other pc's with latest services packs? So not just the SQL client like Management Studio but the actual SQL server Integration services.
February 4, 2009 at 1:36 pm
Hi there thanks for the reply.
Integration services is installed only on the server and is fully patched.
When I connect to Integration services(on server) through management Studio on the client and run the package it completes successfully.
When doing the same thing but on the server, it fails.
The same happens when running the package through BIDS in both scenarios.
February 4, 2009 at 7:15 pm
That does seem strange.
Is it the number of records imported (13,194), or a certain 'rogue' record that is causing the problem?
If you import the records into Access first (so that it is a physical Access table, not just a link) and then do the import, does the problem go away? Maybe you have some sort of permissions issue on the server (I know, sounds unlikely) that you are not experiencing on the client.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 5, 2009 at 2:34 am
I'm pretty sure its not a rogue record. When running on the client the rows imported (in batches of 2199) reach 13194 and then move to 15393... Its as if it times out or just loses the connection?
As you suggested my next step will be to import the table into access (rather than linked) and see what happens.
February 5, 2009 at 4:19 am
Right problem solved... well a work around has been found!
Since the LinkEase ODBC driver is old technology we decided to also use old technology and created a package using sql server 2000 DTS:D.
This seems to work direct via ODBC without having to use MS Access as a staging point.
So we will use the SSIS item 'Execute DTS 2000 Package Task' to import the required data.
Many thanks again to those that replied.
February 5, 2009 at 5:35 am
Sometimes it's easier just to accept that something works, rather than trying to understand why 🙂
Now you can move on to the next project: migration of DataEase data to SQL Server 2008 😀
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply