2

I have a simple package that extracts data from SQL Server and writes it to Excel spreadsheet.

Why am I doing this: the problem is that the value of one of the column is greater than 255 characters, which causes an error. I already tried different ways to find a solution, but so far without success.

So I created dummy row in my Excel template that is able to accept more than 255 characters. After I load the data, I need to delete that dummy row.

I do not know much about C#. How can I delete second row in Excel destination by using SSIS script task C#? Do I need to create variables that hold the path folder and file name?

And then how can I map those variables to my C# code?

enter image description here

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
Serdia
  • 4,242
  • 22
  • 86
  • 159
  • 1
    Why use a script task? Why not just exclude that record in the query in your OLEDB Source? – Tab Alleman May 31 '18 at 18:42
  • 1
    Why do you want to use a Script Task? I imagine it would be far simpler to do it in the DataFlow. – Thom A May 31 '18 at 18:42
  • what is unique about that row in the source? add that to the where clause in the extract or if you can't do that then do a conditional split on the unique item – KeithL May 31 '18 at 18:43
  • The problem is that the value of one of the column is greater than 255 characters, which gives an error. I already tried different ways to find the solution, but still no success. So I created dummy row in my excel template that able to accept more than 255 characters. After I load the data, I need to delete that dummy row. – Serdia May 31 '18 at 18:53
  • I think it would be way better to fix your error another way.. this is a pretty terrible solution. No offense. : ) What was the error you got before you put the "dummy row" in? And instead of creating a "dummy row", why can't you use the same technique to make ALL the rows capable accepting more than 255 characters? – Tab Alleman May 31 '18 at 19:59
  • The technique is easy, you just type same letter like 400 times in the particular cell of the excel template. – Serdia May 31 '18 at 20:53

4 Answers4

1

You can use Microsoft.Office.Interop.Excel.dll library inside the Script Task to achieve this.

You can use a similar code:

Note: I assumed that the file path variable name is FilePath. Remember to select the FilePath variable in the Script Task ReadOnly Variables (in the Script editor). Also don't forget to add Microsoft.Office.Interop.Excel.dll as a reference inside the script task

Imports Microsoft.Office.Interop

Public Sub Main()

    Dim strFile as String = Dts.Variables("FilePath").Value 
    Dim m_XlApp = New Excel.Application
    m_XlApp.visible = False
    m_XlApp.DisplayAlerts = False
    Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
    Dim m_xlWrkb As Excel.Workbook

    m_xlWrkb = m_xlWrkbs.Open(strFile)
    m_xlWrkb.DoNotPromptForConvert = true 

    Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)

    m_XlWrkSheet.Cells(2, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)

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

    Marshal.ReleaseComObject(m_xlWrkb)
    Marshal.ReleaseComObject(m_xlWrkbs)
    m_XlApp.Quit()
    Marshal.ReleaseComObject(m_XlApp)

End Sub
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thanks. I am using Visual Studio 2010 Shell. Is it still possible to add Microsoft.Office.Interop.Excel.dll reference? I couldn't find an answer yet. – Serdia Jun 01 '18 at 16:21
  • @Oleg https://www.acceptic.com/blog/ssis_2012_how_to_add_a_dll_reference_in_ssis_script_task.html – Hadi Jun 02 '18 at 08:21
1

I got the same damn problem.

Like you, I created dummy row in my Excel template that is able to accept more than 255 characters. However, I works on Visual Studio 2008 and I can't install anythings on my computer.

My solution to delete the dummy row is to set in it's first column the value "DummyRow" and create this following macro in the Excel template:

Private Sub Workbook_Open()
    Call SupprDummyRow
End Sub

Sub SupprDummyRow()

    x = Range("A2").Value 'First row after the titles, first column 
    y = Range("A3").Value 'Second row after the titles, first column

    If (x = "DummyRow" And y <> "") Then 'The Dummy Row is present AND the next row have value (we are in the export file)
            Range("A2").EntireRow.Delete 'Delete the lign
    End If
End Sub

Note that on my situation the first column have always a value (no empty string).

Hope this help!

Best regards,

Fortane
  • 11
  • 1
0

Try this to increase the column length on the Excel destination. This way you would not even need to add your dummy record to the excel file and later delete it.

  1. Right Click on your Excel Destination.
  2. Click on "Show Advanced Editor".
  3. In the Advanced Editor, go to the "Input and Output Properties" tab.
  4. Expand the "External Column" of you Excel Destination Section and adjust the Length property values of the required column. You can also change data-types if needed. Please find the screenshot for the same.

enter image description here

Try this and let me know if it worked.

Kashyap MNVL
  • 603
  • 4
  • 13
  • Thanks. I tried to do that...probably more than 10 times different combinations. – Serdia May 31 '18 at 20:56
  • Can you make the dummy row go towards the end of the excel file? – Kashyap MNVL May 31 '18 at 20:57
  • Unfortunately if I make it at the end then it will be pointless. The Excel driver attempts to read the cells in each column and "guess" at their length, based on the first few rows (8 by default I believe). Using that information, it configures the "maximum length" for each column. By default, it chooses 255. The workarounds are ugly. In every other SSIS connection you can explicitly define the column properties, but the Excel connection gives you literally no options or properties at all – Serdia May 31 '18 at 21:12
0

I created dummy row in my Excel template Which is able to accept more than 255 characters. After export data to excel file, delete dummy row from excel with SSIS scripts task.

Pass the exel file as variable like below immage

enter image description here

Write below code to delete 2 number row. worksheet.Rows[2].Delete();

enter image description here

Shahidul Islam Molla
  • 541
  • 6
  • 6