{"id":1360,"date":"2012-07-02T00:00:00","date_gmt":"2012-07-02T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/working-with-ssis-data-types\/"},"modified":"2021-06-03T16:45:25","modified_gmt":"2021-06-03T16:45:25","slug":"working-with-ssis-data-types","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/working-with-ssis-data-types\/","title":{"rendered":"Working with SSIS Data Types"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">Most SQL Server Integration Services (SSIS) packages are created to extract data from one or more sources, transform that data, and load it into one or more destinations. Throughout this process, SSIS uses its own set of data types to move, manage, and manipulate the data before loading it into its final destination. These data types are specific to SSIS and are not the same as those supported by the SQL Server database engine or by other database systems. <\/p>\n<p>Although SSIS data types are different from the SQL Server ones, many of the SSIS types correspond directly to the Transact-SQL types. However, it&#8217;s not a one-to-one mapping. SSIS also includes data types that support other database systems such as Jet, DB2, and Oracle. Plus SSIS types support data from Excel spreadsheets, comma-separated values (CSV) files, text files, directory services, and other sources. <\/p>\n<p>Because SSIS types are independent from the other systems, each SSIS type can map to a variety of types in those systems. For example, SSIS does not include geospatial data types such as those you find in SQL Server. Instead, SSIS uses an image type that is specifically mapped to the geospatial types. Yet the image type is not limited to those geospatial types. It is also mapped to other data types in the various systems. <\/p>\n<p>The data types supported by SSIS can be divided into the following categories: <\/p>\n<ul>\n<li><strong>Numeric<\/strong><strong>:<\/strong> Types that support numeric values formatted as currencies, decimals, and signed and unsigned integers. SSIS supports more numeric types than any other kind.  <\/li>\n<li><strong>String<\/strong><strong>:<\/strong> Types that support ANSI and Unicode character strings.  <\/li>\n<li><strong>Date\/Time<\/strong><strong>:<\/strong> Types that support date values, time values, or both in various formats.  <\/li>\n<li><strong>Binary<\/strong><strong>:<\/strong> Types that support binary and image values.  <\/li>\n<li><strong>Boolean<\/strong><strong>:<\/strong> A type to handle Boolean values.  <\/li>\n<li><strong>Identifier<\/strong><strong>:<\/strong> A type to handle globally unique identifiers (GUIDs). <\/li>\n<\/ul>\n<p>A complete list of the SSIS types and an explanation of each one are beyond the scope of this article, but you can find details about all the SSIS types in the topic &#8220;<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms141036.aspx\">Integration Services Data Types<\/a>&#8221; in SQL Server Books Online. In the meantime, let&#8217;s look at some examples of data types being used in an SSIS package to better understand how they work. The examples retrieve data from the <strong>Production.Product<\/strong> table in the <strong>AdventureWorks2012<\/strong> sample database (on a local instance of SQL Server 2012) and insert data into the <strong>dbo.AWProducts<\/strong> table, which I created in the same database. If you want to try out these examples yourself, you&#8217;ll first need to run the following Transact-SQL code to create the <strong>AWProducts<\/strong> table: <\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('dbo.AWProducts') IS NOT NULL\nDROP TABLE dbo.AWProducts;\nGO\n\nCREATE TABLE dbo.AWProducts\n(\n&#160;&#160;NewProdID NVARCHAR(40) NOT NULL PRIMARY KEY,\n&#160;&#160;OldProdID INT NOT NULL,\n&#160;&#160;ProdName NVARCHAR(50) NOT NULL,\n&#160;&#160;MakeFlag BIT NOT NULL,\n&#160;&#160;ReorderPoint SMALLINT NOT NULL,\n&#160;&#160;ProdPrice MONEY NOT NULL,\n&#160;&#160;Weight DECIMAL(8,2) NULL,\n&#160;&#160;ProductLine NCHAR(2) NULL,\n&#160;&#160;SellStartDate DATETIME NOT NULL,\n&#160;&#160;EndDate DATE NULL,\n&#160;&#160;OldProdGUID UNIQUEIDENTIFIER NOT NULL\n); <\/pre>\n<p>The package I use to demonstrate the SSIS types is very basic and includes only the following components: <\/p>\n<ul>\n<li>An <strong>OLE DB<\/strong> connection manager to retrieve data from and insert data into the <strong>AdventureWorks2012<\/strong> database.  <\/li>\n<li>A <strong>Data Flow<\/strong> task that contains the components necessary to extract, transform, and load the product data.  <\/li>\n<li>An <strong>OLE DB<\/strong> source that retrieves data from the <strong>Production.Product<\/strong> table in the <strong>AdventureWorks2012<\/strong> database. The source uses the <strong>OLE DB<\/strong> connection manager to connect to the database.  <\/li>\n<li>A <strong>Data Conversion<\/strong> transformation that converts two columns in the data flow.  <\/li>\n<li>A <strong>Derived Column<\/strong> transformation that creates a column based on concatenated data from columns in the data flow.  <\/li>\n<li>An <strong>OLE DB<\/strong> destination that inserts data into the <strong>dbo.AWProducts<\/strong> table. The destination uses the <strong>OLE DB<\/strong> connection manager to connect to the database. <\/li>\n<\/ul>\n<p>As we progress through the article, we&#8217;ll look at the package&#8217;s components in more detail in order to examine how the SSIS data types are being used. Note, however, that the components, for the most part, use the default configuration settings, so it is fairly straightforward to set them up. Figure 1 shows what the data flow should look like once you&#8217;ve added and configured all your components. <\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image001.jpg\" alt=\"1516-image001.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1: Setting up your SSIS data flow <\/p>\n<p>As you can see, there&#8217;s a single connection manager, which I&#8217;ve named <strong>AdventureWorks2012<\/strong>, and four data flow components, all included in a single <strong>Data Flow<\/strong> task. Now let&#8217;s look at these components in more detail so we can better understand how SSIS data types are used in each of them. <\/p>\n<h2>Implicit Conversions from Source Data <\/h2>\n<p>When you retrieve data from a data source, the data enters the package&#8217;s data flow, at which time it is implicitly converted into SSIS types. Those conversions are defined in a set of XML data type mapping files that are located on the file system where SQL Server and SSIS are installed. In SQL Server 2012, the files are located by default in the <strong>C:\\Program Files\\Microsoft SQL Server\\110\\DTS\\MappingFiles<\/strong> folder. It is well worth reviewing these files to get a sense of how data types are being mapped in order to facilitate data flowing in and out of an SSIS package. <\/p>\n<p>Each mapping file includes a set of XML elements that define the specific mappings between data source types and SSIS types. For example, the XML file that maps SQL Server data types to SSIS data types is <strong>MSSQLToSSIS10.XML<\/strong>. The file contains a set of mappings that each map a SQL Server type to an SSIS type. One of these mappings is for the SQL Server <strong>XML<\/strong> data type, which is mapped to the SSIS <strong>DT_WSTR<\/strong> data type, as shown in the following XML fragment: <\/p>\n<pre class=\"prettyprint\">&lt;!-- xml --&gt;\n&lt;dtm:DataTypeMapping &gt;\n  &lt;dtm:SourceDataType&gt;\n    &lt;dtm:DataTypeName&gt;xml&lt;\/dtm:DataTypeName&gt;\n  &lt;\/dtm:SourceDataType&gt;\n  &lt;dtm:DestinationDataType&gt;\n    &lt;dtm:CharacterStringType&gt;\n      &lt;dtm:DataTypeName&gt;DT_WSTR&lt;\/dtm:DataTypeName&gt;\n      &lt;dtm:UseSourceLength\/&gt;\n    &lt;\/dtm:CharacterStringType&gt;\n  &lt;\/dtm:DestinationDataType&gt;\n&lt;\/dtm:DataTypeMapping&gt;<\/pre>\n<p>When an SSIS package retrieves data from a SQL Server database, it references the <strong>MSSQLToSSIS10.XML<\/strong> mapping file. If the source data includes an <strong>XML<\/strong> column, it will use the conversion defined in this XML fragment to convert the <strong>XML<\/strong> column to the <strong>DT_WSTR<\/strong> type in SSIS, which is a string type that supports Unicode values. <\/p>\n<p>Now let&#8217;s look at the <strong>OLE DB<\/strong> source shown in Figure 1. The component uses the <strong>AdventureWorks2012<\/strong> connection manager to connect to the <strong>AdventureWorks2012<\/strong> database. In addition, it uses the following <strong>SELECT<\/strong> statement to retrieve data from the <strong>Production.Product<\/strong> table: <\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160;&#160;ProductID,\n&#160;&#160;Name,\n&#160;&#160;MakeFlag,\n&#160;&#160;ReorderPoint,\n&#160;&#160;ListPrice,\n&#160;&#160;Weight,\n&#160;&#160;ProductLine,\n&#160;&#160;SellStartDate,\n&#160;&#160;CONVERT(DATE, SellEndDate, 22) AS EndDate,\n&#160;&#160;rowguid\nFROM\n&#160;&#160;Production.Product; <\/pre>\n<p>After you&#8217;ve set up your <strong>OLE DB<\/strong> source, you can verify how the SQL Server types will be converted to SSIS types by using the advanced editor associated with that component. You launch the editor by right-clicking the component and then clicking <strong>Show Advanced Editor<\/strong>. This opens the <strong>Advanced Editor for OLE DB Source<\/strong> dialog box. Go to the <strong>Input and Output Properties<\/strong> tab and, in the <strong>Inputs and Outputs<\/strong> box, navigate to the <strong>Output Columns<\/strong> node, as shown in Figure 2. <\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image002.jpg\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image002small.jpg\" alt=\"1516-image002small.jpg\" \/><\/a> <\/p>\n<p class=\"caption\">Figure 2: Properties for the ProductID column <\/p>\n<p>Expand the <strong>Output Columns<\/strong> node and select the <strong>ProductID<\/strong> column. The properties for that column appear in the grid to the right. These properties are specific to the SSIS data flow. As you can see in Figure 2, one of those properties is <strong>DataType<\/strong>, and its value is <strong>four-byte signed integer [DT_I4]<\/strong>. The name of the type is <strong>DT_I4<\/strong>, which is how it&#8217;s usually referenced in SSIS. However, in some cases, as in here, a description is also provided, which makes it handy to understand the exact nature of the type. Keep in mind, however, that the data is being retrieved from the <strong>ProductID<\/strong> column in the <strong>Production.Product<\/strong> table in SQL Server. That column is configured with the <strong>INT<\/strong> data type. That means SSIS is converting the type from the SQL Server <strong>INT<\/strong> type to the SSIS <strong>DT_I4<\/strong> type. <\/p>\n<p>Now look at the properties in the <strong>Name<\/strong> column (shown in Figure 3). Notice that the <strong>DataType<\/strong> property now has a value of <strong>Unicode string [DT_WSTR]<\/strong>. In this case, the source data comes from the SQL Server <strong>Name<\/strong> column, which is configured with the <strong>NVARCHAR<\/strong> data type. Again, SSIS has implicitly converted the source data to the SSIS type. <\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image003.jpg\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image003small.jpg\" alt=\"1516-image003small.jpg\" \/><\/a> <\/p>\n<p class=\"caption\">Figure 3: Properties for the Name column <\/p>\n<p>In fact, each column in the data flow has been implicitly converted to an SSIS type. Table 1 shows the data type of the source data and the SSIS type in which it has been converted. <\/p>\n<div>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>Column<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SQL Server data type<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SSIS data type<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>INT<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_I4<\/b> (four-byte signed integer)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Name<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NVARCHAR(50)<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_WSTR<\/b> (Unicode string)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>MakeFlag<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>BIT<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_BOOL<\/b> (Boolean)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ReorderPoint<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SMALLINT<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_I2<\/b> (two-byte signed integer)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ListPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>MONEY<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_CY<\/b> (currency)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Weight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DECIMAL(8,2)<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_NUMERIC<\/b> (numeric)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProductLine<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NCHAR(2)<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_WSTR<\/b> (Unicode string)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>SellStartDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DATETIME<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_DBTIMESTAMP<\/b> (database timestamp)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>EndDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DATE<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_DBDATE<\/b> (database date) <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>rowguid<\/b> <\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>UNIQUEIDENTIFIER<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_GUID<\/b> (unique identifier)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p class=\"caption\">Table 1: Implicit conversions from SQL Server to SSIS <\/p>\n<p>Notice that each SQL Server type has been converted to an SSIS type and that those types correspond with each other in a way that permits the data values to pass seamlessly between the database and SSIS. If you were to refer to the <strong>MSSQLToSSIS10.XML<\/strong> mapping file, you would see that all the mappings have been defined there. Yet even as efficient as this system is, there might be some cases in which you&#8217;ll want to configure a column with a different SSIS data type, in which case you can perform an explicit conversion. <\/p>\n<h2>Explicit Conversions of SSIS Types <\/h2>\n<p>Two common ways to convert data in your data flow is to use the <strong>Data Conversion<\/strong> transformation to perform a simple conversion or to use the <strong>Derived Column<\/strong> transformation to create a column that is based on converted data. Let&#8217;s first look at the <strong>Data Conversion<\/strong> transformation. <\/p>\n<p>In our example package, we&#8217;ve added a <strong>Data Conversion<\/strong> transformation to convert two columns: <strong>rowguid<\/strong> and <strong>ListPrice<\/strong>. If you refer back to Table 1, you&#8217;ll see that the <strong>rowguid<\/strong> column, as it is rendered in the SSIS data flow, is configured with the <strong>DT_GUID<\/strong> data type, which is the only SSIS unique identifier type. For this example, you&#8217;ll convert the column to an ANSI string type (<strong>DT_STR<\/strong>) and change the column name to <strong>OldProdGUID<\/strong>. As for the <strong>ListPrice<\/strong> column, it&#8217;s configured with the currency data type (<strong>DT_CY<\/strong>). You&#8217;ll convert that column to an eight-byte signed integer (<strong>DT_I8<\/strong>) and rename it <strong>ProdPrice<\/strong>. Figure 4 shows how your conversions should appear in the <strong>Data Conversion Transformation Editor<\/strong>. <\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image004.jpg\" alt=\"1516-image004.jpg\" \/><\/p>\n<p class=\"caption\">Figure 4: Converting columns to different SSIS data types <\/p>\n<p>Notice that when you convert the <strong>rowguid<\/strong> column to a string data type, you specify a length. Just as in SQL Server, you must specify a length for any of the character data types. That means you must ensure that the length is long enough to accommodate all the values in the table. Otherwise, SSIS will try to truncate the data. (By default, a truncation causes the transformation to generate an error, although you can override this behavior by redirecting your error output.) <\/p>\n<p>As noted above, another way you can convert data is to you use the <strong>Derived Column<\/strong> transformation. In this case, you&#8217;re converting data as part of the expression you use to define the column. For example, suppose you want to concatenate the <strong>ProductID<\/strong> and <strong>Name<\/strong> columns. However, the <strong>ProductID<\/strong> column is configured with the <strong>DT_I4<\/strong> data type, and the <strong>Name<\/strong> column is configured with the <strong>DT_WSTR<\/strong> data type. To concatenate values from these two columns, you need to first convert the <strong>ProductID<\/strong> column to a string, as shown in the following expression: <\/p>\n<pre>(DT_WSTR,4)ProductID + SUBSTRING(Name,1,4) <\/pre>\n<p>To convert a column in this way, you first specify the new data type and length, in parentheses, and then the column name. You can then concatenate this value with the <strong>Name<\/strong> column. In this case, you&#8217;re using the <strong>SUBSTRING<\/strong> function to specify that only the first four letters of the name be used. <\/p>\n<p>The column expression is one of the values you define when we use the <strong>Derived Column<\/strong> transformation to create a column. Figure 5 shows how to configure the other values. <\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image005.jpg\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image005small.jpg\" alt=\"1516-image005small.jpg\" \/><\/a> <\/p>\n<p class=\"caption\">Figure 5: Converting a column when creating a derived column <\/p>\n<p>As the figure shows, a new column named <strong>NewProdID<\/strong> is being added to the data flow. The column is configured with the Unicode string data type (<strong>DT_WSTR<\/strong>) and its length is set to <strong>8<\/strong>. <\/p>\n<h2>Implicit Conversions to Destination Data <\/h2>\n<p>After you&#8217;ve transformed the data in the data flow, you&#8217;re ready to load it into your target destination. However, you might want to first verify the columns in your data flow. To do so, you can use the <strong>Data Flow Path Editor<\/strong> for the data flow path connecting the <strong>Derived Column<\/strong> transformation to the <strong>OLE DB<\/strong> destination. When you open the editor, go to the <strong>Metadata<\/strong> page, as shown in Figure 6. <\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image006.jpg\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1516-image006small.jpg\" alt=\"1516-image006small.jpg\" \/><\/a> <\/p>\n<p class=\"caption\">Figure 6: Available columns in the SSIS data flow <\/p>\n<p>As you can see, all columns and their data types are listed in the editor. That includes the two columns that have been converted, both before and after that conversion. Also included in the information is the value&#8217;s length, where applicable. If precision and scale are factors in any of your columns, those too are included. <\/p>\n<p>Once you&#8217;re satisfied that the data looks correct, you can configure the destination. If you&#8217;re inserting data into a SQL Server database, there should be little problem, unless the data itself does not conform to the target type. For instance, an incorrectly formatted GUID might cause the destination component to generate an error when loading the data into a target column configured with the <strong>UNIQUEIDENTIFIER<\/strong> type. <\/p>\n<p>Table 2 shows how the data types in the SSIS data flow correspond to the data types in the <strong>AWProducts<\/strong> table. As you can see, not all the SSIS types correlate directly to those of the target columns. (The <strong>ListPrice<\/strong> and <strong>rowguid<\/strong> columns are not inserted into the destination, only the converted columns: <strong>ProdPrice<\/strong> and <strong>OldProdGUID<\/strong>.) <\/p>\n<div>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>SSIS Column<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SSIS data type<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Database column<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SQL Server data type<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProductID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_I4<\/b> (four-byte signed integer)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>OldProdID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>INT<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Name<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_WSTR<\/b> (Unicode string)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>ProdName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NVARCHAR(50)<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>MakeFlag<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_BOOL<\/b> (Boolean)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>MakeFlag<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>BIT<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ReorderPoint<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_I2<\/b> (two-byte signed integer)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>ReorderPoint<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SMALLINT<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ListPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_CY<\/b> (currency)<\/p>\n<\/td>\n<td valign=\"top\"> <\/td>\n<td valign=\"top\"> <\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Weight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_NUMERIC<\/b> (numeric)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Weight<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DECIMAL(8,2)<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProductLine<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_WSTR<\/b> (Unicode string)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>ProductLine<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NCHAR(2)<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>SellStartDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_DBTIMESTAMP<\/b> (database timestamp)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>SellStartDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DATETIME<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>EndDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_DBDATE<\/b> (database date)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>EndDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DATE<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>rowguid<\/b> <\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_GUID<\/b> (unique identifier)<\/p>\n<\/td>\n<td valign=\"top\"> <\/td>\n<td valign=\"top\"> <\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>OldProdGUID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_STR<\/b> (ANSI character string)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>OldProdGUID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>UNIQUEIDENTIFIER<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProdPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_I8<\/b> (Eight-byte signed integer)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>ProdPrice<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>MONEY<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>NewProdID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>DT_WSTR<\/b> (Unicode string)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NewProdID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>NVARCHAR(40)<\/b><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p class=\"caption\">Table 2: Implicit conversions from SSIS to SQL Server <\/p>\n<p>For example, the <strong>OldProdGUID<\/strong> column in the data flow is configured with the <strong>DT_STR<\/strong> data type, but the table column is configured with the <strong>UNIQUEIDENTIFIER<\/strong> type. At the same time, the applicable XML mapping file in this case, <strong>SSIS10ToMSSQL.XML<\/strong>, does not include a mapping for the <strong>DT_STR<\/strong> SSIS type to the <strong>UNIQUEIDENTIFIER<\/strong> SQL Server type. In such cases, SQL Server will usually accept the values and convert them to the <strong>UNIQUEIDENTIFIER<\/strong> type, as long as those values conform to requirements of the target type. If your destination won&#8217;t accept the values because of type incompatibility, then you might need to convert or transform your data flow in some other way to conform to the targeted data source. <\/p>\n<h2>Working with SSIS Data Types <\/h2>\n<p>Whenever you work with data in an SSIS package, you&#8217;re working with SSIS data types. It doesn&#8217;t matter where the data comes from or where it&#8217;s going. When you retrieve or load data, SSIS tries to automatically convert it to the correct types. If SSIS can&#8217;t implicitly convert the data-and transforming the data within the package doesn&#8217;t work-you might need to modify the XML mapping files, stage the data so it&#8217;s compatible with both SSIS and the data source, create a custom component that can retrieve or load the data, or implement a solution outside of SSIS to prepare the data. For the most part, however, you should find that the SSIS types provide you with the versatility&#160;you need to retrieve data from a variety of sources and load it into a variety of destinations. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In order to be able to take data from a variety of sources, manipulate it, and then export it to one of several destinations, SSIS has to use its own data types. Generally, the conversions of data types is handled implicitly, but if you hit problems, then you may need to intervene in one of several ways to ensure an appropriate conversion.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[5508,4179,4150,4306],"coauthors":[],"class_list":["post-1360","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-data-types","tag-source-control","tag-sql","tag-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1360","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1360"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1360\/revisions"}],"predecessor-version":[{"id":91228,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1360\/revisions\/91228"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1360"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}