Change column format to CSV before uploading to SQL Server database using SSIS
I am trying to load a csv file into a SQL Server database table using SSIS. Currently, before uploading the file, we open the file in excel and change the format of column B from General to Number. (if not done, the wrong data is loaded for this column)
for example, the fourth row below is the 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 need to automate this process and use some logic, can this be done in SSIS? Please suggest.
Thanks, Aj
source to share
You can achieve this using Vb.net (or C #) script and using the library Microsoft.office.Interop.Excel
Note: you must add the file Microsoft.Office.Interop.Excel.dll
to the following directories (.NET Framework C:\Windows\Microsoft.NET\Framework\v2.0.50727
DLL directory ) and (SQL Server data dll 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 to 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 with a script task, you need to learn about adding custom links inside a script task
Links that can help
source to share