Error when importing XML to SQL-server using activX

I have tried this example to import XML to SQL-server:
http://www.perfectxml.com/articles/X...rtXMLSQL.asp#3
But i get an error when the import is finish:
Error Source: Microsoft Data Transformation Service (DTS) Package
Error Description: ActiveX Scripting encounted a Run Time Error during
the execution of the script.
The data is imported to the table as it should
What could be the problem?//Peter
"Peter" <dj-pnr@.get2net.dk> wrote in message
news:4073f900@.news.wineasy.se...
[snip]
> The data is imported to the table as it should
> What could be the problem?//Peter
If the data gets imported, then it is probably a problem with syntax at the
end of your script. Check to make sure it is correct.
Bryant
|||I have the following code:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Dim objXBulkLoad
Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objXBulkLoad.ConnectionString =
"PROVIDER=SQLOLEDB.1;SERVER=000.000.000.000;UID=ad m;PWD=password;DATABASE=No
rthWind;"
objXBulkLoad.KeepIdentity = False
'Optional Settings
objXBulkLoad.ErrorLogFile = "c:\NWError.LOG"
objXBulkLoad.TempFilePath = "c:\temp"
'Executing the bulk-load
objXBulkLoad.Execute "c:\books.xsd", "c:\books.xml"
Main = DTSTaskExecResult_Success
Can you see any errors?
"Bryant Likes" <bryant@.suespammers.org> skrev i en meddelelse
news:uekmKakHEHA.3476@.TK2MSFTNGP11.phx.gbl...
> "Peter" <dj-pnr@.get2net.dk> wrote in message
> news:4073f900@.news.wineasy.se...
> [snip]
> If the data gets imported, then it is probably a problem with syntax at
the
> end of your script. Check to make sure it is correct.
> --
> Bryant
>
|||"Peter" <dj-pnr@.get2net.dk> wrote in message
news:407bedf5$1@.news.wineasy.se...
[snip]
> Main = DTSTaskExecResult_Success
> Can you see any errors?
It doesn't look like you have a Function called main, so that last line
would cause the error. You should wrap everything in a function like:
Function Main()
''' Insert all your code here
Main = DTSTaskExecResult_Success
End Function
Bryant
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:23306
Tanks! That solved my problem
"Bryant Likes" <bryant@.suespammers.org> skrev i en meddelelse
news:%237pbt4dIEHA.3964@.TK2MSFTNGP10.phx.gbl...
> "Peter" <dj-pnr@.get2net.dk> wrote in message
> news:407bedf5$1@.news.wineasy.se...
> [snip]
> It doesn't look like you have a Function called main, so that last line
> would cause the error. You should wrap everything in a function like:
> Function Main()
> ''' Insert all your code here
> Main = DTSTaskExecResult_Success
> End Function
> --
> Bryant
>

error when importing text file - saving in notepad first works

I'm trying to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message

Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

The column 19 which reported a problem contains this information:
?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067

However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file.

The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing.

The column has datatype DT_STR with a width of 500.

I use Locale Swedish and when I save in Notepad it is saved in ANSI,

I use Windows XP Swedish version.

Try DT_WSTR instead as its unicode.

There could be a control character in that field that gets thrown away when you save in Notepad.|||

Had the same error importing a text file. Error was resolved by changing the dataype as you advised. Thanks!

error when importing text file - saving in notepad first works

I'm trying to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message

Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

The column 19 which reported a problem contains this information:
?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067

However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file.

The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing.

The column has datatype DT_STR with a width of 500.

I use Locale Swedish and when I save in Notepad it is saved in ANSI,

I use Windows XP Swedish version.

Try DT_WSTR instead as its unicode.

There could be a control character in that field that gets thrown away when you save in Notepad.|||

Had the same error importing a text file. Error was resolved by changing the dataype as you advised. Thanks!

Error when importing data from Excel File

Hi!

This is what i'm doing:

IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=

0 AND srv.srvname = N'ExcelSource')

EXEC master.dbo.sp_dropserver @.server=N'ExcelSource', @.droplogins='droplogins';
-
EXEC master.dbo.sp_addlinkedserver
@.server = 'ExcelSource',
@.srvproduct = 'Excel',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.datasrc = @.Chemin,
@.provstr = 'Excel 8.0';

EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = 'ExcelSource',
@.useself = false,
@.locallogin = NULL,
@.rmtuser ='ADMIN',
@.rmtpassword = NULL;

set @.NomServ = 'ExcelSource';

This create a linkedServer to read my ExcelFile.

Then i'm doing this:

