July 29, 2010 at 9:18 pm
Every night, we copy tables from a database to another using an SQL job. This job calls an SSIS package that uses the Transfer SQL Server Objects task to perform this job.
The problem is that the package fails at random times. However, when I run the job manually, everything works perfectly. I have enabled logging and it's giving this piece of information:
-----
[font="Courier New"]OnInformation,,,{E6196E86-0022-4A7F-8B17-B0D1BB8B4373},{19A4B14B-59EC-4049-93B5-5C91D91F8B51},14/07/2010 3:00:20 AM,14/07/2010 3:00:20 AM,1073935390,0x,Truncating table "ind_inducement"
OnError,,,Transfer SQL Server Objects Task,{D69E0C65-4617-42D3-BDA8-7BDC17B8DEF8},{19A4B14B-59EC-4049-93B5-5C91D91F8B51},14/07/2010 3:06:42 AM,14/07/2010 3:06:42 AM,-1073548507,0x,Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".[/font]
-----
I had a look at other times it failed, and it seems that this table "ind_inducement" is the main culprit.
However, this table contain 33 rows of static data that do not change. It's more like a utility table where parameters are defined. The table has rows that contains NULL data. But so does other tables and they did not fail.
So, I'm not too sure why the process is stuck 5 mins trying to truncate the table and after that giving me an "Invalid character value for cast specification" error.
Any idea?
The server is running the 64 bit version of SQL Server 2005.
July 30, 2010 at 7:02 am
Why not just copy the data you require? Deleting & recreating table objects repeatedly seems a little inefficient.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 30, 2010 at 7:45 am
My bad... What I meant is we copy data from tables using the Transfer SQL Server Objects task in SSIS.
Creating tables each time would be really inefficient.
July 30, 2010 at 8:31 am
OK ... may I ask what 'Transfer SQL Server Objects' gives you that a dataflow task does not? Just wondering why you went down this road.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 1, 2010 at 6:37 pm
Phil Parkin (7/30/2010)
OK ... may I ask what 'Transfer SQL Server Objects' gives you that a dataflow task does not? Just wondering why you went down this road.
I'm not too sure but it's an already existing package that's responsible for a critical process and it was designed that way. From what I see, it refreshes a selective list of approx. 500 tables from a large database to a smaller one.
How would you proceed using a dataflow task?
August 2, 2010 at 9:21 am
coalesce (8/1/2010)
Phil Parkin (7/30/2010)
OK ... may I ask what 'Transfer SQL Server Objects' gives you that a dataflow task does not? Just wondering why you went down this road.I'm not too sure but it's an already existing package that's responsible for a critical process and it was designed that way. From what I see, it refreshes a selective list of approx. 500 tables from a large database to a smaller one.
How would you proceed using a dataflow task?
Eek - 500 tables = 500 data flows! Maybe it's better leaving it how it is 🙂
BTW, if you were using a data flow for your errant table, you would be able to redirect the failing rows to another table & hence (probably) track down what is going wrong.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply