用DTS导入文本文件时，怎样跳过文本文件的第一行和最后一行

最新推荐文章于 2025-01-08 00:31:53 发布

zhanli

最新推荐文章于 2025-01-08 00:31:53 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 文章标签： transformation variables function file microsoft sql server

本文链接：https://blog.youkuaiyun.com/zhanli/article/details/190476

SQL Server 专栏收录该内容

10 篇文章

订阅专栏

本文介绍在SQL Server中将文本文件导入表时忽略首尾行的方法。通过SQL Enterprise Manager创建DTS包，添加全局变量，使用ActiveX Script Task统计行数，设置ActiveX转换脚本跳过首尾行，最后正确设置优先级完成数据导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Suppose we have a table as follows:

CREATE TABLE [ignore_rows] (
[c1] [int] NULL ,
[c2] [char] (10)
)

And the text file is as follows:

1,aaa
2,bbb
3,ccc
100,ddd

To ignore the first and the last row of the text file when importing the text file to the table, you can use these steps:

1. In SQL Enterprise Manager, right click the Data Transformation Services, click New Package, this will launch the DTS package designer.

2. Click Package --> Properties menu, click the Global Variables tab, add two global variables:

currentRow , int, initial value 0 --> we use it to track the row we are currently processing.
lastRow, int, initial value 0 --> we use it to record the row number of the text file.

3. Add an ActiveX Script Task to the design pane, the script is as follows.

This script use the File System Object (FSO), for more information regarding FSO, please check it on MSDN.

Function Main()

Dim fso
Dim ts
Dim rowCount

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:/data/ignore_rows.txt", 1) '1 for reading

rowCount = 0

While Not ts.AtEndOfStream
ts.SkipLine
rowCount = rowCount + 1
Wend

DTSGlobalVariables("currentRow").Value=0
DTSGlobalVariables("lastRow").Value=rowcount

Main = DTSTaskExecResult_Success

End Function

4. Drag two connections to the pane, one text file connection and one Microsoft OLE DB Provider for SQL Server connection, and then drag a Transform Data Task. The ActiveX transformation script is as follows:

Function Main()

DTSGlobalVariables("currentRow").Value=DTSGlobalVariables("currentRow").Value+1

'The following code will skip and first row and last row
if DTSGlobalVariables("currentRow").Value=1 or DTSGlobalVariables("currentRow").Value=DTSGlobalVariables("lastRow").Value then
Main=DTSTransformStat_SkipRow
else
DTSDestination("c1") = DTSSource("Col001")
DTSDestination("c2") = DTSSource("Col002")
Main = DTSTransformStat_OK
end if

End Function

5. Set the precedence correctly, the final package is as follows:

ActiveX Script Task --(on success)--> Text file connection --(transform data)--> SQL connection