Bulk Load XML into SQL using Schema File ...

Hi ...

I am bulk loading XML data into SQL using

SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I

provide the xml data and a schema file (below).

When I use the attached schema file I get the following error:

No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.

I changed the table definition to allow nulls in the SensorId column

and the bulk load was successful. Notice on the records in the

MacgowanTestRWISRawSurface table … where are the records with the NULL

SensorId(s) in the XML data ? Is this is a side effect of my

self-join with the MacgowanTestRWISRawSurface table ?

Any comments?

Thanks,

Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]

RecordId DataSourceId

ProductInstanceId

SystemId RpuId SensorId ObsDateTime

-- --

-- --

1

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 0

NULL NULL

2

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 1

NULL NULL

3

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 2

NULL NULL

4

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 3

NULL NULL

[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]

RecordId DataSourceId SystemId RpuId SensorId ObsDateTime

-- -- --

1

OH

200 0

0 2005-12-05 15:48:00.000

2

OH

200 0

1 2005-12-05 15:48:00.000

3

OH

200 0

NULL NULL

4

OH

200 1

0 2005-12-05 15:49:00.000

5

OH

200 1

NULL NULL

6

OH

200 2

0 2005-12-05 15:50:00.000

7

OH

200 2

1 2005-12-05 15:50:00.000

8

OH

200 2

NULL NULL

9

OH

200 3

0 2005-12-05 15:45:00.000

10

OH

200 3

1 2005-12-05 15:45:00.000

11

OH

200 3

NULL NULL
//////////////////////////////////////////////////////

// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:annotation>

<xsd:appinfo>

<sql:relationship name="MacgowanTest_Atmospheric_Surface"

parent="MacgowanTestRWISRawAtmospheric"

parent-key="SystemId RpuId"

child="MacgowanTestRWISRawSurface"

child-key="SystemId RpuId" />

</xsd:appinfo>

</xsd:annotation>

<xsd:annotation>

<xsd:appinfo>

<sql:relationship name="MacgowanTest_Surface_Surface"

parent="MacgowanTestRWISRawSurface"

parent-key="SystemId RpuId"

child="MacgowanTestRWISRawSurface"

child-key="SystemId RpuId" />

</xsd:appinfo>

</xsd:annotation>

<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"

sql:relationship="MacgowanTest_Atmospheric_Surface">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"

sql:relationship="MacgowanTest_Surface_Surface" >

<xsd:complexType>

<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>

<xsd:attribute name="datetime" type="xsd:date"

sql:field="ObsDateTime" />

<xsd:attribute name="surfacecondition" type="xsd:string"

sql:field="SurfaceCondition"/>

<xsd:attribute name="surfacetemp" type="xsd:string"

sql:field="SurfaceTemperature"/>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>

<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

//////////////////////////////////////////////////////

// Xml Data

<?xml version="1.0"?>

<odot_rwis_site_info>

<site id="200000" number="1" sysid="200" rpuid="0"

name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">

<atmospheric datetime="12/05/2005 03:48:00 PM"

airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"

windspeedgust="19" winddirectionavg="265" winddirectiongust="295"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="450" waterlevel="0">

<traffic

datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"

volume="21" sftemp="1900" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

<site id="200001" number="2" sysid="200" rpuid="1"

name="2-Hardin County Garage" longitude="-83.6148588"

latitude="40.6305358">

<atmospheric datetime="12/05/2005 03:49:00 PM"

airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9"

windspeedgust="22" winddirectionavg="303" winddirectiongust="299"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-410" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="300" waterlevel="0" />

</sensors>

</site>

<site id="200002" number="3" sysid="200" rpuid="2"

name="3-US24 @. Indiana Line" longitude="-84.769597"

latitude="41.17308">

<atmospheric datetime="12/05/2005 03:50:00 PM"

airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9"

windspeedgust="19" winddirectionavg="279" winddirectiongust="313"

pressure="65535" precipitationintensity="Light"

