SQL Server Performance Forum – Threads Archive
SSIS Package and Variable Length Record Import
I have a buddy of mine who is attempting to import a delimited text file into a single table in SQL Server 2005. In and of itself, not a challenging thing using SSIS packages (formerly DTS is SQL Server 2000). The wrinkle in all of this is the fact that the number of delimited columns per record in the text file is variable. This did not seem to be a problem with SQL Server 2000 (using DTS) and it imported fine, but in SQL Server 2005, no go. It seems to be ignoring the end of record delimiter. Here are the details: The table the data is being imported into is defined as such: CREATE TABLE [_TK_852] ([SEGID] char(40) NULL,
[COL002] char(40) NULL,
[COL003] char(40) NULL,
[COL004] char(40) NULL,
[COL005] char(40) NULL,
[COL006] char(40) NULL,
[COL007] char(40) NULL,
[COL008] char(40) NULL,
[COL009] char(40) NULL,
[COL010] char(40) NULL,
[COL011] char(40) NULL,
[COL012] char(40) NULL,
[COL013] char(40) NULL,
[COL014] char(40) NULL,
[COL015] char(40) NULL,
[COL016] char(40) NULL,
[COL017] char(40) NULL,
[COL018] char(40) NULL,
[COL019] char(40) NULL,
[COL020] char(40) NULL,
[COL021] char(40) NULL,
[COL022] char(40) NULL,
[COL023] char(40) NULL,
[COL024] char(40) NULL,
[COL025] char(40) NULL,
[COL026] char(40) NULL,
[ROWID] int NOT NULL
) The text data file uses * as the column delimiter and ~ as the end of record delimiter and here is an example: XQ*H*061225*061231~
XPO*85220070103033256003~
N1*RL*ACME COMPANY,USA~
N9*VR*0000052201~
N9*DP*28~
LIN**SK*636353*VP*10713/150~
ZA*QS~
SDQ*EA*92*0234*.01*0734*23*0740*.01*0882*6*6405*.01~
SDQ*DO*92*0234*.01*0734*55*0740*.01*0882*14*6405*.01~
ZA*QE~
SDQ*EA*92*0234*29*0734*15*0740*43*0882*21*6405*1~
LIN**SK*636369*VP*10720/40~
ZA*QS~
SDQ*EA*92*0701*2*0734*.01~
SDQ*DO*92*0701*13*0734*.01~
ZA*QE~
SDQ*EA*92*0701*33*0734*33~
LIN**SK*636372*VP*10717/36~
ZA*QS~
SDQ*EA*92*6405*.01~
SDQ*DO*92*6405*.01~ As you can see, the number of columns in the text file is variable, and this is causing problems with 2005 whereas it appears to have worked fine with 2000. Can anyone provide a solution? I have Googled this issue and have come up with bupkus! Thanks! JimB
You probably need to set the row delimiter to "~". By default it is a carriage return.
He actually replaced the ~ with a carriage return in the actual data file and it is still not recognzing the end of each record. It appears to be an issue with the fact that each record in the text file is variable in length, in which the delimited columns in each record vary. This is something that DTS in 2000 did not seem to have an issue with, but SSIS packages in 2005 seem to have a problem with the variable record length. JimB
Have you tried using BCP? You could test pretty quickly. bcp [_TK_852] in filename.txt -t * -r ~ Add flight search to your own site for free
www.skyscanner.net
]]>