Home Dashboard Directory Help
Search

SSIS: Data Types by Mike C_1


Status: 

Closed
 as Won't Fix Help for as Won't Fix


13
0
Sign in
to vote
Type: Suggestion
ID: 339068
Opened: 4/19/2008 4:43:34 PM
Access Restriction: Public
0
Workaround(s)
view

Description

SQL Server supports a very rich type system. SSIS supports most SQL Server types. Unfortunately SQL Server implements it's own C++ style naming convention for these data types, which is confusing to say the least. It makes packages harder to debug and maintain. SSIS Variables are a whole different story, since their data types have different names from both SQL Server and the other areas of SSIS. For instance, a VARCHAR in SQL Server represents a variable length character string data type. In SSIS this is referred to as a "String" for variables, and a "string [DT_STR]" for data flow components. A SQL INTEGER, which is a 32-bit integer value, is referred to as "Int32" for variables and "four-byte signed integer [DT_I4]" in data flow components.
Details
Sign in to post a comment.
Posted by Microsoft on 7/22/2010 at 10:46 AM
Thank you for contacting us. After analyzing this issue and prioritizing it in the context of our current development plans, we have found regretfully that it does not meet the bar to be included in an upcoming product release. If you feel that you could provide us with more data to change that judgment, we welcome any further information. Your feedback continues to be invaluable to us and we take all your communication seriously. Please continue to contact us with any questions, suggestions and issues as they arise.

     The SSIS Team

Posted by Todd McDermid on 10/13/2009 at 11:34 AM
It's understandable (to me) why the type system is "a mess" - SSIS is attempting to integrate type systems from multiple technologies (.Net, DB providers, etc...) and remain agnostic.
To that end, I would suggest the following few ways to ease the problem:
1. Change the SSIS variable type system from the ".Net" system to the DT_* system. The only problem I can see with this is that you will lose the instant familiarity that .Net devs would have, as they "know" those types already. However, the gain in removing a type system outweighs that.
2. If the above is done, I believe that the only other type systems in use come from the DB providers, and they're exposed in the parameter lists of Execute SQL Tasks. It would be great (and understandably difficult) to show the SSIS and DB type information in those areas - not just the DB type information. For example, instead of having "LONG" as a selection in the parameter type dropdown (because that's a DB type supplied to SSIS by the provider), show "DT_I8/LONG". Obviously the "mapping" of SSIS types to DB types is the issue - at worst it's a manual mapping table that the SSIS team must maintain and update (ugh). Hopefully there's a way to infer the type... as SSIS has to pass those variables anyway? Doing so would remove the confusion as to "what kind of variable do I need to store the OUTPUT parameter in?" as well as educating the end user as to what SSIS considers equivalent types.
3. Add type aliases to the SSIS .Net API to allow Scripts and Custom Objects to use DT_* types instead of .Net types. For example, alias DT_I4 to Int32, such that Scripts and Custom Objects can be written declaring variables using DT_I4 instead of "integer" or "Int32". This doesn't really "solve" anything - at some point somebody will have to decipher what a particular DT_* type is. However, this won't happen as often, as only the "hardcore" users will get to that level of detail when attempting to construct custom code. Inclusion of a type map in the standard Script comments would be useful too.
Posted by GuruSQL on 6/14/2008 at 10:00 AM
SSIS data type are the most stupid thing in SSIS, why null is no just null you have more them 4 type of null to choose from. I guess is time to take que geeks to produce good code, but with a clear mind to lead the way, because SSIS is a real mess

Regards
Posted by adolf garlic on 6/10/2008 at 5:30 AM
Why can't you just set some kind of compatability option to say "I only use sql server" or at least on a connection, get it to default any types to the recommended SSIS equivalent. As it stands, the SSIS own datatype feature will be used by the minority, but has been inflicted upon the majority.
Posted by Microsoft on 4/25/2008 at 2:16 PM
We have determined that we will not be able to include a correction for this issue in the current release. We have moved this issue forward to be considered for inclusion in the next release. Thank you for your submission and support of SSIS.
Posted by Mike C_1 on 4/19/2008 at 4:44 PM
Typo: "Unfortunately SSIS implements it's own C++ style naming convention..."
Sign in to post a workaround.