Text File Import

Question

Post reply

Text File Import

erngreen

SSC Rookie

Points: 34
More actions
September 10, 2010 at 1:09 pm

#226195

I need to import a text file using a script task, vb.net and a schema.ini file. I can code the vb in Visual Studio but was wondering how I would get this to work in SSIS Script Task. Here is what I have so far any assistance would be greatly appreciated. Thanks
Dim path As String = "C:\testfile.txt"
Dim SQL As String = "SELECT * FROM " & IO.Path.GetFileName(path) & " "
Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='" & IO.Path.GetDirectoryName(path) & _
"';Extended Properties=" & Chr(34) & _
"Text;HDR=No;FMT=Delimited(~)" & Chr(34) & ";"
Dim adapter As New OleDb.OleDbDataAdapter(SQL, cnString)
Dim table As New DataTable()
adapter.Fill(table)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Hi,

You should use Script Component, not Script Task and create Script Source. Two recommendations:

- Explicitly define the columns from the source and then define each and every column in the script setup you will insert in the data flow.

- The provider you are using looks like Jet's OLEDB . Use the old ADODB and construct Recordset object, which you will use to read the data from the text file. I recommend this because the approach you have used in your script will load the complete text file in memory before it starts the loading process.

---------------

I'm wondering why you are not using the regular Flat File Connection Manager / Flat File Source? From what I can see you can process this type of file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

erngreen SSC Rookie Points: 34 More actions · Answer 2

The flat file connection manager does not work for files with uneven column data. It works well in DTS but not in SSIS. The file is also delimited with a tilda characters from the source and not comma and this seems to be a problem also. I agree that this should be in a script component. I guess my quesiton is how to read in and out of the script component with ADO.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

erngreen (9/13/2010)
The flat file connection manager does not work for files with uneven column data. It works well in DTS but not in SSIS. The file is also delimited with a tilda characters from the source and not comma and this seems to be a problem also. I agree that this should be in a script component. I guess my quesiton is how to read in and out of the script component with ADO.

You can define tilda (~) character as delimiter. In the flat file connection manager dialog just type it in.

For the ADO , you have to first include reference to ADO library. Check these resources to find out how to read CSV using ADO recordset:

http://www.simongibson.com/intranet/adooledb/%5B/url%5D

http://msdn.microsoft.com/en-us/library/ms974559.aspx

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

erngreen SSC Rookie Points: 34 More actions · Answer 4

This is a pure text file. I know that the tilda can be defined in the flat file connection. I have gone around and around on this issue for months and the flat file connection issue is something that it is a know issue.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

erngreen (9/13/2010)
This is a pure text file. I know that the tilda can be defined in the flat file connection. I have gone around and around on this issue for months and the flat file connection issue is something that it is a know issue.

Please provide an example of your data and a reference to the known issue you mention.

erngreen SSC Rookie Points: 34 More actions · Answer 6

here are just a couple of posts related to the problem.

http://connect.microsoft.com/SQLServer/feedback/details/126493/uneven-column-count-import-using-ssis

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/53e3f59d-9442-43f9-99c5-37dd964408fc/

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

erngreen (9/13/2010)
here are just a couple of posts related to the problem.
http://connect.microsoft.com/SQLServer/feedback/details/126493/uneven-column-count-import-using-ssis
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/53e3f59d-9442-43f9-99c5-37dd964408fc/

Have you checked this component ?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

erngreen SSC Rookie Points: 34 More actions · Answer 8

While I would like to use this component, I have been unable to get it to install in my enviornment.

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

erngreen (9/13/2010)
While I would like to use this component, I have been unable to get it to install in my enviornment.

What's the issue? Have you tried asking a question in the codeplex forum here ?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

erngreen SSC Rookie Points: 34 More actions · Answer 10

The installation goes smoothly but when I go to add it in BI Studio. I get an error.

COZYROC One Orange Chip Points: 28499 More actions · Answer 11

erngreen (9/13/2010)
The installation goes smoothly but when I go to add it in BI Studio. I get an error.

What is the exact error message?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

erngreen SSC Rookie Points: 34 More actions · Answer 12

I can post the message but it will probably be some time this afternoon as I have something running in BIDS. I will have to re-create.

erngreen SSC Rookie Points: 34 More actions · Answer 13

here is the error message. I have tried to manually install this to GAC with no success. When I go to BIDS --> Toolbox --> Right Click and browse to this dll, I get the error below.

There was an error loading types from assembly 'C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\DelimitedFileReader.dll'

'Could not load file or assembly 'Microsoft.SqlServer.PipelineHost, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.'

COZYROC One Orange Chip Points: 28499 More actions · Answer 14

What version of SQL Serve do you use? 2005 or 2008 ?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/