3

I am trying to load a csv file in a SQL Server database table using SSIS. Currently before loading the file we are opening the file in excel and changing the format of the column B from General to Number. (if this is not done, incorrect data for that column is loaded)

e.g the 4th row below is date, but before loading we change the format of Column B from General to Number. which is then converted to value 42767 (correct data)

Column A Column B
-----------------  
1. 11622
2. IZED
3. DGA-435
4. 1/02/2017

Now we have to automate this process and using some logic, can this be done in SSIS? Please suggest.

Thanks, Aj

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ajay
  • 31
  • 2

1 Answers1

0

You can achieve this using a Vb.net (or c#) script and using Microsoft.office.Interop.Excel library

Note: you have to add Microsoft.Office.Interop.Excel.dll file to the following directories (.Net Framework dll directory) C:\Windows\Microsoft.NET\Framework\v2.0.50727 and (sql server data tools dll directory) C:\Program Files\Microsoft SQL Server\100\DTS\Binn (using visual studio 2005 and sql 2008) and then add this dll as a reference in your script task

Imports Microsoft.Office.Interop.Excel

Public Sub Main()

        Dim m_XlApp As Application 

         m_XlApp = New Application
         m_XlApp.visible = False
         m_XlApp.DisplayAlerts = False

         Dim m_xlWrkbs As Workbooks = m_XlApp.Workbooks
         Dim m_xlWrkb As Workbook
         m_xlWrkb = m_xlWrkbs.Open(strFile) 'strFile must contains the fule path ex: C:\1.xls

         m_xlWrkb.DoNotPromptForConvert = True

        Dim m_xlsheet As Worksheet = w.Sheets(1)

        'Change the second column to number
        m_xlsheet.Cells(1,2).EntireColumn.NumberFormat = "0"

        m_xlWrkb.Save()
        m_xlWrkb.Close(SaveChanges:=True)

        m_XlApp.Quit()


End Sub

Note: if you decide to achieve this using a script task you have to learn about adding custom References inside a script task

Links that may helps

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks for reply Hadi, but I am not sure how to use this. I tried putting this in the script in the script task, but I am not able to resolve the errors after that (I don't know VB.net as I am not from coding backgound). Could you please provide an example if possible. – Ajay Jun 23 '17 at 04:54
  • @Ajay i edited my answer, you have add `Microsoft.office.Interop.Excel` as reference to fix errors, also replace strFile variable with the full file path (you can pass it using package variable or manually) – Hadi Jun 24 '17 at 00:44