Converting Excel to CSV - SeverityCodeDescriptionProjectFileLineSuppressio

Question

Post reply

Converting Excel to CSV - SeverityCodeDescriptionProjectFileLineSuppressio

yrstruly

Hall of Fame

Points: 3475

March 13, 2024 at 4:29 pm

#4394690

I am trying to convert an Excel to CSV. I am getting an error, using the code below:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.OleDb;
using System.IO;
#endregion

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            //File DataTable from Excel Source File. I have used Sheet1 in my case, if your sheet name is different then change it.
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                Dts.Variables["User::VrExcelPath"].Value.ToString() +
                           ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query = "SELECT * FROM [Utilization Data]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);

            //Create csv File
            using (var sw = new StreamWriter(Dts.Variables["User::Vr_CSV_Path"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";
                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += "\"" + datatable.Rows[row][col].ToString() + "\",";
                    }
                    //remove last , from row
                    strRow = strRow.Remove(strRow.Length - 1);
                    //write row to file
                    sw.WriteLine(strRow);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

I am following example here: https://www.techbrothersit.com/2014/09/ssis-how-to-convert-excel-file-to-csv.html Please find SSIS and data source attached.

Error:

SeverityCodeDescriptionProjectFileLineSuppression State
ErrorCS0234The type or namespace name 'Tasks' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)SC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs16Active
ErrorCS0234The type or namespace name 'Tasks' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)SC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs17Active
ErrorThe type or namespace name 'Tasks' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)SC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs16
ErrorThe type or namespace name 'Tasks' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)SC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs17
ErrorCS0103The name 'Dts' does not exist in the current contextSC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs24Active
ErrorCS0103The name 'Dts' does not exist in the current contextSC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs35Active
ErrorCS0103The name 'Dts' does not exist in the current contextSC_102a2216653a493f8202292786e099b0C:\Users\Anthony.DESKTOP-ES5HL78\AppData\Local\Temp\Vsta\SSIS_SC150\VstauvtiOQ1UaUmBNrxSycyrXQ\VstajiJ9tCNDaEaja__pXvcK0Vw\main.cs50Active
WarningThere was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SqlServer.DTSRuntimeWrap, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.SC_102a2216653a493f8202292786e099b0
WarningThere was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SqlServer.DTSRuntimeWrap, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.SC_102a2216653a493f8202292786e099b0
WarningThere was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SqlServer.DTSRuntimeWrap, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.SC_102a2216653a493f8202292786e099b0C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets2110

Attachments:

You must be logged in to view attached files.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

At what point do you get that error? Design time, or run time?

I just created a script task and pasted the code in. It built successfully.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

I tested the C# code/ ran it i cant close the script editor because the error is there. I also resolved this: dll

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Sorry … no idea.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

easy - would help if you had created a script task (as instructed on the link) instead of a script component.

so do it correctly - script component does NOT allow for same type of object references - and it is also not the place to put a converter like this one.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

frederico_fonseca wrote:

easy - would help if you had created a script task (as instructed on the link) instead of a script component.
so do it correctly - script component does NOT allow for same type of object references - and it is also not the place to put a converter like this one.

As the linked example specifically talks about using a script task, I had assumed that that is what the poster would use. Silly me.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yrstruly Hall of Fame Points: 3475 More actions · Answer 6

I tested the C# code/ ran it i cant close the script editor because the error is there. I also resolved this: dll