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 a corporate windows network. I can list and have access to the files through UNC shares (\ server \ shared folder).
I would like to know how I can list the files and save them to a table in sql 2012, using the SSIS 2012 standard version, since WMI usage does not work with this device.
Thank you in advance for the answer!
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 put 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