loop through ini file and pass paramters to an SP
-
Hi
I would like to loop through this ini file section below by means of a VBscript:
[DatabaseCleaner]
! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd>
! -- <N> must be a successive number starting from 1 ...
TableToBeCleaned_1=Transactions,24,24
TableToBeCleaned_2=ECElementValueLog,24,24
TableToBeCleaned_3=Payments,24,24
TableToBeCleaned_4=PresenceTickets,24,24
etc.
etc.
and pass the three parameters per line to an stored procedure (SP). This happens within DTS environment of MS SQL Server 2000.
As I do not yet know how many lines I may expect in the ini file - how can I control it?
Someone has some hints or an construct for me?
Thanks
mipo
-
Basically you need to call the system API's the api will return to you all the items in a section or a specific item in a section.
I looked on msdn for an example but couldn't find one but I found the one below on google.
http://www.codeguru.com/vb/openfaq/comments/119.shtml
If you can't decipher this let me know and I'll write a specific one. Have done this in vb script, but used to do this in VB all the time.
hth
-
Thanks for your input.
The problem is that the ini file is not only for my application respectively there are other sections in threre. So I have to jump to my section and read only the parameter of interest. So how can I know this is the end of me relevant section?
Thanks
mipo
-
If the section header will remain the same, you can create a VBScript that will open a FileSystemObject on the INI file. Then scroll through the file till it finds the section header you want. Then scroll through the Key values within the section till you reach the next section header, or a blank line. Separating the parameters for the SP would be done using String functions such as Mid$ and InStr.
Another more complicated method, but possibly more functional and supportable, would be to use the Dynamic Properties task to load each Key value from the INI file in turn. You would need to have some VBScript code that dynamically re-assigned the Key value property in the task and loop through the steps. EG: 1) Set key value property, 2) get Key value from INI File, 3) process key value, 4) Exec SP, 5) loop back to start.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
The beauty of the getPrivateProfileString is that you can either read all section names, all items in a section (which is what you want), or the value of a specific item in a section. MS has provided a flexible api call that can be used which eliminates the need to parse, but with a little more coding the fileSystemObject could be used, or even the ole provider for text files.
-
Thanks for your input.
Someone has a sample how to pass the parameters (read from the ini file by means of a VBScript) to the SP within the VBSript/DTS?
The string looks like "Transactions,24,24" which will then be splitted into the relevant three parts for the SP to be processed.
Thanks
mipo
-
DTSGlobalVariables("myglobalvariable").Value = parameter1frominifile
DTSGlobalVariables("myglobalvariable2").Value = parameter2frominifile
DTSGlobalVariables("myglobalvariable3").Value = parameter3frominifile
'Note: Now I would create an ADO command object and create and append the appropriate parameters:
'Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
Dim cn
Dim cmd
Dim strConn
Dim prm1
Dim prm2
Dim prm3
CONST adInteger = 4
CONST adParamInput = 1
CONST adCmdStoredProc = 4
Set cn = CreateObject("ADODB.Connection")
strConn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='yourdatabasename';Integrated Security='SSPI';"
cn.Open strConn
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "yourstoredprocedurename"
cmd.CommandType = adCmdStoredProc
Set prm1 = cmd.CreateParameter("parameter1namehere", adInteger, adParamInput)
cmd.Parameters.Append prm1
prm1.Value = DTSGlobalVariables("myglobalvariable").Value
'Repeat for the other two parameters:
Set prm2 = cmd.CreateParameter("parameter2namehere", adInteger, adParamInput)
cmd.Parameters.Append prm2
prm2.Value = DTSGlobalVariables("myglobalvariable2").Value
Set prm3 = cmd.CreateParameter("parameter3namehere", adInteger, adParamInput)
cmd.Parameters.Append prm3
prm3.Value = DTSGlobalVariables("myglobalvariable3").Value
Set cmd.ActiveConnection = cn
cmd.Execute
'Clean up here
cn.close
Set cn = Nothing
Set cmd = Nothing
Set prm1 = Nothing
Set prm2 = Nothing
Set prm3 = Nothing
You will need to look up the appropriate values for the ADO constants...you can find these easily on MSDN
by searching for ADO Enumerated Constants
Anyway, this code is off the top of my head..it probably needs to be debugged! Hope this helps...
Michael
Michael Weiss
Michael Weiss -
Another thought...you can use an ini file as the source of global variables in an Execute SQL task...this would necessitate moving the stored procedure outside of the activex script task...
Michael
Michael Weiss
Michael Weiss -
Thanks for the information. Still I do not understand how to overcome the problem if I do not yet know how many lines (e. g. PresenceTickets,24,24) there are in the ini file so I can probably not define "DTSGlobalVariables("myglobalvariable").Value = parameter1frominifile" as I do not know how many GlobalVariables I have.
Or did I understand you wrong?
Mipo
-
OK, lets say your INI file lokos like the following,
[DatabaseCleaner]
! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd>
! -- <N> must be a successive number starting from 1 ...
TableToBeCleaned_1=Transactions,24,24
TableToBeCleaned_2=ECElementValueLog,24,24
TableToBeCleaned_3=Payments,24,24
TableToBeCleaned_4=PresenceTickets,24,24
[OtherSection]
OtherSettings1=1
OtherSettings2=2Here's what I would do. Using ActiveScript open a FileSystemObject on the file. Do a ReadLine until you get to the [DatabaseCleaner] line. Do two more ReadLines to skip over the comments.
Do yet another ReadLine to get first setting.
Check if the line you've read is blank or contains the '[' character. If it does you've finished. You would probably do this check at the start of a Loop.
Then using the InStr and Mid functions break up the parameters and build an T_SQL statement that executes your procedure.
Open an ADO connection and use the Execute method to execute the T-SQL string.
Read another line from the INI file and start loop again.
The code would look something like this
Function Main
Dim oFS ' filesystem object
Dim oFile ' File object
Dim sLine ' text line from file
Dim sParm1 ' parameter 1
Dim sParm2 ' parameter 2
Dim sParm3 ' parameter 3
Dim iChr ' character pointer
Dim sSQL ' SQL string
Dim sConn ' ADO Connection string
Dim oConn ' connection object
Set oConn = CreateObject("ADODB.Connection")
Set oFS = CreatObject("Scripting.FileSystemObject")
sConn = "Provider=SQLOLEDB.1;Persist Security Info=True;"
sConn = sConn & "Password=YourPwd;"
sConn = sConn & "User ID=YourUserID;"
sConn = sConn & "Initial Catalog=YourDatabase;"
sConn = sConn & "Data Source=YourServer"
cn.Open sConn
' Open the file
set oFile = oFS.OpenTextFile("Your INI File")
sLine = ""
' Read a line from the file till we get to the [DatabaseCleaner] section
While sLine <> "[DatabaseCleaner]"
sLine = oFile.Readline
Wend
' read two more lines to skip comments
sLine = oFile.Readline
sLine = oFile.Readline
While (Instr(sLine, "[", 1, vbTextCompare) = 0) AND (IsNull(sLine) = False)
' set character pointer to position of first comma
iChr = InStr(sLine,",",1,vbTextCompare)
' get first parameter
sParm1 = Mid( sLine, InStr(sLine,"=",1,vbTextCompare)+1, (iChr - InStr(sLine,"=",1,vbTextCompare)))
' get second parameter
sParm2 = Mid(sLine, iChr+1, (InStr(sLine, ",", iChr+1, vbTextCompare) - iChr))
' set character pointer to position of second comma
iChr = InStr(sLine, ",", iChr+1, vbTextCompare)
' get third parameter
sParm3 = Mid(sLine, iChr + 1, (InStr(sLine,",",iChr +1 , vbTextCompare) - iChr))
' build SQL statement
sSQL = "EXEC YourStoredProc "
sSQL = sSQL & sParm1 & ", "
sSQL = sSQL & sParm2 & ", "
sSQL = sSQL & sParm3
' execute SQL statement
oConn.Execute sSQL
' read next line from file
sLine = oFile.ReadLine
Wend
' Clean up objects
Set oConn = Nothing
Set oFile = Nothing
Set oFS = Nothing
Main = DTSTaskExecResult_Success
EndNo guarantees that this works, or that the syntax is correct. I did most of it from memory as my SQL Server is out of action.
Feel free to contact me if you need further info.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface -
Actually if yyou are executing the stored procedure from inside the same ActiveX task that you are using to read the ini file, you don't need to use global variables...if you do need the global variables, you can create them on the fly. Otherwise, Phill's code looks like the solution to your dilemma...
Michael
Michael Weiss
Michael Weiss -
Hi
How can I debug such applications as VBScript and is it possible to do it with Visual Studio 6?
Mipo
-
First download the Script Debugger from,
You could also download the documentation files from,
Then right click on the "Data Transformation Service" folder in Enterprise Manager and select Properties. Then click the check box labelled "Turn on just-in-time debugging"
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply