如何使用ssis转换一组xls 2制表符分隔的文件

Question

如何使用ssis将一组xls文件转换为制表符分隔的文件？

在谷歌搜索中找到了这样做的脚本。请告诉我如何使用ssis 实现这一点

    Dim objFSO, objFile, objFileTSV
    Dim strLine, strNewLine, strNewText
    Dim FileNameLength, LineLength, NewFileName, Linepos, Quote, QuoteCount, TotalFilesConverted
    objFSO = CreateObject("scripting.filesystemobject")
    strCurPath = objFSO.GetAbsolutePathName(".")
    TotalFilesConverted = 0
    For Each objFile In objFSO.getfolder(strCurPath).Files
        If UCase(Right(objFile.Name, 4)) = ".CSV" Then
            FileNameLength = Len(objFile.Name) - 4
            NewFileName = Left(objFile.Name, FileNameLength) & ".tsv"
            objFile = objFSO.OpenTextFile(objFile, 1)
            Do Until objFile.AtEndOfStream
                strLine = objFile.ReadLine
                LineLength = Len(strLine)
                Linepos = 1
                strNewLine = ""
                Quote = False
                QuoteCount = 0
                Do While Linepos <= LineLength
                    If Mid(strLine, Linepos, 1) = "," And Not Quote Then
                        strNewLine = strNewLine + vbTab
                        Quote = False
                    ElseIf Mid(strLine, Linepos, 1) = Chr(34) Then
                        QuoteCount = QuoteCount + 1
                        If QuoteCount = 2 And Linepos <> LineLength Then
                            If Mid(strLine, Linepos, 2) = Chr(34) & Chr(34) Then
                                strNewLine = strNewLine + Chr(34)
                                Linepos = Linepos + 1
                                Quote = True
                                QuoteCount = 1
                            Else
                                Quote = False
                                QuoteCount = 0
                            End If
                        Else
                            Quote = True
                        End If
                    Else
                        strNewLine = strNewLine + Mid(strLine, Linepos, 1)
                    End If
                    Linepos = Linepos + 1
                Loop
                strNewText = strNewText & strNewLine & vbCrLf
            Loop
            objFile.Close()
            objFileTSV = objFSO.CreateTextFile(NewFileName)
            objFileTSV.WriteLine(strNewText)
            TotalFilesConverted = TotalFilesConverted + 1
            strNewText = ""
            objFileTSV.Close()
        End If
    Next
    MsgBox(CStr(TotalFilesConverted) + " Files Converted from CSV to TSV.")

据了解，这可以用于脚本任务。。但是让我有一些线索

Answer 1

假设不涉及数据转换，最简单的方法是创建一个具有Excel源和平面文件目标的数据流任务。您应该在平面文件连接管理器中将"列分隔符"字段设置为"Tab｛t｝"。

由于您的输入中有多个Excel文件，因此可以使用Foreach循环对每个文件执行上述过程。