Dynamically Creating Excel table through SSIS

Question

Using Execute SQL Task in SSIS - created Excel file- contains multiple columns and different data types.The issue here is INT/Money columns are displaying as a text columns in Excel even though excel table is created with Int/Money datatype

I have tried to used double precision,CY datatypes but nothing worked out.

CREATE TABLE `Employer` (
`MEMBERSHIP NUMBER` VARCHAR(30),
`RETIREE #` VARCHAR(12),
`COPID` CHAR(6),
`PERSON LAST NAME` VARCHAR(150),
`FIRST NAME` VARCHAR(150),
`RETIREE PLAN` CHAR(15),
`PLAN NAME` CHAR(200),
`BILL GROUP` INT,
`BRANCH ID` CHAR(3),
`CONTRACT NUMBER` CHAR(5),
`PBP` CHAR(3),
`BRANCH NAME` VARCHAR(150),
`COVERAGE MONTH` DATE,
`DUE AMOUNT` INT
)

Expected output should be in input datatype format.

You have to change `NumberFormat` within Excel files since data types are only used by SSIS. You've got an excellent answer describing that — Yahfoufi, May 24 '19 at 15:43
Yes! changing in excel is easy, user has to do manually but how can automate through system — Koti Raavi, May 25 '19 at 08:15

score 0 · Answer 1 · answered May 23 '19 at 21:34

OLE DB data types and Excel

Even if you can create Tables in Excel using OLE DB commands, excel files are not databases, where columns have data types. The OLE DB provider read only tabular data and need to specify a data type for each column. For this reason, you must specify columns data types when handling Excel, even if excel has the ability of storing multiple data types within the same column.

One of the disadvantages of using OLE DB provider to read Excel is that if a column contains mixed data types, it only reads the dominant data types and convert all remaining data types to NULL.

Excel Number Format property

On the other hand there are a Cell property in excel called Number Format which is used to change the way values are shown in Excel. You can use this property to show values as Currency.

Solution

You can use two approaches to solve the issue:

(1) Changes Column Number Format using Script Task

You can add a Script Task and change the entire column Number Format using Interop.Excel.dll assembly.

(2) Create an Excel template file

You can create an Excel file and change the columns Number Format as needed and use this file as template. You can copy the file the the destination directory, then perform data import phase.

Quick Start: Format numbers in a worksheet

I'm trying to use template still loading data as text instead of number. — Koti Raavi, May 24 '19 at 16:58
@KotiRaavi make sure you changed the entire column number format to currency — Hadi, May 24 '19 at 17:09
I'm exporting to excel .xlsx one, I have done same complete column in excel formatted to money and after loading to excel it is updating as "General" from number. format Thanks! — Koti Raavi, May 24 '19 at 17:32
@KotiRaavi then you should go to the first suggested solution, and add a script task at the end of the package — Hadi, May 24 '19 at 18:09