.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

(SSIS 2008) What are the precise numerical sizes (in bytes) of the date data types in SSIS?

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

I have been reading the famous Integration Services: Performance Tuning Techniques document and I want to use the guidance in the Buffer Sizing section.

In order to optimize my settings for DefaultMaxBufferRows and DefaultMaxBufferSize, I need to calculate the Estimated Row Size for my Data Source.

However, when I look to the Integration Services Data Types document I find that several of the data types do not explicitly list their size in bytes.

(DT_BOOL also isn't listed but the assumption must be it's one bit)

Does anyone know how big (in bytes) these data types are? The Estimated Row Size can't be found without them.


Peter Kral

View Complete Post

More Related Resource Links

2008 SSIS and NUMA Memory issues and BLOB Data Types


We're running SSIS 2008, moving data from an Oracle 10g database to a SQL 2008 database.  The SSIS is running on the same machine as the SQL Destination server.  The server has 8 gigs and is windows 2003 sp2

The issue we're having is when our package pulls a blob data type from oracle it will just quit with no errors at about 1.4 million records.   We know there are 6 millions records in the dataset. 

A friend of mine said it was a NUMA Memory issue that it is running out at some point and SSIS thinks the incoming data is finished.  Unfortunately, she said there was no answer. 

I was wondering if someone else had a simliar situation moving blobs from Oracle?


Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" 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. Meng Chew

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

SSIS data flow Data Types vs. SQL Server Data Types


How do I join a SSIS text field type cast from the Derived Column Transformation Editor with a varchar from an SQL data table in a lookup?

Also, is there any reference on which SSIS data flow datatypes can be JOINed to SQL Server datatypes?

Here's the problem:  I have records in a flat file.  The flat file name contains the data of the records.  When importing, I read the flat file name into a data field using SSIS.  Then, I use FINDSTRING to create a derived column that contains just the file date as YYYYMMDD.

There's a SQL Table that contains Currency Rates, indexed by currency code and date, in DD/MM/YYYY format, but it's stored as a VARCHAR

My problems:

A.) I tried to convert the string, '07302010' into the following formats, without success:





However, none of these can understand the string '20100730' as a date!


B.) Furthermore, if i convert the field into a DTW_STR, it still doesn't match the varchar(50) in my db.


So, basically, despite using all available typecast date formats, and several string formats as well, I can't find a way to

A.) Parse text out of a field

B.) Convert that text to either a DATE format or a TEXT format

C.) Use that resulting field to

SSIS 2008 data type bug



I am using SSIS 2008, I set a varaible @t1 to data type CHAR and set the value to Y next I used an Execute SQL Task with an ADO.NET connection to SQL 2008 R2 database to insert this the value of @t1 into a test table with only 1 column of data type NCHAR(1):

insert into test1

On the Parameter Mapping section of this task I put the Data Type as String and initially set the Parameter size to -1  

The only problem is it tries to insert a 2 character numeric value into the SQL table. If I set the value of @t1 to N the parameter mapping seems to convert this to 78, when I set the value of @t1 to Y the parameter mapping converts this to 89 it looks like it converts the character to an ASCII code.

Is this meant to be the intended behaviour, as all I wanted to do was insert the actual character into the destination column and not the ASCII code?



Incremental data load from MySQL database to SQL server 2008 using SSIS


I am looking for best solution to perform incremental data load from MySQL database to SQL Server 2008.  I tried to follow instructions from articles on incremental data load in SSIS  using MD5 hashes but I failed to make connection in Lookup transformation editor between MySQL Id (4 byte unsigned integer) and Id in SQl Server 2008 table which is  bigint. i tried to add  data conversion and convert MySQl Id to string. It works fine when you map this field directly to OLEDB destination but in Lookup component it failed. 


Any input greatly appriciated.



Visual Studio 2008 crashes on SSIS Data Flow Task opening


Visual Studio 2008 crashes on SSIS Data Flow Task opening.
This is a random behavior on random SSIS packages on Data Flow Task opening on packages that I've finished developing last week. The packages does not executes with error.

Error Description from Event Viewer:
Faulting application devenv.exe, version 9.0.30729.1, stamp 488f2b50, faulting module msdds.dll, version 9.0.30729.1, stamp 488f2e31, debug? 0, fault address 0x00012b6a.

I'm runnig SSIS development against SQL SERVER 2008 ENT 64.

There is already a fix for this ?

My environment description:
Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: Professional

Microsoft Visual Basic 2008 91605-270-4167253-60984
Microsoft Visual Basic 2008
Microsoft Visual C# 2008 91605-270-4167253-60984
Microsoft Visual C# 2008
Microsoft Visual Studio 2008 Tools for Office 91605-270-4167253-60984
Microsoft Visual Studio 2008 Tools for Office
Microsoft Visual Web Developer 2008 91605-270-4167253-60984
Microsoft Visual Web Developer 2008
Crystal Reports    AAJ60-G0MSA4K-68000CF
Crystal Reports Basic for Visual Studio 2008
KB944899, KB945282, KB946040, KB946308, KB946344, KB946581, KB947171, KB947173, KB947180, KB947180, KB947180, KB94

Variable types are strict, except for variables of type Object- SSIS 2008


Hi , I declared a user variable MINIODate  as string in package level and assigned this variable in an execute sql task 2008 , where my source is ado.net and sql command is Select CSTR(MIN(IODate) ) as MINIODate  from IOData ( This is source table available in MSAccess DB) , The above senario working fine in 2005. During the run time in 2008  i am getting the following error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MINIODate": "The type of the value being assigned to variable "User::MINIODate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

checking data types in SSIS

hi i have a "gender" column in my database in which o/1 value exists.i want to change 0 to male and 1 to female in SSIS.
2) i also have DOB table in which dates are in dd-mmm-yy format.i want to chechk some of them are erronous like 31-feb-90 ,22-apl-88,21/03/67.
I want to know method how to do remove these erronous data? how to convert it?
how to send dirty data to "error table" ???
need quick response plzzzzz

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

Issues with SSIS and SQL 2008 - Integration services not running

Hey guys, I installed SQL 2008 with complete BI studio including Integration Services. When i check in my services - i cant see my integraiton services in there When i try to connect to Integration Service using localhost, I cant connect it And when i try to run setup of SQL 2008 - It shows that Integration Services already installed and that what i checked when i fetched my Installation tool report from SQL Server to verify i have it installed. Why can i access Integration services, if i already have it, Please suggest with ideas, checks i can make to ensure every setting is in place. Thanks. Would appreciate for quick response.  

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend