Additional info - the db table it goes into
CREATE TABLE DailyFileRecords(
FN varchar(40),
DATE datetime,
FTYPE int,
IIN varchar(10),
CRN varchar(10),
LUHN varchar(10),
LICENSEE_FORENAME varchar(21),
LICENSEE_SURNAME varchar(21),
Result int,
QueryType int,
ErrorCode varchar(21),
Org varchar(122),
POBox varchar(13),
Subprem varchar(30),
Buildname varchar(21),
Buildnum varchar(21),
Depthoro varchar(81),
Thoro varchar(81),
Deplocal varchar(35),
Local varchar(35),
Town varchar(21),
County varchar(21),
Postcode varchar(8),
Premises varchar(21),
Address varchar(21),
ContAddress varchar(21),
TownCity varchar(21),
CountyReg varchar(21),
PostcodeZip varchar(16),
Country varchar(21),
GENDER varchar(6),
DOB datetime
SENIOR_ID VARCHAR(25),
DISABLED_ID varchar(13),
EMAIL varchar(50),
ETHNIC_ORIGIN VARCHAR(20),
PREF_POST char(1),
PREF_EMAIL char(1),
PREF_SMS char(1),
MOBILE varchar(15),
LICENCE_CATEGORY varchar(36),
LICENCE_TYPE varchar(19),
START_DATE datetime,
START_TIME datetime,
CHANNEL_ID varchar(7),
SERIAL_NO varchar(12),
AMOUNT float,
MOPEX int,
SYSTEM_DATE datetime,
SYSTEM_TIME datetime,
Record int
)
"jojo123" wrote:
> Thanks for the reply - I run the script outside the dts and it runs - no
> errors but again no data goes into the table
>
> sample data (xml) is below:
>
>
>
> 1.1
> EAF1NL20080708.xml
> 20080708 14:07:59
> 1
>
>
>
> 10000001
> 100000001
> 1
>
> John
> Smith
>
> 1
> 1
>
>
>
>
> The Cottage
>
> Fox Lane
>
> Wicks
>
> Bridgeford
> Nottinghamshire
> N14 7LY
>
>
>
>
>
>
>
>
> Male
> 11/08/1983
>
>
>
>
> Y
>
>
>
> New Member
> Full Licence
>
>
> 1234567
>
> 199.00
> 1
> 01/05/2008
> 11:28:30
>
>
> 1.1
> 1
>
>
> "Bob" wrote:
>
>> Hard to tell from that. Have you been able to get the code to run just as
>> VBScript, outside DTS? I'd start there.
>>
>> If it does run successfuly, you may need to look at what service account DTS
>> runs under and what permissions it has.
>>
>> A _small_ amount of sample data and target table DDL might help too ... if
>> you're really stuck.
>>
>> "jojo123" wrote:
>>
>>> Hi
>>>
>>> I have the vbscript below in my dts package and xml schema, where I specify
>>> which sql table field the data would go into - whilst I don't get any errors
>>> - there's no data going into the table - it's completely empty - any ideas?
>>> I'm trying to load the data into a single table
>>>
>>>
>>> VB Script :
>>>
>>> Function Main()
>>> Set objBulkLoad =
>>> CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
>>>
objBulkLoad.ConnectionString = "provider=SQLOLEDB;data
>>> source=localhost;database=myproj; integrated security=SSPI"
>>> objBulkLoad.ErrorLogFile = "c:\error.log"
>>> objBulkLoad.Execute "c:\EAF1NL.xsd", "c:\EAF1NL.xml"
>>> Set objBulkLoad = Nothing
>>> Main = DTSTaskExecResult_Success
>>> End Function
>>>
>>>
>>>
>>>
>>> schema
>>> ----------
>>>
>>>
http://www.w3.org/2001/XMLSchema"
>>> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>>>
>>>
>>>
>>>
>>> sql:is-constant="1" />
>>>
>>> type="Record" sql:is-constant="1" />
>>>
>>> sql:is-constant="1" />
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:datatype="varchar(40)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="DATE" sql:datatype="datetime" />
>>>
>>> type="xsd:string" sql:field="FTYPE" sql:datatype="int" />
>>>
>>>
>>>
>>>
>>>
>>> type="licence" sql:is-constant="1"/>
>>>
>>> sql:field="LICENSEE_FORENAME" sql:datatype="varchar(21)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="LICENSEE_SURNAME" sql:datatype="varchar(21)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> type="pafaddress" sql:is-constant="1" />
>>>
>>> sql:field="GENDER" sql:datatype="varchar(6)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="DOB" sql:datatype="datetime"/>
>>>
>>> sql:field="SENIOR_ID" sql:datatype="varchar(25)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="DISABLED_ID" sql:datatype="varchar(13)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="EMAIL" sql:datatype="varchar(50)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="ETHNIC_ORIGIN" sql:datatype="varchar(20)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="PREF_POST" sql:datatype="char" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="PREF_EMAIL" sql:datatype="char" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="PREF_SMS" sql:datatype="char" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="MOBILE" sql:datatype="varchar(15)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="LICENCE_CATEGORY" sql:datatype="varchar(36)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="LICENCE_TYPE" sql:datatype="varchar(19)" >
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> type="xsd:date" sql:field="START_DATE" sql:datatype="datetime" />
>>>
>>> type="xsd:date" sql:field="START_TIME" sql:datatype="datetime"/>
>>>
>>> sql:field="CHANNEL_ID" sql:datatype="varchar(7)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> sql:field="SERIAL_NO" sql:datatype="varchar(12)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> type="xsd:double" sql:field="AMOUNT" sql:datatype="float" />
>>>
>>> sql:field="MOPEX" sql:datatype="int" />
>>>
>>> type="xsd:date" sql:field="SYSTEM_DATE" sql:datatype="datetime" />
>>>
>>> type="xsd:date" sql:field="SYSTEM_TIME" sql:datatype="datetime" />
>>>
>>>
>>>
>>>
>>>
>>> sql:datatype="varchar(10)">
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
