I have a Bulk insert that doesn't load but doesn't error,
SET @SQL= 'BULK INSERT dbo.LexisNexis_import_BANKRUPTCY FROM ''' + @ImportFile + ''' WITH (FIRSTROW = 2, FORMATFILE = ''' + @FormatFilePath + ''' )'
EXEC(@SQL)
All columns in the csv are double quoted so I stip them out in a format file.There is data in the source file. Why this Isn't working?
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
Is that possible to load files (*.bmp, *.jpg etc) to table (field type IMAGE) using BULK INSERT?
Or is it better to do it otherwise?
Thanks
I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4)  which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is.  We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC  is handling the bulk update is  a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.
It will be impossible  for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..
At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.
-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw
The customer has SQL Server 2008 R2 Express installed
The connection string to the database works on everything else and it is the sa account with password
On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.
I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).
Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.
How shall I go about it?
many thanks! :D
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
Hi, folks:
View 18 Replies View RelatedSince I have to go across the network, I'm trying to use the UNC. However, this won't even work when I'm using the UNC to point to the server on which this is run. I'm trying to restore a single table on 6.5. What is the obvious piece that I'm missing?
This works.
LOAD TABLE address
FROM DISK = 'd:MSSQLackupDBBackup.DAT'
WITH source='address'
This doesn't.
LOAD TABLE address
FROM DISK = 'server1d$MSSQLackupDBBackup.DAT'
WITH source='address'
Hello !
Please, this is an urgent call; May be someone issued the sama probleb I do :
When I'm trying to import a XML file, that contains diffgram, using the XML Source task in SSIS, I choose an Inline Schema Option - everything goes well...the tables and columns are displayed, and the Import task into a Database's table succeds.
The problem is that , it doesn't load any data into the table, though there are plenty .
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 'GlobalDTSQLIMPORT ' 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!
Hi All,
Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?
This is my current sql statment to load data.
EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'
SET ANSI_WARNINGS OFF
BULK INSERT mystuff.dbo.[v1]
FROM 'c:myfile.txt'
WITH
(
FIRSTROW = 1,
FORMATFILE = 'c:scriptsv1.fmt',
MAXERRORS=2000,
ROWS_PER_BATCH=100000
)
Thanks,
Mike
I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
Any comments are appreciated,
-Marc
hi all,
I am getting this error when I try to insert an image or update the database image. I am the owner of the database. I am trying to insert or update using an ASP.Net form (published) from a remote computer. Recently I changed a few permissions (added a user with login name and password) to that database to allow Reporting services access to all my group employees. Earlier everything was working absolutely fine. But now it is denying permissions when I try to insert or update the data when I access it from a remote system. I am able to get the data incase I do a get using the ID. But if I start the application on the localhost (Start Debugging) then its working fine and i am able to insert the data. Can someone suggest what could be wrong?
Check it out!!!! There is an exception!!!!! System.Data.SqlClient.SqlException: You do not have permission to use the bulk load statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.Insert_Method()
Thanks
Is there a way to use the script file from one database to load all the stored procedures into another database? I have used DTS to do them one at a time since the SQL task is very limited in the amount of text that it will handle. There has to be a way to use a script filel with all of the procedures in it to load a new database. Any suggestions are appreciated.
View 2 Replies View Relatedi have the stored procedure...
ALTER procedure [dbo].[xmlpaths]
as
Declare @xml VARCHAR(MAX)
Declare @i as int
select @xml=BulkColumn from openrowset(bulk 'C:Documents and SettingsKasiDesktopote.xml', single_clob)as cse
EXEC sp_xml_preparedocument @i OUTPUT,@xml
Select * From OpenXML(@i,'/college/cse',2) With (name varchar(50), rollno int, year int)
i got the output...
but i want to give path as parameter during execution of procedure name. can anyone help me..
thanks in advance
I have a file which has * as the field delimiter and ~ as the record delimiter, but I don't know how much columns each row will have. Only known is the maximum which can be 15.
The file looks something like: A1*A2*A3*~B1*B2~C1*C2*C3*C4*C5~
SO I have created a table with 15 columns(since 15 can be the max) but now when I try to insert it to that table, I inserts only the entire file into 1 single column.
The command which I am using is: BULK INSERT tablename FROM filename WITH (FIELDTERMINATOR = '*' , ROWTERMINATOR = '~')
but this is not giving the correct output.
The output expected is
A1 A2 A3
B1 B2
C1 C2 C3 C4 C5
I am trying to configure a bulk table upload from a 2003 Access table to a matching table in SQL with SSIS. I can configure the source file but am unable to configure the destination. When I configure the SQL source and use SQL Native Client I get an error message of:
The selected connection manager uses an earlier version of a SQL server provider. Bulk insert requires a connection that uses a SQL server 2005 provider.
When I go through the new connection setup I don't see any available provider named like that. I believe the SQL server I am loading to is a 2003 version.
I need to execute the following:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
before I do a bulk load because the table I am inserting into has an indexed view created on it. Whats the best way to set these options prior to a bulk load?
works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
I need to update a number of sql server tables, the data sources for these coming from a number of stored procedures. I want a generic way of getting the data and then passing this data to the tables.I am thinking of doing this for each table:Populating a datasetWriting this dataset to XMLUsing SQLXML Bulk Load to pass this XML to the database to updateI can create the xml data file by:
dataset.WriteXml("C:data.xml")The problem I have is that the example (http://support.microsoft.com/default.aspx/kb/316005/en-us) I looked at relies on the schema being defined:<?xml version="1.0" ?><Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql" > <ElementType name="CustomerId" dt:type="int" /> <ElementType name="CompanyName" dt:type="string" /> <ElementType name="City" dt:type="string" /> <ElementType name="ROOT" sql:is-constant="1"> <element type="Customers" /> </ElementType> <ElementType name="Customers" sql:relation="Customer"> <element type="CustomerId" sql:field="CustomerId" /> <element type="CompanyName" sql:field="CompanyName" /> <element type="City" sql:field="City" /> </ElementType></Schema>Is there any way I can create the schema 'on the fly' similar to how I did for the data source file.As I could then pass these files to the database:objBL.Execute ("schema.xml","data.xml");
I have a VB.NET scheduled job as a task scheduler ( windows 2012) that calls a stored procedure that bulk inserts. I have added the user in the server role "bulkadmin" yet I get the "You do not have permission to use the bulk load statement error"
System.Data.SqlClient.SqlException (0x80131904): You do not have permission to use the bulk load statement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
[code]....
hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι�„�‰Î»Î¿Î±ÎºÎ±��ν16/11/1909
2 102Α��γολίδο�‚29/04/1949
3 103Α��καδία�‚16/11/1909
4 104Ά���„η�‚ 16/11/1909
5 105Α�„�„ική�‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile
DATAFILETYPE = 'char'
FIELDTERMINATOR=' '
ROWTERMINATOR='
'
KEEPNULLS
)
As a result of the above configuration I get this:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
Hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι�„�‰Î»Î¿Î±ÎºÎ±��ν16/11/1909
2 102Α��γολίδο�‚29/04/1949
3 103Α��καδία�‚16/11/1909
4 104Ά���„η�‚ 16/11/1909
5 105Α�„�„ική�‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile,
DATAFILETYPE = 'char',
FIELDTERMINATOR=' ',
ROWTERMINATOR='
',
KEEPNULLS
)
As a result of the above configuration I get this:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am trying to load a simple Excel file into a Database table and the SSIS Package is not loading any records beyond 3233 records. I am just surprised. I tried using the "IMEX=1" mentioned in some of the online resources but it didn't work. I am using an Excel Source, a Data Conversion Transformation and an OLEDB Destination in my package in SQL Server 2014 (which is pretty simple and straightforward).The Excel file I am trying to load can be found here.
And, here is my table structure.
CREATE TABLE [gov].[loan_limits](
[FIPS_State_Code] [varchar](3) NOT NULL,
[FIPS_County_Code] [varchar](3) NOT NULL,
[County_Name] [varchar](50) NOT NULL,
[State] [varchar](2) NOT NULL,
[CBSA_Number] [varchar](6) NOT NULL,
[code]...
Hi All,I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?Thanks,Claude.
View 3 Replies View RelatedFor Bulk Load requests in SQL server, Are there any specific profiler event? Like the one we have for RPC RPC:Starting and for Batch Requests, we have SQL:BatchStarting.
Are Bulk Load requests that are being monitored through Profiler captured as SQL:Batch... events at the backend?
Are there any new features added in 2012 or 2014 to identify a Bulk request submitted through bcp.exe utility or any other sqlbulkcopy program?
I want to import the contents of a remote database every morning via a Web service to populate a data mart. I am looking for the most efficient way to do this. Step 1 is to use the Web Service Task in SSIS to grab the data and save to an XML file (since the content is far too large to store in the String data type).
For step 2, I can either use the SQLXMLBulkLoad object in VB Script to read in the file and populate the tables. Or I can using the XML Source object in Integration Services. It is clear that SQLXMLBulkLoad is the most efficient way to load data, but is the XML Source object in SSIS just a graphical representation of the SQLXMLBulkLoad object, or is it something else entirely that is inefficient because it requires loading of the XML content entirely into RAM before it can process the XML?
Any other suggestions are welcome.
SQL Server 2005 SP2.
Thank you,
Mike Chabot