SQL Server Integration Services Data Conversion Task to Convert Data Types<\/h2>\n
Let’s look at the Data Conversion task first…<\/p>\n
First, open Visual Studio (or Business Intelligence Dev Studio if you’re using pre SQL Server 2012) and create an SSIS project. Next, we can go ahead and configure a Connection Manager to our database. Right click Connection Managers in Solution Explorer and choose New Connection Manager:<\/p>\n
![\"SQL](\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-SSIS-Data-Type-Conversion.png\")
<\/a>
\nChoose your Connection Manager type. In this example, we’ll use OLEDB. Next, configure the Connection Manager to point to your dataset. In this example, I’ll use localhost and the AdventureWorks2008R2 database:
\n![\"SQL](\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-SSIS-Data-Type-Conversion-1.png\")
<\/a>
\nTest the connection and click OK. Next, drag a Data Flow task from the SSIS toolbox onto the design screen:<\/p>\n
\n<\/a>Right click the Data Flow task and choose Edit. You are now inside the data flow task. This is where all the action happens. Drag an OLEDB source task from the SSIS toolbox to the design screen:<\/p>\n<\/a>
\nRight click the OLEDB task and choose Edit. This screen is where we will define the Connection Manager we created earlier. Under OLEDB connection manager choose the connection you created. Leave data access mode as Table or view. Change the name of the table or the view to the table that contains the data types to change. In this example, I’ll use a table named Sales.CurrencyRate:<\/p>\n
\n<\/a>To preview the data click Preview. In my example, I want to change the CurrencyRateDate and the ModifiedDate columns from a datetime data type to a date data type:<\/p>\n<\/a>
\nClick OK to close the OLEDB Source task. Drag the Data Conversion Transformation task onto the design screen. Connect the OLEDB Source task to the Data Conversion task:<\/p>\n
\n<\/a>Right click the Data Conversion task and choose Edit. Here is where we will convert our data types. Since I am converting CurrencyRateDate and ModifiedDate I will click on each of them in the Available Input Columns list:<\/p>\n