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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

Supposewehaveatableasfollows:

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

Andthetextfileisasfollows:

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

To ignorethefirstandthelastrowofthetextfilewhen importingthetextfiletothetable,youcanusethesesteps:

1.InSQLEnterpriseManager,rightclicktheDataTransformationServices,clickNewPackage,thiswilllaunchtheDTSpackagedesigner.

2.ClickPackage-->Propertiesmenu,clicktheGlobalVariablestab,addtwoglobalvariables:

currentRow,int,initialvalue0 -->weuseittotracktherowwearecurrentlyprocessing.
lastRow,int,initialvalue0        -->weuseittorecordtherownumberofthetextfile.

3.AddanActiveXScriptTasktothedesignpane,thescriptisasfollows.

ThisscriptusetheFileSystemObject(FSO),formoreinformationregardingFSO,pleasecheckitonMSDN.

FunctionMain()

Dimfso
Dimts
DimrowCount

Setfso=CreateObject("Scripting.FileSystemObject")
Setts=fso.OpenTextFile("C:/data/ignore_rows.txt",1) '1forreading

rowCount=0

WhileNotts.AtEndOfStream
ts.SkipLine
rowCount=rowCount+1
Wend

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

Main=DTSTaskExecResult_Success

EndFunction

4.Dragtwoconnectionstothepane,onetextfileconnectionandoneMicrosoftOLEDBProviderforconnection,andthendragaTransformDataTask.TheActiveXtransformationscriptisasfollows:

FunctionMain()

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

'Thefollowingcodewillskipandfirstrowandlastrow
ifDTSGlobalVariables("currentRow").Value=1orDTSGlobalVariables("currentRow").Value=DTSGlobalVariables("lastRow").Valuethen
 Main=DTSTransformStat_SkipRow
else
 DTSDestination("c1")=DTSSource("Col001")
 DTSDestination("c2")=DTSSource("Col002")
 Main=DTSTransformStat_OK
endif

EndFunction

5.Settheprecedencecorrectly,thefinalpackageisasfollows:

ActiveXScriptTask--(onsuccess)-->Textfileconnection--(transformdata)-->SQLconnection

 

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>