I created a simple ragged file with 3 lines:
1
2
3
and coupled it to a Flat File Source. The Flat File Source suggested that the type was DT-I1.
Next I created a “SQL Server Destination task and connected the output of the Flat File to the Sql Destination. In the Destination editor I created a table, whose one column was declared to be a sql smallint.
Here’s the problem: the Sql Server Destination now displays a red circle with a white “x”. The tooltip says that: “The column “Column 0” can’t be inserted because the conversion between types DT_I1 and DT_I2 is not supported”
Can anyone explain why the conversion is not supported
TIA,
Barkingdog
P.S. It's not kidding! When I changed the Flat File Source type to DT-I2 everything worked fine.
"...conversion between types DT_I1 and DT_I2 is not supported"
Dan Spalding
Hi,
Please could you rephrase that a bit I'm afraid the meaning got somewhat lost in translation :)
-Jamie
Wagner Traut
Now that I understand what happened I too agree that this behaviour is a great thing. The problem for begineers like myself is that these types of points are not obvious (they weren't true in sql 2000) so I stumble all over them and get confused. What is needed is a "facts of life" document for those with sql 2000 experience learning sql 2005.
TIA,
Barkingdog
bravipandey
So what you're saying is, "If you do something wrong, you'll know about it".
That's a good thing I reckon. You don't want the software to compensate for your shortcomings because any assumptions the software has to make may be wrong.
What do you think
-Jamie
BlizzGuy
I agree that a document about "migrating mindsets" from DTS to SSIS would be good. We had one in beta 1 - I'll need to dust it off and have it updated.
Donald
Ando182
The SQL Server Destination is a high-perf destination that uses in-memory interfaces to communicate with SQL Server. It's like being able to insert data directly to SQL Server's memory space, if you like. One consequence of this is that the data types must match exactly what SQL Server expects there.
If you need a more "tolerant" destination then the OLEDB destination will do some implicit conversions.
Donald
BTW - I have been asked many times privately why we are so focussed on features that saving performance at the expense of possible usability optimiztions. Think of it this way - every millisecond we add to the processing of a row, adds up to over 15 minutes when processing 1M rows. That's a lot, especially when we are seeing data integration processes regularly processing millions of rows every night. The extra time needed to design the package, is often much less than the extra processing time that could accumulate over weeks, months or years.
Amli
Regarding t-sql, dba programmers. They were used to working bad without seeing really the things that SQL did under it skin. When they were working with Sql 2000 in comparison with Sql 2005...
Sorry, my english is not my forte
joklahome
The SQL Server Destination is a high-perf destination that uses in-memory interfaces to communicate with SQL Server. It's like being able to insert data directly to SQL Server's memory space, if you like. One consequence of this is that the data types must match exactly what SQL Server expects there.
So then we were accostumed to do any sort of stuff without really know what the hell did