//
you're reading...
Database, IT-Center, SQL Server

SSIS Data Conversion

Data conversion :
SQL Server Data type Numeric —-> [DT_Numeric]
SQL Server Data type Varchar —–> DT_STR
SQL Server Data type NVarchar —–> Unicode String [DT_WSTR]

The following table provides guidance on mapping the data types used by certain databases to Integration Services data types. These mappings are summarized from the mapping files used by the SQL Server Import and Export Wizard when it imports data from these sources. For more information about these mapping files, see Using the SQL Server Import and Export Wizard to Move Data.

Important note Important
These mappings are not intended to represent strict equivalency, but only to provide guidance. In certain situations, you may need to use a different data type than the one shown in this table.
Data Type SQL Server

(SQLOLEDB; SQLNCLI10)

SQL Server (SqlClient) Jet Oracle

(OracleClient)

DB2

(DB2OLEDB)

DB2

(IBMDADB2)

DT_BOOL bit bit Bit
DT_BYTES binary, varbinary, timestamp binary, varbinary, timestamp BigBinary, VarBinary RAW
DT_CY smallmoney, money smallmoney, money Currency
DT_DATE
DT_DBDATE date date date date date
DT_DBTIME timestamp time time
DT_DBTIME2 time(p) time(p)
DT_DBTIMESTAMP datetime, smalldatetime datetime, smalldatetime DateTime TIMESTAMP, DATE, INTERVAL TIME, TIMESTAMP, DATE TIME, TIMESTAMP, DATE
DT_DBTIMESTAMP2 datetime2 datetime2 timestamp timestamp timestamp
DT_DBTIMESTAMPOFFSET datetimeoffset(p) datetimeoffset(p) timestampoffset timestamp,

varchar

timestamp,

varchar

DT_DECIMAL
DT_FILETIME
DT_GUID uniqueidentifier uniqueidentifier GUID
DT_I1
DT_I2 smallint smallint Short SMALLINT SMALLINT
DT_I4 int int Long INTEGER INTEGER
DT_I8 bigint bigint BIGINT BIGINT
DT_NUMERIC decimal, numeric decimal, numeric Decimal NUMBER, INT DECIMAL, NUMERIC DECIMAL, NUMERIC
DT_R4 real real Single REAL REAL
DT_R8 float float Double FLOAT, REAL FLOAT, DOUBLE FLOAT, DOUBLE
DT_STR char, varchar VarChar CHAR, VARCHAR CHAR, VARCHAR
DT_UI1 tinyint tinyint Byte
DT_UI2
DT_UI4
DT_UI8
DT_WSTR nchar, nvarchar, sql_variant, xml char, varchar, nchar, nvarchar, sql_variant, xml LongText CHAR, ROWID, VARCHAR2, NVARCHAR2, NCHAR GRAPHIC, VARGRAPHIC GRAPHIC, VARGRAPHIC
DT_IMAGE image image LongBinary LONG RAW, BLOB, LOBLOCATOR, BFILE, VARGRAPHIC, LONG VARGRAPHIC, user-defined CHAR () FOR BIT DATA, VARCHAR () FOR BIT DATA CHAR () FOR BIT DATA, VARCHAR () FOR BIT DATA, BLOB
DT_NTEXT ntext text, ntext LONG, CLOB, NCLOB, NVARCHAR, TEXT LONG VARCHAR, NCHAR, NVARCHAR, TEXT LONG VARCHAR, DBCLOB, NCHAR, NVARCHAR, TEXT
DT_TEXT text LONG VARCHAR FOR BIT DATA LONG VARCHAR FOR BIT DATA, CLOB

For information on mapping data types in the data flow, see Working with Data Types in the Data Flow.

 

See Also :
http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range
http://msdn.microsoft.com/en-us/library/ms141036.aspx
http://www.bimonkey.com/2009/06/the-data-conversion-transformation/ 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: