How to write a SSIS package to load a text file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to write a SSIS package to load a text file

Author

Topic

pavandpavan
Starting Member

5 Posts

Posted - 2011-10-12 : 07:26:53

Hi,

i have a text file with pipe delimited , each row in the text file has diff no of columns .

ex:

aaa|bbb| ccc |100 |bbb | uuu| kkk |lll

xxx |vvv|vvv|110|nnn|uuu|kkk|jjj|yyyy|oooo

aaa|bbf|ccc|100|ttb|wwu|eek|lll

sss|fff|qqq|200|rrr|iii|kkk|ggg|dddd

xxx| vvv|vvv|110|nnn|uuu|kkk|jjj|hhh| oooo

no of columns depends on value in the row 100 or 110 or 200 in the file...

if the value is 100 -no of colums are 8

if the value is 110 -no of colums are 10

if the value is 200 -no of colums are 9

i have 3 table in SQL Server import_100,import_110,import_200 in my database

if the value in file is 100 , then that row should be save to import_100 table.

if the value in file is 110 , then that row should be save to import_110 table.

if the value in file is 200 , then that row should be save to import_200 table.

How to write a script task to read the file and save data to SQL Server.

Please Help.........

Thanks
Pavan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 08:07:21

why write script task? you can use data flow task with flat file source,then use conditonal task to split data based on logic then use a oledb destination to drop it to relevant table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavandpavan
Starting Member

5 Posts

Posted - 2011-10-12 : 09:11:28

flat file source is not recognizing the pipe delimiter properly.
may be because of diff no of columns in each row.

Thanks
Pavan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:09:08

do you mean your number of columns will change each time in the file

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavandpavan
Starting Member

5 Posts

Posted - 2011-10-13 : 19:14:10

yes no columns are not same.

but,
all the columns which have 100 in 4th column will have same no of columns.
all the columns which have 110 in 4th column will have same no of columns,
all the columns which have 200 in 4th column will have same no of columns.

please help

Thanks
Pavan

mandm
Posting Yak Master

120 Posts

Posted - 2011-10-14 : 14:47:16

The easiest way is to split the different line types out into their own files first then use data flow tasks to get each file into the appropriate table. Below is some VBScript code that should at least get you started with looping through a file and writing out to another.

Option Explicit

Dim objFSO
Dim strTextInFile
Dim strTextOutFile
Dim strData
Dim strOutData
Dim strLine
Dim strOutLine
Dim arrLines

CONST ForReading = 1
CONST ForWriting = 2

'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'name of the text file
strTextInFile = "InputFile.txt"

'name of the text file. You'll need three output files one for each type of line.
strTextOutFile = "OutputFile.txt"

'Create Output file
Set strOutData = objFSO.OpenTextFile(strTextOutFile, ForWriting, True)

'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextInFile,ForReading).ReadAll

'Split the text file into lines
arrLines = Split(strData,vbCrLf)

'Step through the lines
For Each strLine in arrLines

'Set up three if statements that determine if the strLine contains 100, 110, or 200
'then write the line out to the appropriate out file. You may need to use SPLIT to break
'up the line into columns.

strOutData.writeline(strLine)

' End If statement

Next

'Cleanup
Set objFSO = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources