Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Export Column Transformation in SSIS

by suresh

Export Column Transformation in SSIS is useful to export images, binary files, media, or any large documents from SQL Server to file system. The SSIS Export Column Transformation reads the path or location present in the source column and transfers the data to that particular path.

For example, if we have a product description stored in the text file and the text file is stored in one column. We have a situation to send product descriptions only to any specific store. Then, we can use the SSIS Export Column Transformation to transfer and save those text files to the path we specified.

Export Column Transformation in SSIS is precisely opposite to Import Column Transformation. Please refer to Import Column Transformation in SSIS article to understand How to Import Images, Text files, or any huge file to SQL Server Database.

Export Column Transformation in SSIS Example

In this SSIS example, We will export images from the SQL Server table to a local hard drive. The following SQL table is the one we are going to use in this example.

SELECT [PhotoID]
      ,[PhotoSource]
      ,[Photo]
FROM [SSIS Tutorials].[dbo].[PHOTOSOURCE]
Export Column Transformation in SSIS 0

The Export Column Transformation in SSIS will store the images present in the Photo Column and store them in the location or path specified in the [PhotoSource] column.

STEP 1: Drag and drop the data flow task from the toolbox to control flow and name the data flow task as Export Column Transformation.

Export Column Transformation in SSIS 1

Double click on the data flow task will open the data flow tab.

STEP 2: Drag and drop OLE DB Source, Export Column Transformation from the toolbox to the data flow region.

Double click on OLE DB source in the data flow region will open the connection manager settings. Here we are selecting [SSIS Tutorials] Data Base and PHOTOSOURCE table as OLE DB Source.

Export Column Transformation in SSIS 2

STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Here we are unchecking the PhotoID key column. Because there is no point in saving the Identity column as output at this time

Export Column Transformation in SSIS 3

Click ok, and Drag and drop the blue arrow from the OLE DB source to Export Column Transformation to perform the transformations on the source data.

STEP 4: Double click on SSIS Export Column Transformation to edit and configure. It opens the window, as shown in the below screen.

Export Column Transformation in SSIS 4

Here we have option to select the column where the path to be places as. You can also find check boxes at the right side of SSIS Export Column Transformation.

  1. Extract Column: Select text or image data columns from the list of available input columns. In this example, We selected Photo because we are exporting images from SQL to the local hard drive.
  2. File Path Column: Select file paths or file names from the list of available input columns. It is the path where files or images will be saved (Here Images).
  3. Allow Append: Specify whether you want the Export Column Transformation to add data to existing files or not. The default is false, and you can change it if requires.
  4. Force Truncate: Specify whether you want the Export Column Transformation to delete any of the existing files before writing the new data. The default is false, and you can change if you require by checkmark the option.
  5. Write BOM: Specify whether you want the Export Column Transformation to write a byte-order mark (BOM) to the file. A BOM only print if the data has the DT_NTEXT or DT_WSTR data type.

Let’s look at our destination path before we run the SSIS Export Column Transformation package

Export Column Transformation in SSIS 5

Let us run the Export Column Transformation in SSIS package

Export Column Transformation in SSIS 6

Let’s see the destination folder, whether we succeeded or not.

Export Column Transformation in SSIS 7

Well, we did it.

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy