VB script to insert row in sql

[techipa]

Hello All,

I have a VB task on the DTS which uploads the CSV files to the SQL tables. If there are no CSV files I want the VB task to write a errorlog row in the sql table.

Can anyone tell me how to add the logic to insert row in SQL table from VB script task?

Here is the check on the csv file in VB script task:

Set fso=CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)

if fsoFolder.files.count <> 0 then

For Each fsoFile in fsoFolder.Files
'Get first filenme
IF lcase(Right(fsoFile.name, Len(fsoFile.name) - InStrRev(fsoFile.name, ".")) ) <> Lcase(sFileExtension) OR Lcase(Left(fsoFile.name, 6)) <> Lcase(sFilePrefix) Then

'Insert the row ('No CSV files') in the SQL ErrorLog table and exit out of DTS

ELSE
sFileName = sFolder & fsoFile.Name
sFile = fsoFile.Name
Exit For
END IF
Next

else
'Insert the row ('No CSV files') in the SQL ErrorLog table and exit out of DTS
Main = DTSTaskExecResult_failure
End if


Thanks,
-techiPA

 

[AlexCuse]

TechiPA - Try this out:

'*************************************************
'  Visual Basic ActiveX Script
'*******************************************************

Function Main()
        Dim strQuery
        Dim strTableResult
        Dim strConn
        Dim bMail

      dim oConn


'specify connection string
strConn =  "Driver={SQL Server};Server=ServerNamePHISQL002;Database=[b]TEST[/b];Uid=[b]MyUID[/b];Pwd=
[b]MyPass[/b];" 
' create Connection object
set oConn = CreateObject("ADODB.Connection") 
'open connection using connection string
oConn.Open(strConn)      
'create and execute query to insert
strQuery = "[b]insert into instest select '" & Date() & "'"[/b]
oConn.Execute(strQuery)

Main = DTSTaskExecResult_Success


      
End Function

You can alter this to be inside your if statement so taht it executes only if the file does not exists. Everything in bold you will need to change.

Hope this helps,

Alex




Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Thanks Alex,

This logic worked perfectly except the record got addedd three times because I had three other files in that folder. It should add the error log only once if the cdv file is there there. And one more thing I had to hardcode the userid and pwd which I would not prefer since I use 'sa'

Is there better way to implement this such as assign the error message ('CSV file not found') to the global variable and then insert that variable in the SQL table...something like that.. and then run the SP to send the email to the respective users and exit out of DTS.

Your suggestion is appreciated.

Thanks,
-techiPA

 

[AlexCuse]

techipa -

Maybe instead of looping through your folder and comparing file names to your string, you should key off of fso.FileExists(strFileName)? You can do it like this:

'****************************************************
'  Visual Basic ActiveX Script
'**********************************************************

Function Main()

dim objFSO
dim strFileName

'define file you are looking for
strFileName ="G:\Some\Directory\" + inputBox("FileName to look For?")

'instantiate FSO
set objFSO = CreateObject("Scripting.FileSystemObject")

'check for file existence, notify user
if objFSO.FileExists(strFileName) then

msgBox "File Exists!"

else

msgBox "File Not Exists"

end if

Main = DTSTaskExecResult_Success

End Function

If you are only looking for files based on their prefixes and suffixes, then you will need to re-think your if logic, so that the row is only appended if NONE of the files in the folder match. The best way off the top of my head to do this is to create a boolean and start it out set to 'False', and set this to 'True' each time a file is found.

Then, you decide whether to insert/halt package or proceed based on the value of this boolean (whether a file was found or not).

Does this make sense?

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Alex,

I like your suggestion. Actually I have to have the prefix and postfix checked for example the first 6 letters of files (prefix) and the file extension (postfix) if they exists then the flag should be true and then loop through the files to upload them one by one in SQL else the file is not found to set the flag to false and insert the error log to the SQL table and come out of the DTS.

This looks little difficult since I am not an expert in vb/dts but I will try.

May I ask you how can I come out of the VBtask if certain condition is not meet? and how do i come out of the DTS package from VB script?

Tahnks again,
-techiPA

 

[AlexCuse]

techiPA -

I played around a bit with your script, and added the function to insert your row. Try this out (not tested).

'**********************************************************
'  Visual Basic ActiveX Script
'**********************************************************
Function Main()

dim fso
dim fsoFolder
dim boolFilesExist

Set fso=CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder) 

'start boolean at False
boolFilesExist = "False"

if fsoFolder.files.count <> 0 then

For Each fsoFile in fsoFolder.Files
       'Get first filenme
        IF  lcase(Right(fsoFile.name, Len(fsoFile.name) - InStrRev(fsoFile.name, ".")) ) = Lcase(sFileExtension) AND  Lcase(Left(fsoFile.name, 6)) <> Lcase(sFilePrefix) Then
   	 sFileName = sFolder & fsoFile.Name
                 sFile = fsoFile.Name
                 boolFilesExist = True
    	Exit For
        END IF
Next

End If

if boolFilesExist = "False" Then

'call second function to insert record
Call InsertRecord()
'step failure
Main = DTSTaskExecResult_failure

else

Main = DTSTaskExecResult_Success

End if

End Function

'***********************************************

Function InsertRecord()

Dim strQuery
Dim strConn
dim oConn


'specify connection string
strConn =  "Driver={SQL Server};Server=MyServer;Database=TEST;Uid=MyUID;Pwd=MyPass;" 
' create Connection object
set oConn = CreateObject("ADODB.Connection") 
'open connection using connection string
oConn.Open(strConn)      
'create and execute query to insert
strQuery = "insert into instest select 'No CSV Files'"
oConn.Execute(strQuery)

set oConn = Nothing
      
End Function

As far as the login issue, that is simple. Just create a login that has write permission to the table you need to insert to, and restrict its' access to other tables/db's as you need to. Then use this login in your connection string.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Thanks Alex for your prompt response and the sample code. I will try to implement it and see how far I reach.

If I get issues I will post on Monday.

Thanks,
-techiPA

 

[AlexCuse]

Let me know if it works, too

I am guessing by your handle that you are in PA, are you leaving work early as well? (I am, in the southeastern part of PA)

One thing to note, for the future. When you post code, use these tags around your code:

[ignore]

Here is where I type the code

[/ignore]

This will give the following result, which (with real code anyway) is much easier to read:

Here is where I type the code

Have a good weekend,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Thanks Alex,

I implemented the logic and it works file now.
Next time, I will use the format you mentioned to post my code

Thanks for your help
-techiPA