Data Import Problems

Question

Hello,

I just want a simple SSIS package, that takes all the columns in a flat file, and inserts it into a newly created table, each field VARCHAR(1000), or any other simple text.  The problem is, files are huge, with approximately 200 columns each, and changing each and every column is painful.

So, basically, I want to transfer data into the table and then deal with it there.

Any help will be appreciated.

Thanks.

Answer 1

I believe you can do this using the Script Transformation Task. You can take a look at this link that partially explains how this can be done.

http://www.sqlis.com/sqlis/post/Handling-different-row-types-in-the-same-file.aspx

Answer 2

You can also create a linked server to TEXT file and operate with as  a regular table

Sample text file:

"KE02","01",0,"DIGIMON DIGITAL INC.","840 N LENOLA
ROAD","","","MOORESTOWN","NJ",80570000," 3/27/2002","",1
"KE03","01",0,"NABOO INTERNATIONAL","NABOO INTERNATIONAL","840 N. LENOLA
ROAD","","MOORESTOWN","NJ",80570000," 3/27/2002","",2
"KE04","01",0,"RAZOR SCOOTER CENTRAL","","840 N. LENOLA
ROAD","","MOORESTOWN","NJ",80570000," 3/27/2002","",3
"KE05","01",0,"TAX CENTRAL - HEARTLAND TEST","HEARTLAND TEST","840 N. LENOLA
ROAD","","MOORESTOWN","NJ",80570000," 3/27/2002","",4

 





/*
-- This must be done once to set up the server

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'e:\txtsrv', -- the directory that will hold your text file(s)
   NULL,
   'Text'
select Col1,Col2,Col3,Name1,Name2,Address1,Address2,City,ST,Col10,Col11,Col12,Col13
into #temp
from txtsrv...enrhead#txt
select * from #temp
go
drop table #temp
EXEC sp_dropserver txtsrv
EXEC sp_droplinkedsrvlogin 'txtsrv', NULL
EXECUTE sp_addlinkedsrvlogin 'txtsrv' , False, NULL, NULL, NULL
*/

/* The txtsrv directory needs a file named schema.ini containing this:

[enrhead.txt]
Format=CSVDelimited
CharacterSet=OEM
ColNameHeader=False
DateTimeFormat=mm/dd/yyyy
Col1=Col1 Text
Col2=Col2 Text
Col3=Col3 Long
Col4=Name1 Text
Col5=Name2 Text
Col6=Address1 Text
Col7=Address2 Text
Col8=City Text
Col9=ST Text
Col10=Col10 Long
Col11=Col11 DateTime
Col12=Col12 Text
Col13=Col13 Long



*/
select Col1,Col2,Col3,Name1,Name2,Address1,Address2,City,ST,Col10,Col11,Col12,Col13
into #temp
from txtsrv...enrhead#txt

select * from #temp
go
drop table #temp

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Hello,

Below link might help you.

http://stackoverflow.com/questions/6464601/validate-csv-file-data-before-import-into-sql-server-table-in-ssis