.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 data flow Data Types vs. SQL Server Data Types

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

Frequently Asked Questions - SQL Server Data Types

This is good and precise introduction and Frequently Asked Questions - SQL Server Data Types

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 task not loading all rows from sybase server



Using a data flow task I am trying to load data from a table in sybase server to sql server. There are approx 10000 rows in the sybase table, however for some reason which I am not able to find out, at random certain no of rows are getting inserted. e.g. in one run 800 rows then 200 rows etc.

Can somebody please guide me on this?

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

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?


Emergency.......Loding data into DB2 from SQL Server 2005 through SSIS data flow task????????



When I'm trying to load the data from SQL Server into DB2 database. It's trowing an below error. Can anyone help me what would be the possible resaon and workaround for this. ANy help would be much much appreciated.

Error: [OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "IBM OLE DB Provider for DB2"  Hresult: 0x8000FFFF  Description: " CLI0125E  Function sequence error. SQLSTATE=HY010"

What exactly I'm doing is ......Loading data into

Loading the data from sql server into the view on DB2

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


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

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



Hello People,

               Well i am just trying to test interoperability between ASP.net And Java web services, wanted to know what are the INTEROPERABILITY issues concering DATA TYPES that are there between the 2?...any thoughts? 

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!

Global Variables and data types

Hi, I have a small issue with the datatype problems. This is what my scenario is. I have a global variable "Qtr" declared as "Int32" datatype. Next, i have a ActiveXscript with VBScript Language. With the below piece of code. All it does, it will calculate and assign the calculated value to the global variable "Qtr". But due to some datatype conversion problem it is not displaying the Calculated value. It is showing null. But the value in the local variable is getting displayed. Can anyone figure out what the problem is all about. I have the option of taking the String datatype for the global variable but in the next step in the workflow , we have an stored procedure call i.e. an execute sql task where we will be call a stored procedure EXEC sp ? where the parameter datatype is an INT in the database. That is why i am finding problem.  I have used the work around i.e. using SSIS advanced expressions and avoided the parameter mapping and all. Function Main() 'msgbox("test") Dim Qtr Dim currentYear 'Constants qYears = 3 Qtr= (currentYear - qYears) * 10 + 4 msgbox(Qtr) msgbox("Assigning the value to the variable") DTSGlobalVariables("Qstart").Value =  Cint(Qtr) msgbox("Display Value in the variable") MsgBox(DTSGlobalVariables("Qtr").Value)      End Function Can a

Storing multiple data types in a class?

I need help writing a class that will contain three values per item.  I'll be passing parameters to a report: Parameter Name, Data type (int, string, date, etc), and Value. When I pass those values to the report using the class they should be in original form (i.e. an integer should be a numeric, a string should be a string, etc.).  I can do it converting the data's value to strings on one end, and reconverting to the original data type on the other, which seems like a lot of runaround. Is there a simple way?  Also, if I'm able to store & retrieve the data in it's native format I don't need the "Data type" stored. Any help or tips will be appreciated.

What does strategy exist to deploy SSIS package and my own data flow components into a enterparise s

I created a SSIS package and several data flow componenets for this package.    What does strategy exist to deploy SSIS package and data flow components into a enterparise server?   Thanks in advance.

The data types varchar and date are incompatible in the add operator

On the following query I keep getting the error 'The data types varchar and date are incompatible in the add operator'.  As you can see I have tried to convert the date but doesn't seem to work.  The variable @Date1 will start off as a date.  Any suggestions? ' declare @Date1 date declare @SQL1 varchar(2000) set @Date1 = convert(varchar, cast(getdate() as date), 102) set @SQL1 = 'select * from tbl_1 where convert(varchar,cast(tbl_1.Current_dt as date),102) = '+@Date1+'' print @SQL1    LISA86

Assembly items don't show in SSIS Data Flow

I successfully add a new assembly (MapPoint Batch Geocoder.dll) into GAC using Mscorcfg.msc and I can see it from the assembly list and I've added the dll into SSIS's PipelineComponents folder but for some reason I just cannot see it from SSIS Data Flow Item tab, I saw some others have the similar issue, and I actually tried to fix it based on their solutions such as change the framework version, but nothing changed. I am using VS2008, .NET 3.5 framework. I don't understand how come I can see it from GAC and also can see it through .NET tab in "Add References" but just not from Data Flow Item tab? Any ideas? Thanks a lot!


Hi Folks, I downloaded SQL 2008 and installed recently.Everything perfectly works except one thing that I can't use VARCHAR AND CHAR data types anymore.I was using SQL 2005 and I had the same problem there as well. Please helpe to solve this problem
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