SSIS 2012 how to list files contained in UNC sharing?

Question

I have multiple files stored in a "Hitachi Data Ingestor" (HDI) solution that serves as File Server. This solution is managed by linux system within an enterprise windows network. I can list and access files through UNC shares ( shared folder server).

I wonder how I can list the files and save them in a table in sql 2012, using SSIS 2012 standard version, since using WMI does not work with this device.

Thank you in advance for your reply!

Answer 1

I was able to find a solution to my problem. Drag a Scrpit Task from the SSIS toolbar, double click, change the language to Visual Basic 2010, edit the script code, and set the routine:

Public Sub Main()
        '
        ' Add your code here
        '

        Dim Computador As String = Dts.Variables("vServer").Value.ToString
        Dim FolderPai As String = Dts.Variables("vFolderPai").Value.ToString
        Dim CompartilhamentoUNC As String = "\\" & Computador & FolderPai

        Dim conexaoSQL As SqlClient.SqlConnection = New SqlClient.SqlConnection(Dts.Connections("ConexaoSQL").ConnectionString)
        Dim SqlComm As SqlClient.SqlCommand = New SqlClient.SqlCommand("sp_InsereTabela", conexaoSQL)
       
        Try
            'Configura o objeto SqlCommand com outros atributos e adiciona os parametros
            SqlComm.CommandType = CommandType.StoredProcedure
            SqlComm.Parameters.Add("@CaminhoArq", SqlDbType.NVarChar, 255)
            SqlComm.Parameters.Add("@NomeArquivo", SqlDbType.NVarChar, 255)

            SqlCommArqNaoCad.CommandType = CommandType.StoredProcedure
            SqlCommArqNaoCad.Parameters.Add("@CaminhoArq", SqlDbType.NVarChar, 255)
            SqlCommArqNaoCad.Parameters.Add("@NomeArquivo", SqlDbType.NVarChar, 255)


            conexaoSQL.ConnectionString = conexaoSQL.ConnectionString & "Password=App_ContratosDigitalizados;"

            Dim Arquivos = From Arquivo In Directory.EnumerateFiles(CompartilhamentoUNC, "*.pdf", IO.SearchOption.AllDirectories) _
                           Select Arquivo




            For Each strArq As String In Arquivos

                Try
                    Dim NomeArq As String = strArq.Substring(strArq.LastIndexOf("\") + 1)

                        SqlComm.Parameters("@NomeArquivo").Value = NomeArq
                        SqlComm.Parameters("@CaminhoArq").Value = strArq.Replace(NomeArq, "")

                        If conexaoSQL.State <> ConnectionState.Open Then
                            conexaoSQL.Open()
                        End If

                   
                Catch ex As Exception

                End Try



            Next
            
            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
        End Try

    End Sub