Hi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False
Dim xlFile
xlFile = "C:\Data\file.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)
'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")
Dim serverName
serverName = "myserver2"
strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"
cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2
client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"
sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"
row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)
Loop
if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End FunctionYou might want to refer to this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Please can you post the value of the sql variable? To us this is several magnitudes more important than all that vb.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).|||Actually - I have read the code now. I would dump the on error resume next when debugging. Probably in production too but especially now.
Also - the loop is unecessary - refer to the link I sent. SQL Server is perfectly capable of dealing with an excel sheet as a table rather than a row at a time. Quicker and easier. As such you can skip all the ActiveX stuff altogether. DTS & ActiveX -> difficult to debug code. If possible try to do it all in T-SQL and run it from a job. You will find most of the guys here won't use ActiveX for very much if anything at all and work with the native T-SQL language.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).Damn youngsters and their quick typing skills ;)|||Reminds me of some ActiveX in a DTS my old manager wrote...
It went through every row (46K) and assigned zeroes or blank strings to every NULL value... Needless to say it took aaaaaaaages.
A couple of very tiny T-SQL tweaks and it runns in seconds again ;) No prizes for guessing the function anyhow|||Thanks for the quick response, the sql variable = INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES
( 'Adisseo USA Inc.' ,'Wesley Kent', 2/27/2007, 'WCS');
the reason i'm reading line by line is because i only want certain columns having certain values, it doesnt show that in this code, but eventually i will need to read line by line. Thanks!|||i only want certain columns having certain values
Can you expand on this please?
Rules/logic etc|||como se dice?|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again?|||the rule is, read specific columns(not all), and then insert the values in a temp table. there are four tabs that need to be imported, all with different columns to import. but it needs to be in a DTS package. I guess loading it all would be another option|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again -- With a DTS package?|||I take it you did not bother looking at the link then? You can specify columns & where clauses using that technique.|||Gotta ask - what is everyone's obsession with using DTS packages? Why stipulate that the answer, whatever it is, must be in a DTS package?
In any event - you can call T-SQL from a DTS package too.|||i got it, the problem was with the excel object "Trim(objsht.Cells(row, 2).Value)"
instead i used Trim(objxl.Cells(row, 2).Value). Thanks for all your suggestion guys!|||i did look at the link pootle, but in some cases i would need to skip lines, and i didnt know how to do that with that link|||Gotta ask - what is everyone's obsession with using DTS packages? ...
ummm... because I haven't learned the other way(s?) yet.
Besides, the graphical interface makes it so easy to follow job flow:
