Creating dynamic excel table through Execute Sql Task - data types problem

• idogal

  Old Hand

  Points: 368

  More actions

  December 12, 2012 at 6:05 am

  #271827

  Hi,

  I'm using an SSIS package to create an Excel table and export the data from a corresponding SQL Server table into it. Because I wish to export all of the tables in the DB, I'm using dynamic scripts.

  The method is:

  1. generate CREATE TABLE string

  2. use this string in an Execute Sql Task with Excel CM - and create the Excel file.

  3. generate an INSERT INTO OPENROWSET... SELECT FROM ... command string.

  4. use this string in an Execute Sql Task with SQL SRV CM

  The problem is that the created excel file column type are all "general", even tho my script says otherwise. For example, the following script would create an excel file with "general" columns type:

  CREATE TABLE `CONTACT1`

  (

  [ACCOUNTNO] LongText,

  [COMPANY] LongText,

  [CREATEON] datetime,

  [CREATEAT] LongText,

  )

  Later on, when I try to use the insert into command, the command fails because of mismatch of the data type. An example:

  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\\junk\\accountnoWithNoApps.xlsx;HDR=YES;IMEX=1\"',

  'SELECT [accountno], [COMPANY], [CREATEON], [CREATEAT] FROM [CONTACT1$]')

  SELECT [accountno], [COMPANY], [CREATEON], [CREATEAT] FROM CONTACT1

  How can I solve this?

  Thank u in advance.

• sumit.joshij

  Ten Centuries

  Points: 1040

  More actions

  December 12, 2012 at 6:30 am

  #1567853

  you have to do it from script task.

• idogal

  Old Hand

  Points: 368

  More actions

  December 12, 2012 at 7:27 am

  #1567885

  Creating the excel file? populating it? or both?

  Thanks.