Problems installing msde2000a

When i try to install MSDE2000A i get the following error message:
The system cannot open the device or file specified.
Internal error 2755. 110
C:\Msde\Setup\SqlRun01.msi
I start with the following parametres:
DISABLENETWORKPROTOCOLS=0 INSTANCENAME="OpfServer" SAPWD="Opf" SECURITYMODE=SQL
Regards,
Olav
Maybe the media you are installing from is corrupt or the file is missing?
Jim
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:BC928DD0-41AC-444E-B211-3A4E7F5800FD@.microsoft.com...
> When i try to install MSDE2000A i get the following error message:
> The system cannot open the device or file specified.
> Internal error 2755. 110
> C:\Msde\Setup\SqlRun01.msi
> I start with the following parametres:
> DISABLENETWORKPROTOCOLS=0 INSTANCENAME="OpfServer" SAPWD="Opf"
SECURITYMODE=SQL
> Regards,
> Olav
|||Hi Jim,
I have just downloaded it from MS and unwrapped it to my harddsisk with the message that it was succesfully delivered. The file SqlRun01.msi is in the Setup directory.
I have uninstalled MS SQL Server 2000 and rebooted after.
Regards,
Olav

Problems in Loading configuration file

Hi,

We have configured package to load configurations from XML file.

But whenever we try to run the package, it throws below warning message

and continues to download the configuration file and finally ending up in low memory exception

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

I have made sure the file exists at the above location.

Please let me know if anyone have any solution on this.

Regards,

sachin

What exactly is the file name you are trying to supply? What are the machine specs where the package is running? What exactly is configured in the xml file?
|||

SachinS wrote:

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

That's an informational message. Where's the error/warning message? That message will occur every single time (and it is valid) that the package runs.|||

We are storing connection strings in this config file.

'Loading configuration settings from file' message gets repeated n number of times and it does not stop until it gets some memory exception

|||Do you have the same configuration file listed multiple times in the package configuration window?sql

Problems importing TXT files using DTS

Hello,

I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

What am I doing wrong? Should I be using something else than DTS?

Any suggestions are greatly appreciated.

Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.

ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,

Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.

I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.

At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.

I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.

Thanks again,
JB|||Yeah, you've got bad data...

I would load the data to a table of a varchars...like you have now

Think of it as a staging table

Then audit the data...

For example

Col030 should be a date

Find out all the rows with bad dates

SELECT * FROM myTable99 WHERE ISDATE(Col030)=0

Will show you rows sql doesn't consider a date...

Same with numerics

ISNUMERIC(col1)=0

You won't be able to load those rows to your final destination table

either fix the input file, or ignore the rows...

in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...

and wait a week while they try to figure out what to do...

Golf, Tennis, Long lunches...whatever you want...|||Brett,

You are correct it was bad data. That should have been the first place I looked.

I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.

Anyways, the problem is solved.

Thanks,
JB

Problems importing a datetime from a flat file

I have a flat file with a datetime field as follows

12419,1,'T','P',229.72,2,'N',2004/may/05 19:47:42.546001
12419,1,'T','R',605.38,76,'N',2004/may/05 19:47:42.546000
12419,2,'T','P',110.49,2,'N',2004/may/05 19:47:42.546003
12419,2,'T','R',215.53,11,'N',2004/may/05 19:47:42.546002
12419,3,'T','F',9.29,1,'N',2004/may/05 19:47:42.546005
12419,3,'T','R',696,38,'N',2004/may/05 19:47:42.546004

I can NOT get last field i.e. the date time field to import into SQL werver 2005 using import wizard.

I can manually enter the value in the table so I know it can handle data in the format it is in.. (table field is date time) but I cant get import wizard to read the file and bring in the data.. It bombs at coloumn 7... which is the date field.. It works fine if I delete the milisecond portion i.e. anything after the dot but I need to keep that information.

Any help will be greatly appreciated.

What type does the SSIS package think the column is? DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

What errors so you get?

Please provide more information.

I strongly suspect that the value is not in a format that SSIS can understand. Your ability to type the value into a column is irrelevant - the informaiton is not stored this way

-Jamie

|||

I have tried with all column options i.e. DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

In every instance it comes back with error message saying

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 4" 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)

When I remove the miliseconds i.e. anything after the decimal point in date time field it works fine.. It also works fine if I have the datetime in the following format

'2004-05-05 19:47:42.547000000' Instead of 2004/may/05 19:47:46.562003

Hope this helps

Thx

Amit

|||

This behaviour is as expected. SSIS will not recognise yyyy/mon/dd hh:mi:ss:XXXXXX. That is not a valid format.

You will need to import it as a string and do a manual conversion using the Derived Column component.

-Jamie

|||

Thank you for your response Jamie. You confirmed my fears. I find it rather surprising that SSIS will read upto seconds but will not read milliseconds portion.. Anyway.. weird are the ways of microsoft and/or other software vendors.. :) Every software has its quirks.. Wish they didnt..

Thank you though for all your help

Thx

Amit

|||

Well those aren't milliseconds. A millisecond is a thousandth of a second hence it will read to hh:mi:ss:XXX but not hh:mi:ss:XXXXXX and I dare say you won't find any other tool that will either!

-Jamie

Problems exporting to Excel

I am having a problem exporting a linked SQL server table from Access 2003
to
Excel. The file is about 2,000 records with several columns. The problem
is that when the table is imported into excel some of the records are not
coming across with the correct data and when numeric fields are summed the
totals are wrong.
When I export directly to Excel from the SQL server using DTS, I don't have
this problem. Has anyone else had this problem? Anyone have ideas of how to
fix it? I need to be able to allow the users to create queries in access
and export the data they need, so setting up a DTS as a solution is not
really workable (or at least I don't think it is).Hi Jim,
Being that this works fine from SQL Server but you are
having problems from MS Access, it's likely an MS Access
issue that you may want to post in one of the MS Access
newsgroups. Maybe try the following group:
microsoft.public.access.externaldata
-Sue
On Mon, 21 Aug 2006 14:52:39 GMT, "JIM" <jcrisp1@.kc.rr.com>
wrote:

>I am having a problem exporting a linked SQL server table from Access 2003
>to
>Excel. The file is about 2,000 records with several columns. The problem
>is that when the table is imported into excel some of the records are not
>coming across with the correct data and when numeric fields are summed the
>totals are wrong.
>When I export directly to Excel from the SQL server using DTS, I don't have
>this problem. Has anyone else had this problem? Anyone have ideas of how t
o
>fix it? I need to be able to allow the users to create queries in access
>and export the data they need, so setting up a DTS as a solution is not
>really workable (or at least I don't think it is).
>

Problems deleting SQL.LOG file

Hi,

I can not delete this file from C/Windows/Temp directory, all other files form that foder I can delete. The error message I get is that something else is using this file. The thing is I cant find what is using it, even though I uninstaled SQL Server 2005 completely. I have tried lots of things, nothing helps. At the end I entered windows in SAFE MODE, went to Computer Management and disabled almost every service I can, that might be using it, and still cant get rid of this SQL.LOG which is 26GB now. The only thing I could do is to compress the file and now it is 6.5GB.

Txs for the help..

This file is a result of ODBC tracing. Here's the article about how to delete it.

http://support.microsoft.com/kb/268591

Thanks,
Sam Lester (MSFT)

Problems creating format file.

sql2k sp4

Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.

declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec

output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO

CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping


DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname

SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''

OPEN FormatCard

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END

SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END

CLOSE FormatCard
DEALLOCATE FormatCard

GO

--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.

Problems creating Error File when using Bulk Insert or BCP from xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

I had this error too. It looks like a bug as BULK INSERT works fine without the -ERRORFILE option. It seems that the execution of the BULK INSERT first creates the file without closing it which results in the error message afterwards. As Workaround just don't use the option

Nobsay

problems connecting to sql express 2005 server, where mdf file go?

Hello, I have correctly installed sql express 2005 onto my company server. I can connect to the sql express 2005 fine within sql management studio express. I can also connect to it the within visual studio 2005 server explorer. I have my database (MDF) on our server. I am trying to run my application within my client computer. I have changed the connection string property to connect to the server:

"Data Source=CTS\SQLEXPRESS;Initial Catalog=UpdateAttributes;Integrated Security=True"

My program will only work if there is a local copy of MDF database located within my application directory. If I remove the local copy, it errors "No process is on the other end of the pipe" Is it possible to have my application without the local copy of the MDF? I thought the idea is to have the MDF on the server. Then my app would not have the MDF copy!?Fixed the problem. Went to the server, changed the sql server 2005 surface area configuration under remote connections from using TCP/IP to TCP/IP and named pipes.

problems connecting to DB.

Hi,

thats what i get when running the app:

Server Error in '/Jobs5' Application.

---

Unable to open the physical file "C:\MPDB\ManPower.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file C:\MPDB\ManPower.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Nothing seems to be using that file. So what is the problem?

Thanks.

The problem is that the DB is being used by the default web server of VS2005 whule i am trying to access using IIS.

the qu. is how do i make my application use IIS insted?

Thanks.

Problem:MS-Access.adp with MSDE link to csv file

Hi. I'm coming from a background of developing mdbs and am trying to migrate
our databases to adps running atop MSDE 2000. However, I've encountered a
problem while trying to do analogous things to what I've done before with
mdbs...for example:

-Linking to a csv file on another machine: I am able to establish a link
uning the 'Link Table Wizard' that shows up as a new view. However, upon
openning the view I see only a single column (left-most).

What am I missing here?

JamesMaybe I can help, drop me a email direct, I do this sort of thing for a
living

Regards
Andrew

JimJimJimJim wrote:

> Hi. I'm coming from a background of developing mdbs and am trying to migrate
> our databases to adps running atop MSDE 2000. However, I've encountered a
> problem while trying to do analogous things to what I've done before with
> mdbs...for example:
> -Linking to a csv file on another machine: I am able to establish a link
> uning the 'Link Table Wizard' that shows up as a new view. However, upon
> openning the view I see only a single column (left-most).
> What am I missing here?
> James
>
>

Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1

problem with workflow in dts designer...looping?

what im trying to do is:

1) ftp a file from a server to a local directory,

2) check the file for its lastmodified date

3) depending on a constraint, perform a data import.

However, if the file is not modified, this means that the server thats supposed to ftp and update the file hasnt done its job yet, in this case i would like dts to wait a few minutes and then again go to step 1. I want this to repeat for a whole hour until the data has finally imported OR if it hasnt imported at the end of the hour it will send me an email saying it failed.

The closest I have come to this type of functionality in DTS designer is picture 1.

In picture 1 i am using the WAITFOR DELAY '000:02:00' to wait 5 seconds between every step. However To do this would require me to create about 30 iterations to span the whole hour! The activeX portion works fine its just learning the flow in dts designer that is giving me the problems.

It would be so much easier to do what I did in picture 2, but nothing runs.

here are the pics:
http://www.geocities.com/samirrahan/picture1.gif
http://www.geocities.com/samirrahan/picture2.gif

I would appreciate any help you can offer.



Thank you.I would do it differently...

Each time I successfully import a file (eg I have found a new file and processed it) I would set the next run date.

I would then set a job up to run the dts package every X minutes (5 for example) between the hours that you expect the file to turn up.

The first step of the package would be to check if it is the run date is less then or equal to today, if it is then you continue processing, if not then you halt processing.

This will solve your problem. Yes, it will mean that the package will run more often then it technically needs to, but it will only do the actual processing once.

HTH|||here is the link to my workflow pictures:
http://www.geocities.com/samirrahan/index.html

ideally i would want the dts to stop running as soon as the file import is successful. i guess i could do that on a success by rescheduling the job.

by the way on another note? do u use the designer or just a vb exe. itself? can this be alot easier if i dont use the designer?

thanks.|||I use the designer...

There is a method to change the jobs schedule using sql. Using sp_add_jobschedule and sp_delete_jobschedule what you could do is...

step 1, check for new file if fail end DTS Pakcage else step 2

step 2, ftp file

step 3, process file

step 4, execute sql task - sp_delete_jobschedule

step 5, execute sql task - sp_add_jobschedule

Problem with uploading data SQL

Hello,

I have problem:

My *.txt file is like it:
"
12345612345678123
abcdefabcdefghabc
" etc.

i want upload data into table (for example TEST) i want to sql read this
file and automatically upload to table.(as job for example)
but i have 3 columns and i dont know how to separate this text to 3 diffrent
text columns

1 column | second column | third column
-------------
123456 | 12345678 | 123
abcdef | abcdefgh |abc

PLEASE HELP ME, i dont know how to do it.

Robert KlomaHi Robert,

i'm using the following stored proc (sp) for this. I suggest to use field
separators to make it
easier to separate the columns. This sp can be executed by a job.
The imported file should look like this:

1234;abcd;1212
321123;kdkdkd;121233

In the sp you have to replace CPRave15 with your database name.

Hope it helps.

Michael Zankl
http://www.zankl-it.de
Berlin

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
================================================== ==========================
==
Syno: imports file specified in @.UNCPathFileName into a table, specified
in @.DBTable
Use ';' as fieldterminator in the imported file

REMARKS:
- User, who runs this SP, has to be a member of SysAdmin
or BulkAdmin
- User must have Insert-Permission on specified Table or
has to be a member of db_owner

TEST:

DECLARE @.RC int,
@.UNCPathFileName varchar(1024),
@.DBTable varchar(128)

SET @.UNCPathFileName = '\\Absrv02\Components\Debitoren.csv'
SET @.DBTable = 'cprSYSMD_DebImp'

EXEC @.RC = cprIMP_File @.UNCPathFileName, @.DBTable
PRINT @.RC

select * from cprsysmd_debimp
--delete from cprSYSMD_DebImp

Author: MZA, http://www.zankl-it.de, 14.01.2003
================================================== ==========================
==
*/
CREATE PROCEDURE cprIMP_File @.UNCPathFileName varchar(1024),
@.DBTable varchar(128)
AS

DECLARE @.RetVal int,
@.Cmd varchar(8000)

--Example
-- BULK INSERT CPRave15.dbo.cprSYSMD_DebImp
-- FROM '\\Absrv02\Components\Debitoren.csv'

SET @.Cmd = '
BULK INSERT CPRave15.dbo.' + @.DBTable + '
FROM ''' + @.UNCPathFileName + '''
WITH (FIELDTERMINATOR = '';'')' --<== IMPORTANT: use a fieldterminator in
imported file

--print @.Cmd
EXEC (@.Cmd)

SET @.RetVal = @.@.ROWCOUNT

RETURN @.RetVal

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"Robert K" <rkloma@.hotmail.com> schrieb im Newsbeitrag
news:bi1qj0$etr$1@.news.onet.pl...
> Hello,
> I have problem:
> My *.txt file is like it:
> "
> 12345612345678123
> abcdefabcdefghabc
> " etc.
> i want upload data into table (for example TEST) i want to sql read this
> file and automatically upload to table.(as job for example)
> but i have 3 columns and i dont know how to separate this text to 3
diffrent
> text columns
> 1 column | second column | third column
> -------------
> 123456 | 12345678 | 123
> abcdef | abcdefgh |abc
> PLEASE HELP ME, i dont know how to do it.
> Robert Kloma
>
>|||"Robert K" <rkloma@.hotmail.com> wrote in message news:<bi1qj0$etr$1@.news.onet.pl>...
> Hello,
> I have problem:
> My *.txt file is like it:
> "
> 12345612345678123
> abcdefabcdefghabc
> " etc.
> i want upload data into table (for example TEST) i want to sql read this
> file and automatically upload to table.(as job for example)
> but i have 3 columns and i dont know how to separate this text to 3 diffrent
> text columns
> 1 column | second column | third column
> -------------
> 123456 | 12345678 | 123
> abcdef | abcdefgh |abc
> PLEASE HELP ME, i dont know how to do it.
> Robert Kloma

One option is to create a staging table with one column, load the data
into that table without changing it, then insert into the final table
like this:

insert into dbo.TEST (col1, col2, col3)
select left(StagingColumn, 6), left(StagingColumn, 8),
left(StagingColumn, 3)
from dbo.StagingTable

Simon|||hello, thank you for hint but:

i want to read this data
My *.txt file is like it:
"
1234567890

" etc

1 column | second column | third column
-------------
12 | 3456 | 789

how to do it ,

> One option is to create a staging table with one column, load the data
> into that table without changing it, then insert into the final table
> like this:
> insert into dbo.TEST (col1, col2, col3)
> select left(StagingColumn, 2), left(StagingColumn, 4),
> left(StagingColumn, 4)
> from dbo.StagingTable

the result is of this is
1 column | second column | third column
-------------
12 | 1234 | 1234