How can I insert in table list of file available in a folder

Question

As the title I would like to insert in a SQL table the list of file available in a folder using C#. All the Files are CSV.

At the moment at the end of the script I can not find any record into the table but I don't have any error code. I would like to use this code into an ssis script.

 SqlConnection theSqlServer = new SqlConnection(@"Server=sql\insta; Database=database; Trusted_Connection=True;");
 theSqlServer.Open();

 SqlCommand theSqlCommand = new SqlCommand("INSERT INTO [File] ([FileName]) VALUES (" + fileName + " )", theSqlServer);

 MessageBox.Show(fileName);
 theSqlServer.Close();

Please could you help me to find whats is wrong in this code?

You are missing the execution of the command - `theSqlCommand.ExecuteNonQuery()` — Ofir Winegarten, Mar 07 '17 at 20:03

score 0 · Answer 1 · answered Mar 07 '17 at 20:02

0

If you have access to the SQL Server Profiler you can capture the actual SQL statement issued again the server (if one is actually issued!)

answered Mar 07 '17 at 20:02

Giorgos Altanis

2,742
1
13
14

score 0 · Answer 2 · answered Mar 08 '17 at 20:30

First at all, you have created a SqlCommand but you didn't have execute it, you need to use theSqlCommand.ExecuteNonQuery()

Another way to achieve this using SSIS (Better way)

It is not best practice to use an SQL Task to achieve this (it will open a connection for each command, and your query is not parameterized so you have a SQL Injection ALERT!!.

In SSIS you can do the following:

Assuming that you have added an OLEDB connection manager to the SQL server you want to import the data

Add a SSIS Variable User::FolderPath of type System.String with Value equal thef older path containning CSV Files
Add A dataflow Task DFT Import
Inside the dataFlow Task add s Script Component (use it as a source)
In the Script COmponent add an Output Column OutFileName of Type DT_WSTR and length = 4000
Add User::FolderPath variable to the script ReadOnlyVariables

In the Script Window write the following Code (I used Vb.net):

Private strFolderPath As String = String.Empty
Public Overrides Sub PreExecute()

    MyBase.PreExecute()

    strFolderPath = Variables.RowCount

End Sub

Public Overrides Sub CreateNewOutputRows()

    Dim strFiles() As String = IO.Directory.GetFiles(strFolderPath, "*.csv", IO.SearchOption.AllDirectories)

    For Each strFiles As String In strFiles

        With Output0Buffer

            .AddRow()
            .OutFilename = strFiles

        End With

    Next

End Sub

Add an OLEDB Destination
Connect the script output to the OLEDB Connection

How can I insert in table list of file available in a folder

2 Answers2