DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • JSON-Based Serialized LOB Pattern
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • SQL Commands: A Brief Guide
  • Kafka JDBC Source Connector for Large Data

Trending

  • How to Perform Custom Error Handling With ANTLR
  • How to Ensure Cross-Time Zone Data Integrity and Consistency in Global Data Pipelines
  • Secure by Design: Modernizing Authentication With Centralized Access and Adaptive Signals
  • Operational Principles, Architecture, Benefits, and Limitations of Artificial Intelligence Large Language Models
  1. DZone
  2. Data Engineering
  3. Data
  4. Data Conversion for SQL Server Integration Services (SSIS)

Data Conversion for SQL Server Integration Services (SSIS)

In the series of step by step lessons of SSIS (SQL Server Integration Services), this is part six in which we are going to learn a new control: Data Conversion.

By 
Rajat Jaiswal user avatar
Rajat Jaiswal
·
Sep. 15, 15 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
17.8K Views

Join the DZone community and get the full member experience.

Join For Free

In the series of step by step lessons of SSIS (SQL Server Integration Services), this is part six in which we are going to learn a new control: Data Conversion. I am sure that just by the name it's clear that this control will be used when we require data type conversion of input columns. This is exactly the same as CONVERT or CAST in our programming.

However, in this case it would be important to know that SSIS also has data types which are similar to other programming or database’s data types.

Just as DT_NUMERIC is equivalent to numeric data type, DT_I2 is equivalent to smallint, DT_str is equivalent to VARCHAR and so on.

I am sure you will find this control fairly simple, so without wasting any more time, let's start step by step.

For a change, I am using an excel data source.

Suppose we have an excel sheet of sales orders which have columns like sales order, subtotal, tax amount, and freight:

SaleOrderExcel

Here, the sales order is varchar, while SubTotal, Tax amount, and Freight are numeric with four decimal places. We want to convert the numeric field into an integer and want the result in a flat file. So let’s start.

Step 1

Drag and drop a Data flow Task control and double click it. Now add an excel data source from SSIS tool bar as shown in the below figure.

ExcelSource

Step 2

Now, the next step is to configure this excel source. To do this, just right click and use the EDIT option. You will get a screen where you need to provide the file that we want to access.

ExcelSourceStep2

Once the connection with excel is established the next very important step is to configure the sheet as highlighted in the below figure. Remember: You need to configure this sheet because an excel sheet may have the number of sheets available, so you will need to assign only the sheet whose data you want to process.

ExcelSource3

Step 3

Once the sheet is configured you can choose the columns, as well, which you want to process or want in the destination by clicking the column’s option as shown in the below figure.

ExcelSourceColumn

Step 4

Now drag drop the data conversion control from SSIS toolbar

dataConvrsionControl

Step 5

Now, open the context menu and click the edit button to configure data conversion. Now remember: Here we have to change the data from numeric to integer (as discussed earlier). So just change the desired data type which is integer (DT_I4). Also, a point to remember here is that when you do data type conversion you need to careful about Aliasing also. You will see copy of as a prefix on column.

Convesion

Step 6

Now, we need this output in flat file, so you can do so by adding a file destination control from SSIS toolbox and configuring it by clicking Edit button as shown in below figure.

FlatFile

Step 7

In the next step we have to configure the file location and columns which we require in the flat file, and delete any unnecessary columns which are not required.

deleteAccesscolumns

dataConvesionmapping

Step 8

Once the above step is done, we are good to go to run this package. So, hold your breath for few seconds (kidding!) and hit F5.

You will get the below screen which shows that all the provided rows are inserted in a file. 

result

Step 9

To cross check whether file has integer columns or not, let's open it.

CSVREsult

If you see above screen then you did it successfully. So we achieved our goal in this post and used DATA conversion control successfully.

I hope you feel this post useful.

Thanks for reading this post.

Data (computing) sql Data Types Database Integration

Published at DZone with permission of Rajat Jaiswal, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • JSON-Based Serialized LOB Pattern
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • SQL Commands: A Brief Guide
  • Kafka JDBC Source Connector for Large Data

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: