SSIS Data from SQL Server into an Excel File Using SSIS

Question

Good day,

I have data like the below:

CREATE TABLE [dbo].[test_data](

[Local_Site] varchar NULL,

[LM_Company] [int] NULL,

[TotalStaff] [int] NOT NULL,

[NewOpp] nvarchar NULL,

[ExistOpp] nvarchar NULL,

[EasyTarget] [int] NOT NULL,

[GoldTarget] [int] NOT NULL,

[PremierTarget] [int] NOT NULL,

[BusinessTarget] [int] NOT NULL,

[COMMENTS] varchar NULL,

[SSCode] varchar NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT [dbo].[test_data] ([Local_Site], [LM_Company], [TotalStaff], [NewOpp], [ExistOpp], [EasyTarget], [GoldTarget], [PremierTarget], [BusinessTarget], [COMMENTS], [SSCode]) VALUES (N'Romey Park', 7860940, 3, N'3', N'0', 3, 0, 0, 0, NULL, NULL)

INSERT [dbo].[test_data] ([Local_Site], [LM_Company], [TotalStaff], [NewOpp], [ExistOpp], [EasyTarget], [GoldTarget], [PremierTarget], [BusinessTarget], [COMMENTS], [SSCode]) VALUES (N'Northvale', 7860940, 5, N'5', N'0', 5, 0, 0, 0, NULL, N'ss453890')

INSERT [dbo].[test_data] ([Local_Site], [LM_Company], [TotalStaff], [NewOpp], [ExistOpp], [EasyTarget], [GoldTarget], [PremierTarget], [BusinessTarget], [COMMENTS], [SSCode]) VALUES (N'Killarney', 7860940, 4, N'0', N'0', 0, 0, 0, 0, N'I called the client again to follow up and arrange a date for the presentation. The client''s response was that they would like to postpone.', N'ss334678')

INSERT [dbo].[test_data] ([Local_Site], [LM_Company], [TotalStaff], [NewOpp], [ExistOpp], [EasyTarget], [GoldTarget], [PremierTarget], [BusinessTarget], [COMMENTS], [SSCode]) VALUES (N'Douglasdale', 7860940, 4, N'0', N'0', 0, 0, 0, 0, NULL, N'ss457890')

GO

 

I would like to export this data to excel 2016 using an SSIS package.

 

How can I create the package so that the data types are correct in the excel columns, thus numeric column data should be 'number' format and non-numeric column data should be 'text' format

 

The result I get is the below (the numeric data is not of a number format):

 

 

 

Kindly advise

 

Thanks

 

Answer 1

SSIS Excel destination know values, but no format informations.

See Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)

=> 4. Known issues and limitations. => data types

Answer 2

Hi @Rontech10111,

The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).

The easiest way is converting the datatype from Excel by format the cell.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.