• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1192
  • Last Modified:

In SSIS, why I cannot create a new variable of Int16?

This doesn't make sense.  Am I doing it the wrong way?  Thanks.
0
thomaszhwang
Asked:
thomaszhwang
  • 10
  • 9
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
(DT_I2)Int16 datatype will use 2 bytes for storage and hence it is not equivalent to SQL Server datatype int which requires 4 bytes for storage.

If you want to match SQL Server int, then you need to use DT_I4 ie., Int32 which is 4-byte.
0
 
thomaszhwangAuthor Commented:
I'm using TINYINT for the equivalent column in the database.  Thanks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then you can map it with DT_I1 which is a 1 byte signed integer equivalent to tinyint datatype in SQL Server.
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
thomaszhwangAuthor Commented:
Sorry about the misunderstanding.  I'm talking about the variables.  Please see the screen shot attached.  As you can see, I have the option to set a variable to be Int16, but whenever I set it to Int16, it doesn't save correctly.  When I come back again, it changes back to Int32.  Please help.  Thanks.
SSIS.png
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> When I come back again, it changes back to Int32

Then either the variable is fetching value from some column in a table which has int datatype.

Int16 - Stores values from -32768 to 32767
Int32 - Stores values from -2,147,483,648 to 2,147,483,647

In the meanwhile are you passing values for Indicator_Id more than 32767 to get it automatically changed to Int32
0
 
thomaszhwangAuthor Commented:
It's actually variable Date_Row.

I don't think I'm passing any value into it.  It's a new variable.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Sorry for the confusion..
Kindly check the maximum value that would be passed for the variable Date_Row..
Posting some sample values would help confirm whether the value can be accomodated in Int16 or it requires Int32.
0
 
thomaszhwangAuthor Commented:
Thanks rrjeqan17.

I'm pretty sure the value that is going to pass into Date_Row would be either 0 or 2.  That's why I'm using TINYINT in the database.

On the other hand, I haven't started to pass the value into that variable yet.  It's a brand new variable.
0
 
thomaszhwangAuthor Commented:
I just tried on another machine.

I just created a new variable of Data Type Int16 and when the Variable window reloaded, it changed back to Int32.  This is not what it's supposed to be.  Really strange!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If it is a new variable, it should accept Int16 for Tinyint..
In the meanwhile, have you applied SP2 for SQL Server 2008
0
 
thomaszhwangAuthor Commented:
Well.  Actually I'm not sure.  Please see my SQL Server About screen.  Can you tell from that?  Thanks.
SQL-Server.png
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Service pack information can't be identified from the above screenshot.
Kindly run the below script to verify that:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
0
 
thomaszhwangAuthor Commented:
Please see the attached screen shot.  Thanks.
Version.png
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You seem to have SQL Server 2008 R2 Enterprise Edition..
SQL Server 2008 R2 doesn't have any Service pack available till now.

kindly try creating a new variable with Int16 datatype to check whether it is not accepted or not.
0
 
thomaszhwangAuthor Commented:
Unfortunately, the type of the new variable changed back to Int32.

It might be a bug I think.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, it might be..
But there is no harm in using Int32 as in SQL Server everyone uses it.
0
 
thomaszhwangAuthor Commented:
I chose to use an alternative and it works fine now.  Thanks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Request you to Post the alternate method you have tried which would be helpful for others in future..
0
 
thomaszhwangAuthor Commented:
Oh, sorry for the misunderstanding.  I still cannot use the Int16 type and the alternative I use only works in my project.  It cannot be universally applied.  Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now