precipitationtype="Snow" precipitationrate="115"

precipitationaccumulation="-1" visibility="2000" />

<sensors>

<surface id="0" datetime="12/05/2005

03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="-100" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="160" waterlevel="0">

<traffic

datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89"

volume="16" sftemp="-100" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-130" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

<site id="200003" number="4" sysid="200" rpuid="3"

name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">

<atmospheric datetime="12/05/2005 03:45:00 PM"

airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6"

windspeedgust="20" winddirectionavg="216" winddirectiongust="275"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="100" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="300" waterlevel="0">

<traffic

datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108"

volume="11" sftemp="100" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-310" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

</odot_rwis_site_info>

It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.

Bulk Load XML into SQL using Schema File ...

Hi ...
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris

[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId ProductInstanceId SystemId RpuId SensorId ObsDateTime
-- -- -- --
1 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 0 NULL NULL
2 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 1 NULL NULL
3 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 2 NULL NULL
4 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 3 NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1 OH 200 0 0 2005-12-05 15:48:00.000
2 OH 200 0 1 2005-12-05 15:48:00.000
3 OH 200 0 NULL NULL
4 OH 200 1 0 2005-12-05 15:49:00.000
5 OH 200 1 NULL NULL
6 OH 200 2 0 2005-12-05 15:50:00.000
7 OH 200 2 1 2005-12-05 15:50:00.000
8 OH 200 2 NULL NULL
9 OH 200 3 0 2005-12-05 15:45:00.000
10 OH 200 3 1 2005-12-05 15:45:00.000
11 OH 200 3 NULL NULL

//////////////////////////////////////////////////////
// Schema File

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>

<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string" sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string" sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>

<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

//////////////////////////////////////////////////////
// Xml Data

<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1" name="2-Hardin County Garage" longitude="-83.6148588" latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM" airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9" windspeedgust="22" winddirectionavg="303" winddirectiongust="299" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-410" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2" name="3-US24 @. Indiana Line" longitude="-84.769597" latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM" airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9" windspeedgust="19" winddirectionavg="279" winddirectiongust="313" pressure="65535" precipitationintensity="Light" precipitationtype="Snow" precipitationrate="115" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="-100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="160" waterlevel="0">
<traffic datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89" volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-130" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3" name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM" airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6" windspeedgust="20" winddirectionavg="216" winddirectiongust="275" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0">
<traffic datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108" volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-310" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.

Bulk Load problem - error connecting

I get the following error when trying to use the SQL Bulk Load object.

"Error connecting to the data source." The datasource is used right above this code to successfully read from the database. It is the last line that blows up.

Here is the problem code:

connStr = "provider=SQLNCLI;Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad

objBL.ConnectionString = connStr

objBL.BulkLoad = True

objBL.XMLFragment = True

objBL.KeepIdentity = False

objBL.ErrorLogFile = "C:\BulkLoadErrors.xml"

objBL.Execute(SchemaFile, datafile)

(SchemaFile and datafile are strings containing the full file name and path)

Use the connection string as follows :

ConnStr = "provider=sqloledb;data source=myserver;database=mydb;integrated security=SSPI;"

It should work.

Thanks.

Naras.

|||Thank you! That did it. Annoying part is the connection string I was using was taken straight from the MSDN examples.

Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.
|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.

sql

Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.
|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.

Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.
|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.

Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.
|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB Destination instead of SQL Server Destination.can someone help me in this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.

Bulk Load Failure

I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.

After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.

I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!

I have the same thing happening. I can't find anything either. I hope someone can help us with an answer. It is a maintenance pain.

|||

I am working with Microsoft Support to try and determine the cause of the problem. When it has been resolved I will post it here. One thing you can do to slow down the occurance of the problem is to add the parameter -g512 or -g1024 to your Sql Server startup. Addint this parameter slowed the occurances from 1-2 a day to 1 every other day.

I hope this helps.

-Mark.

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you

need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.|||

Kaarthik Sivashanmugam wrote:

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74428&SiteID=1

To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

I've posted to a couple of the forum threads that discuss this topic. I am getting this exception, too. However, I am using OLEDB connections and they work sometimes and sometimes they don't. I can't figure it out. Here is a specific of what I am receiving. Maybe someone will see something in the error that I am overlooking.

My file is there. I can see the columns, data in the file source connection. So, it isn't that my file doesn't exist. But, where is this Global\DTSQLIMPORT file? Why would this run sometimes and then decide not to run only to run again some other time with no changes having been made?

Thanks in advance.

[SQLDest TE [45]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

|||

Mark,

I tried adding the -g1024 parameter to my startup. My SQL Server 2005 is on a two-node cluster. After saving the change, I attempted to restart SQL Server. It would not restart. So, I tried removing the startup parameter that I added and then restarting again. It still won't restart. And, the parameter reappears after every attempt to restart the service.

I am hoping you or someone has an idea of how to get back to where I was. Good news is that this is not my production environment.

Thanks,

sk

|||I ended up having to remove the parameter from the registry. Now, I can restart SQL Server.|||

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

|||

Pam B wrote:

I just started working with SSIS and I'm having the same problem. Has anyone come up with a good solution? The text file I'm trying to load is on the same server as SQL Server and the database but I get the following message:

Error: 0xC022009 at ..........(name of my task)........................., SQL Server Destination [2026]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. SOurce: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see the file data in the tasks at design time.

Any help is appreciated.

Are you scheduling the package? If so, check that the user account the SQL Server service uses has access to the file.|||

Sorry about the needless post (but maybe this will help someone else out there) I kept looking and found a post where someone said that if you are not running your SSIS package on the Server with the DB to use the OLE DB Destination instead of the SQL Server Destination. I changed this to an OLE DB Destination and it worked! Hope this helps someone.

Pam

|||I am getting the same error. i dont have option to use OLEDB

Destination instead of SQL Server Destination.can someone help me in

this.|||

Ramvarun wrote:

I am getting the same error. i dont have option to use OLEDB

Destination instead of SQL Server Destination.can someone help me in

this.

Why not?|||

Hi,

I am using Data Flow task and the OLEDB destination. It worked when i truncate the existing content in table and load it again. But when i tried to load a table with data already present in it, then i get the error

[tTaskName] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

Tried SQL Destination also, but no use. Is there any solution? I do not have access to restart the SQL server also :-( So i am not able to check.

Regards,

|||Pam thank you that worked. Changing the stock control from SQL Server Destination to OLE DB Destination.|||Thanks Kaarthik. Solved my problem immediately.

Bulk import images to binary object in SQL 2000

I have a web application that I am rebuilding. I have many picture files that want to take off the file system and move into SQL as a blob. I will create an index of uids against the file names but need a good way to bulk add the files to the database... any hints on code or tools would be a great help.

Thanks

Bill

You can use a utility 'textcopy' in 'Command Prompt' under the SQL instance directory:

C:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn

FYI, you can use it as following, make sure the command in a single line:

====Copy data from file to table====

textcopy /S labsh96223\iori2000 /I /F c:\xml\iori.bmp /D northwind /T testImage /C img /U sa /P /W "where idx=2"
====Copy data from table to file====

textcopy /S labsh96223\iori2000 /O /F c:\xml\iori.jpg /D northwind /T testImage /C img /U sa /P /W "where idx=2"

You can also use some programming code (written in VC++,C# etc.) to manipulte the binary data of BLOBs between files and database.

building a program model

could someone show me how to go about modeling an object oriented data access application using SQL server and .Net. what I'm looking for is a somewhat extensive project (such as northwind traders) with which to play around and get some ideas. good links to look up would also be appreciated

Thread moved to SQL Server Data Access as this forum is about coding the CLR running inside SQL Server.

Niels