fetching the data from a table using batch file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

fetching the data from a table using batch file

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-14 : 08:03:28

aman writes "can you give me the codes or structure of codes with which i can retrieve data from a database using batch file?"

danielforrester
Starting Member

1 Post

Posted - 2005-10-14 : 12:33:06

Ahmad,

Here is some code for something close to what you want to do which i currently use.

This works by updating the filename property of the Textfile source object you have your DTS package. What you need to do is create a package that reads a textfile into a table then add this code to a activex script task before datapump. Obviously you are going to have to modify it to get it to work but the concept is there.

--------------------------------------------------------
Function Main

Dim sDir, sFilename
Dim fso
Dim oFolder
Dim oFile, sDatasource
Dim oConnection, oRecordset
Dim sSQL
Dim sServerName
Dim oPackage
Dim sFiles_Loaded_DB

sServerName = DTSGlobalVariables("ServerName")
If sServerName = "" Then sServerName = "(local)"

'sFiles_Loaded_DB = DTSGlobalVariables("Files_Loaded_DB")
sFiles_Loaded_DB = "GBR_STAGING"

sDir = DTSGlobalVariables("Directory")
If right(sDir,1) <> "\" Then sDir = sDir + "\"

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open "Provider=sqloledb;Data Source=" + sServerName + ";Initial Catalog=" + sFiles_Loaded_DB + ";Integrated Security=SSPI;"

'Create the Files_Loaded
sSQL = "If Not Exists(Select name From sysobjects Where name='Files_Loaded' and xtype='U')"
sSQL = sSQL + " Create Table Files_Loaded"
sSQL = sSQL + "(row_id integer Identity(1,1),load_date datetime NOT NULL Default(getdate()),filename varchar(256),outcome bit default(0))"

oConnection.Execute sSQL

Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(sDir)
sDatasource = ""

For Each oFile in oFolder.Files
' If lcase(Left(oFile.Name,6)) = "expshk" Then
sSQL = "Select row_id From Files_Loaded Where filename='" + sDir + oFile.name + "' And outcome=1"
oRecordset.Open sSQL, oConnection,3
If oRecordset.RecordCount = 0 Then sDatasource = sDir + oFile.name

oRecordset.close
' End If
Next

DTSGlobalVariables("Datasource") = sDatasource
Set oPackage = DTSGlobalVariables.Parent

'Prevent the package from running
If sDatasource = "" Then
'MsgBox "Disabled"
oPackage.Steps("Load Data File").DisableStep = true
Else
'Run with new source
'MsgBox "Loading"
Set oTextFileConnection = oPackage.Connections("TextFile")
oTextFileConnection.DataSource = sDatasource
sSQL = "INSERT INTO Files_Loaded(filename) Values('" + sDatasource + "')"
oConnection.Execute sSQL
oPackage.Steps("Load Data File").DisableStep = False
End If

oConnection.Close

Set oConnection = Nothing
Set oRecordset = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing

Main = DTSTaskExecResult_Success

End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources