error on opening a txt file with vb.net

[neoklis]

Hi,

I am trying to read a text file with vb.net from SSIS package but it fails on objCom.ExecuteReader(). The error message is below


[Script Component [294]] Error: System.Data.OleDb.OleDbException: Invalid argument. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

This is my code. I can’t figure out what may be wrong.


Dim con As New OleDb.OleDbConnection
Dim objCom As New OleDb.OleDbCommand
Dim rdr As OleDb.OleDbDataReader
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SapText;Extended Properties=""text;HDR=Yes;FMT="""
Dim cmd As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Prg_Sys.txt", con)
con.Open()

rdr = objCom.ExecuteReader()
Do While rdr.Read()
MsgBox(rdr.Item(0))
Loop

con.Close()

Thanks

 

[SQL_Hell]

It looks like 'objcom' has not been set to anything, you declared it and set it to a new oledb command, but you didn't assign anything to it.

So when you execute rdr = objCom.ExecuteReader() how does 'objcom' know what to read?

Why are you writing custom code for this? there are drag and drop objects in SSIS that will do the same thing?

 

[SQL_Hell]

This site will help you out:

http://www.sqlis.com/

 

[neoklis]

Thanks once again for your help

I have a text file and a Sql Server Table. The text file has about 20 rows and the SQL table one row and always will have one row.The value of a specific column and row of text file may corresponds under circumstances to a specific field of the SQL table. So all I want is to open the txt file and read it as table so with some logical statement to lead the read values exactly to the target I want.

I made the corrections you suggest me. Now it looks like it fails on con.open.

Dim con As New OleDb.OleDbConnection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SapText;Extended Properties=""text;HDR=Yes;FMT="""
Dim objCom As New OleDb.OleDbCommand("SELECT * FROM Prg_Sys.txt", con)

con.Open()

Dim rdr As OleDb.OleDbDataReader
rdr = objCom.ExecuteReader()

Do While rdr.Read()
MsgBox(rdr.Item(0))
Loop

rdr.Close()
con.Close()



The error message

[Script Component [294]] Error: System.Data.OleDb.OleDbException: Invalid argument. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

 

[neoklis]

After changing the FMT=""" to FMT=Delimited""" started playing I will watch it now and i will let you know

 

[neoklis]

I finally managed to read the txt file. I had to make a schema ini file to initialize the schema of mytxt file.

Thanks for the very interesting link