EXEC ('Insert into Elements (No_element, Nom_elem, Desc_elem, Code_grpe_classe, Tps_elem, Code_sgrpe, Code_produit)
Select No_element, Nom_elem, Desc_elem, Code_grpe_classe, Tps_elem, Code_sgrpe, Code_produit
from ' + @.NomServ + '...[Elements$];')

This is where i got an error. The error is:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" does not contain the table "Elements$". The table either does not exist or the current user does not have permissions on that table.

I can't figure out what i'm missing. I've add permissions for EVERYONE on the file and on the folder just to be sure and i still have the same error. How can i check if the table [Elements$] exist ?
I've forgot to mention that my excel spreadsheet name is Elements.|||I finally found what the problem was. One of my column name had the wrong Column name. Now it's working fine !

Error when importing data from AS/400 - appeared after SP1

I cannot transfer a list of tables from AS/400 to SQL 2005, in SQL Server
Import and Export Wizard.
This is what happends:
In SQL server Import and Export wizard, I choose IBM AS400 OLE DB Provider,
with Properties:
Data Source: (IP adress of my AS/400)
User name, password: (My username/password on AS/400)
Default Collection: (library on AS/400)
Then I choose destination database, and Next, it starts transfering list of
tables . After a few seconds I get this error:
'IBMDA400.DataSource.1' failed with no error message available, result code:
E_INVALIDARG (0x80070057)
Additional Information:
'IBMDA400.DataSource.1' failed with no error message available, result code:
E_INVALIDARG (0x80070057). (System.Data)
Error appeared shortly after installation of SP1, so I suspect of SP1
(although I am not yet sure) . Error happends always, nevermind the number
of tables in library.
This is important error for us because we cannot do transfer from AS400 at
all.I have gotten the same error and I was wondering if you had found an
answer to this problem?
*** Sent via Developersdex http://www.codecomments.com ***sql

Error when importing Access report

Hi! When i'm importing into ReportServer report made with Access i've got those errors:

An error occurred while the report Bank Account Transactions By Type was being imported: Item has already been added. Key in dictionary: '[Bank_x0020_Account_x0020_Transactions_x0020_By_x0020_Type].Parameters![trans type]' Key being added: '[Bank_x0020_Account_x0020_Transactions_x0020_By_x0020_Type].Parameters![trans type]'

An error occurred while the report Bills from Purchase Orders by Supplier was being imported: Item has already been added. Key in dictionary: '[Bills_x0020_from_x0020_Purchase_x0020_Orders_x0020_by_x0020_Supplier].Parameters![vendor]' Key being added: '[Bills_x0020_from_x0020_Purchase_x0020_Orders_x0020_by_x0020_Supplier].Parameters![vendor]'

What is the meaning of those errors? I thought it was because ReportServer was trying to add 2 or more parameter with the same Name but for example in my report Bank Account Transactions By Type i only have one textbox that use the name trans type. So is someone have an idea?

Thanks !
It hapened to me, more or less the same...

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose

Error when importing Access report

Hi! When i'm importing into ReportServer report made with Access i've got those errors:

An error occurred while the report Bank Account Transactions By Type was being imported: Item has already been added. Key in dictionary: '[Bank_x0020_Account_x0020_Transactions_x0020_By_x0020_Type].Parameters![trans type]' Key being added: '[Bank_x0020_Account_x0020_Transactions_x0020_By_x0020_Type].Parameters![trans type]'

An error occurred while the report Bills from Purchase Orders by Supplier was being imported: Item has already been added. Key in dictionary: '[Bills_x0020_from_x0020_Purchase_x0020_Orders_x0020_by_x0020_Supplier].Parameters![vendor]' Key being added: '[Bills_x0020_from_x0020_Purchase_x0020_Orders_x0020_by_x0020_Supplier].Parameters![vendor]'

What is the meaning of those errors? I thought it was because ReportServer was trying to add 2 or more parameter with the same Name but for example in my report Bank Account Transactions By Type i only have one textbox that use the name trans type. So is someone have an idea?

Thanks !
It hapened to me, more or less the same...
For what I know it can be a problem with the default values of the Report Parameters... they should not be originated by a query.
I had this problem and puting this as static or calculated values solved the issue...
Not the best way as by default the user's are not getting the data shown, but that's a solution.
Hope Microsoft solves the issue.. as this is a serious bug.
Regards,
Jose

Error using /SET

I have a data importing .dtsx file that I would like to point towards another server on occasion. I tried:

DTEXEC /FILE "fubar.dtsx" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString];Data Source=NEWSVR;Initial Catalog=Fubar;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

but I keep getting the error:

Argument ""\Package.Connections[ConnectionManagerName].Properties[ConnectionString];Data Source=NEWSVR;Initial Catalog=Fubar;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=False;"" for option "set" is not valid.

Staring at the XML within the .dtsx file gave me no insight other than I suspect the "ConnectionManagerName" is the problem. How do I figure out the magic incantation? It's definitely not as trivial as using '-S NEWSVR' on SQLCMD!
You need to put double quotes around the connection string, first of all.

You also might want to try using the /CONN switch instead. Search this forum for examples.

Error translations

I'm importing data from a flat file into a SQL Server table. Yes, I have this thing mapped correctly, data converted correctly, data scrubbed correctly. I've tested this against 14 successive files dumped from the source system and everything works perfectly...until today. Now I'm getting the following gobbledygook.

I have absolutely no clue where to even begin looking. I've already inspected every single column in the input file that is converted to something other than a string. Absolutely nothing shows up as being an overflow or truncation issue. I've calculated the maximum length of every single text column being imported and none of them overflow the destination either. Where do I even start in this completely uninformative junk to even try to figure out where the issue is?

Error: 0xC02020C7 at Load Tables, MLBAMBio [1656]: The attempt to send a row to SQL Server failed with error code 0x80004005.

Error: 0xC0047022 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "MLBAMBio" (1656) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020C7. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread7" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0202009 at Load Tables, MLBAMBio [1656]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E57.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E57 Description: "The statement has been terminated.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E57 Description: "String or binary data would be truncated.".

Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread7" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread9" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread9" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at Load Tables, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Load Tables, Biography [951]: The processing of file "D:\FTPtransfersource\curbio.csv" has ended.

Error: 0xC02092B5 at Load Tables, MLBAMBio [1656]: A commit failed.

Error: 0xC0047018 at Load Tables, DTS.Pipeline: component "MLBAMBio" (1656) failed the post-execute phase and returned error code 0xC02092B5.

Have you tried changing the OLEDBsource &* connection manager, as I have fixed it earlier for the "DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread9" has exited" issues (trying to workaround).|||Can you set up error redirection to capture the rows that are causing the problem?|||

I would start looking for the field(s) that make it fail.

If the error redirection does not help, try splitting your file in halves (binary search) until you get close to the source of the problem.

Thanks.