April 12, 2010 at 4:52 am
Hi
I'm new to SSIS. I've been trying to update from DTS with quite a lot of problems...
Everything works brilliantly in DTS but I don't understand the issues SSIS is presenting.
Using a Data Flow Task with Source and Destination: in the OLE DB Source Editor I have a SQL Command
which uses subqueries. They Parse fine and Preview fine (also work fine in SQL Server Management
Studio), but when I run the package, there are lots of errors - none of which I understand...
All the source tables are in the same database The SQL is in essence:
select col1, col2,code,
SectionTitle =
(select distinct LongDescription, from anothertable1 where id =
(select id from anothertable2 where code = table1.code))
from table1
inner join table2
When I click Run, the Source goes red. It doesn't get as far as the Destination. I have made the
SectionTitle column varchar(4000) in the destionation table.
Can anybody please help...
Error messages below:
[OLE DB Source [1]] Error: There was an error with output column "SectionTitle" (1008) on output
"OLE DB Source Output" (11). The column status returned was: "The value could not be converted
because of a potential loss of data.".
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column
"SectionTitle" (1008)" failed because error code 0xC0209072 occurred, and the error row disposition
on "output column "SectionTitle" (1008)" specifies failure on error. An error occurred on the
specified object of the specified component. There may be error messages posted before this with
more information about the failure.
Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source"
(1) returned error code 0xC0209029. 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: 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.
Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and
is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline
to shutdown. There may be error messages posted before this with more information on why the thread
was cancelled.
Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code
0xC0047039. There may be error messages posted before this with more information on why the thread
has exited.
April 12, 2010 at 4:55 am
What's the datatype/length of 'LongDescription'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2010 at 5:17 am
Hi Phil
Thanks for your quick reply.
The 'LongDescription' column is Text and allow Nulls. I changed my destination table to match, but no change in the outcome.
Many thanks
Paul
April 12, 2010 at 5:41 am
Try putting a data conversion task after your source component. Convert the text field to string(4000) and map the converted field to your destination.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2010 at 8:42 am
Hi Phil
I've just tried this and it appears to be correct in the Transformation Task. I changed the field types and length. Then in the Destination I deleted the earlier column mappings and linked up the new 'copy of' aliases created by the transformation - I hope this is correct...
Unfortunately it still only seems to get as far as the OLEDB Source and fails there (the only one to go red of the three - Source, Transformation and Destination)
Many thanks
Paul
April 12, 2010 at 8:50 am
Puzzling.
OK, please try opening the advanced editor for your data source.
Have a look at Input and Output properties. Check External and Output columns for SectionTitle - should be TextStream (at least that's what I've got in SSIS 2008).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 13, 2010 at 10:15 am
Hi Phil
Sorry for the late reply - I haven't had a chance to get near it...
I re-did the whole thing yet again to see if I could begin to understand the logic.
I changed the SQL to:
select col1 = null,
col2 = 'MyWord',
SectionTitle =
(select distinct LongDescription, from anothertable1 where id =
(select id from anothertable2 where code = table1.code))
And this time it worked, but I still don't get it... So I added another column at the end:
select col1 = null,
col2 = 'MyWord',
SectionTitle =
(select distinct LongDescription, from anothertable1 where id =
(select id from anothertable2 where code = table1.code))
col3 = null
I ran it again and it failed
I followed your helpful instructions and went into 'Input and Output Properties' of the Advanced Editor.
In the Source Output it shows:
External Columns:
The nulls dataType is 'four-byte signed integer [DT_I4]'
The text columns dataType is 'string [DT_STR]'
Output Columns:
Col1 dataType is 'four-byte signed integer [DT_I4]'
Col2 dataType is 'string [DT_STR]'
SectionTitle dataType is 'string [DT_STR]'
In the Destination Output it shows:
External Columns:
All columns dataType is 'string [DT_STR]'
I have tried changing the Destination datatypes - to no avail and putting in a data conversion task. And I still can't get it to work.
I can't quite get to grips with the logic of the data types, how they are chosen by the system and the required changes...
Error messages are:
[OLE DB Destination [3180]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Destination [3180]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "OLE DB Destination" (3180) failed the pre-execute phase and returned error code 0xC0202025.
Best wishes
Paul
April 13, 2010 at 10:55 am
Paul
i believe that it might be a typo but the SQL is missing a "," before Col3. Now the error describes metadata has not been updated meaning there was a change which was not updated.
I would suggest you try this.. Open up columns tab in Source and unselect all columns then Select them again and see If It works.. Also check the advanced editor again..Input and Output columns should have same datatypes..
select col1 = null,
col2 = 'MyWord',
SectionTitle =
(select distinct LongDescription, from anothertable1 where id =
(select id from anothertable2 where code = table1.code)),
col3 = null
April 14, 2010 at 3:30 am
Hi Phil
Yes, it was a typo. And yes what you suggested worked...
What I'm finding is that the data returned from the subquery (which uses the same field each time but with a different designation) is returning a different source type.
Sometimes the source type is 'string [DT_STR]' , sometimes 'text stream [BT-TEXT]. I must calculate the field type on the fly. The source data field, which is being used on all occasions is designated 'varchar 2000'.
I can't change the destination to 'string [DT_STR] 2000' , as it doesn't work. I can cast/convert the sql source to varchar(2000). At least it works!!
Something which seems strange to me is the type given to nulls - 'four-byte signed integer [DT_I4]'. Do you know the reasoning behind this.
One other thing... I believe I have the opportunity to move directly to SQL Server 2008 - which I think will require VS 2008 for SSIS... Would you recommend this rather than 2005? What might be the advantages and caveats. I'm using my laptop (Dell Latitude E5500) for development.
Many thanks for your help, Phil. I now have at least a working understanding of what is going on.
I'm very grateful for your time.
Paul
April 14, 2010 at 3:42 am
No idea about the NULLs datatype, sorry.
Regarding SQL Server 2008 - go for it. SSIS is not much different, but in some areas it is just plain better and more convenient. The knowledge you've picked up on 2005 will not be wasted. And if you prefer C# to VB, it's a must, as SSIS 2008 scripts support both.
With datatypes, you just have to watch what's going on through the entire pipeline - and the advanced editors let you do that. Rather than trying too hard to bend what SSIS is trying to make you do, use data conversion components where necessary to make your source data fit nicely into your destination.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 14, 2010 at 3:46 am
Thanks, Phil
I really appreciate your very good advice.
Best wishes
Paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply