Importing XML into SQL using DTS

I need to import large XML files into an SQL table.
My XML experience is minimal...
My current DTS script can import xml files which are more structured (using
NODES) and works fine. How ever i need to modify it to look at Attributes
instead of nodes.
It looks like this:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objXMLDOM
Dim objNodes
Dim objBookNode
Dim objADORS
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load
"U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
Set objNodes = objXMLDOM.selectNodes("/Books/Book")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic
For Each objBookNode In objNodes
With objADORS
.AddNew
.fields("BookTitle") = objBookNode.selectSingleNode("Title").nodeTypedVal ue
.fields("Publisher") =
objBookNode.selectSingleNode("Publisher").nodeType dValue
.fields("DateOfPurchase") =
objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
Main = DTSTaskExecResult_Success
End Function
#################
How do i modify it to look at an XML file structured using Attributes?
...XML File looks like
The xml structure looks like this:
<?xml version="1.0" encoding="utf-8"?>
<usageFile source="abc" countRetrievals="12345" countSearches="0"
fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
<BookTitle="abc123" Publisher="abcdef"
DateOfPurchase="2005-05-16T05:45:36"/>
Thanks for the help
Did you try to use '@.Publisher' instead of 'Publisher' in your path
expression?
Best regards
Michael
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help
|||Try to use attributes property on Dom nodes to get the values of attributes:
http://msdn.microsoft.com/library/de...65757ceb24.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help

Importing XML File using SSIS (DTS)

I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.

Has anyone tried this? If so and you were successful, what were the steps to set up the package?

Thanks.

Have you tried to use XML Source adapter?

- Grab a Data Flow task from the toolbox

- Double-click on the added task

- Double-click on the XML Source in the data flow toolbox

- Double-click added component on the diagram

- Set-up your XML sorce

HTH.

|||

Hi HTH,

After reading your post, i was trying to dothe same thing using the below xml:

<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>

and i even generated the XSD using SSIS and got this :

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="country">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="population" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

and now after pointing things correctly in my connection manager when i go to my columns all i get is one column which says Name..

why is it so? ideally i should get Name and Pollution right..? Can you advice..?

|||

Try wrapping the xml in a root node:

<root>
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
</root>

Thanks
Mark

|||

Mark,

Thanks It worked.

So the impression that i get is that all XML's should be waped in a root node. Is it True

Importing XML File using SSIS (DTS)

I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.

Has anyone tried this? If so and you were successful, what were the steps to set up the package?

Thanks.

Have you tried to use XML Source adapter?

- Grab a Data Flow task from the toolbox

- Double-click on the added task

- Double-click on the XML Source in the data flow toolbox

- Double-click added component on the diagram

- Set-up your XML sorce

HTH.

|||

Hi HTH,

After reading your post, i was trying to dothe same thing using the below xml:

<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>

and i even generated the XSD using SSIS and got this :

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="country">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="population" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

and now after pointing things correctly in my connection manager when i go to my columns all i get is one column which says Name..

why is it so? ideally i should get Name and Pollution right..? Can you advice..?

|||

Try wrapping the xml in a root node:

<root>
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
</root>

Thanks
Mark

|||

Mark,

Thanks It worked.

So the impression that i get is that all XML's should be waped in a root node. Is it True

Importing Views

How do you import a view from one database to another? I tried using the DTS wizard but when I pick the view I want to import, it is imported as a table and not a view. And I don't see anything in Management studio then can help me. Does anyone have any suggestions.

hi,

try generating the DDL script for the desire view, and then run it against the desired database..

regards

sql

Importing times into SQL Server

I am trying to import an access database into SQL Server 2000.
I am using the DTS Import wizard to to this.

Most of the tables import fine, but a few are not importing.

Here is the error message that occurs on most of them:

Code Snippet

Table Name Here
Error at destination for Row number 1. Error encountered so far in this task:

1. Insert error, column 7 (‘CallTime’, DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.

Here is an example of an entry in one of the 'Calltime' rows: 7:45:30PM.
This is a date/time field in Access.

I looked at this SQL table after the import finished and there was no data.
Also, it tried to import as a smalldatetime data type.

Is there something I can change in Accesss to fix this problem?

Perhpas you should identify with a query instead of a pure table mapping which values break the import process. Use the ISDATE function for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Importing text files - DTS help needed

Hi all

Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.

File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form

Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53

and so on.

Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).

Any help greatly appreciated.

Cheers
Robbut surely there's a way to get DTS to read from a format file so I don't

Quote:

Originally Posted by

have to click 200 times (with all the ensuing errors I could make).

Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"JCC" <gofyself@.wrong.address.comwrote in message
news:fdE1h.31731$w07.17227@.newsfe6-win.ntli.net...

Quote:

Originally Posted by

Hi all
>
Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.
>
File is >1m rows and >200 columns and is defined by a proprietory (i.e.
not bcp produced) format specification of the form
>
Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53
>
and so on.
>
Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so
I don't have to click 200 times (with all the ensuing errors I could
make).
>
Any help greatly appreciated.
>
Cheers
Rob
>

|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:SYG1h.947$m54.525@.newssvr14.news.prodigy.com. ..

Quote:

Originally Posted by

Quote:

Originally Posted by

>but surely there's a way to get DTS to read from a format file so I don't
>have to click 200 times (with all the ensuing errors I could make).

>
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
>
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP

I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.

It looks like I can't avoid it though!

Cheers
Rob

Importing Text File: How to dynamically change the row delimiter

Hi,

I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

Any help would be appreciated

I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function

I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:

type inputfile.txt>newfile.txt

before importing. Type may convert LF to CRLF.

I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Importing Text File With DTS

Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.
Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.
|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:

> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>

Importing Text File With DTS

Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It´s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can´t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn´t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> It´s a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I can´t define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldn´t be so happy with that)
> thanks in advance.|||I´ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i´ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> > Hello,
> > i'm trying to import a text file using DTS... I check the 'delimited'
> > checkbox to do it because the guy that sent me this file didn't padronized
> > very well the file.
> > It´s a large file, it has about to 70 columns to be imported, and
> > thousands
> > of rows.
> > The problem is that the file has been sent to me like this:
> > column_name1 column_name2 column_name3]
> > data1 data2 data3
> >
> > I can´t define a blankspace using the 'other' checkbox, because some of
> > the
> > columns have more than one blankspace in between.
> > So, can I delimit the columns using some kind of wildcards or do I have to
> > manually padronize all the files?(I wouldn´t be so happy with that)
> > thanks in advance.
>
>sql

Importing Text File With DTS

Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.t
xt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate
it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:

> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*
.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>

Importing TEXT File in DTS

Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;Does it have to be DTS?

Why not DTS in to a single column table (varchar(8000)) and the parse out the data in to the final table?|||Because the text files can be larger than 200MB. :(
I need to take just the first Row of the text file to know some important informations.|||You sure you're talking about row size?

That's a long row....|||No. Im speaking about the File.

Look an example of the beggining of the file:
I want to get the first row of the file and put it into a column. Note, just the first row. You can see that the another lines are in a different layout and would make my table very big.

A221539 DPVAT - COD BAR 151BANCO NOSSA
G00000000000000 20040123200401298664000000093373362
G00000000000000 20040123200401298663000000093383362
G00000000000000 20040123200401298669000000051623362
G00000000000000 20040123200401298669000000093383362
G00000000000000 20040123200401298664000000093383362
G00000000000000 20040123200401298661000000093383362
G00000000000000 20040123200401298661000000055433362|||Have you looked at BULK INSERT in BOL?

You can specify first row and last row (ie 1 and 1)

Importing Problems with XML and XSD into SQL 2000

Im new to everything I'm tring to import an XML file using the bulkload method in an vb script within a DTS package. It initally gave me errors of "relationship expected on 'EventList'" but after some research I specified a relationship within my xsd f
ile for events with an annotation. However, it still gives me "the parent/child table of the relationship on 'EventList' does not match." I think the reason is the parent child relationship is not necessarly linked by any subelement key. Rather the one of
the two "main" elements in this group "Event" appears in as a "sub"element or referenced element for the other "main"element "EventList." What code can i use to establish the SQL:relationship in a way that correctly describes the elements' relationship?
Here's part of the xsd, a little long I apologize:
<xs:annotation><xs:appinfo><sql:relationship name="Events" parent="Event" parent-key="Event" child="EventList" child-key="Event"/></xs:appinfo></xs:annotation><xs:element name="Event" sql:relation="Event" sql:relationship="Events"><xs:complexType><xs:seq
uence><xs:element ref="Action"/><xs:element ref="Date"/><xs:element ref="Time"/><xs:element ref="SeqNo"/><xs:element ref="ConnID"/><xs:element ref="User"/><xs:element ref="Code"/><xs:choice><xs:element ref="UserInfo"/><xs:element ref="EntryInfo"/></xs:cho
ice></xs:sequence><xs:attribute name="ver" type="xs:string"/></xs:complexType></xs:element><xs:element name="EventList" sql:relation="EventList" sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element ref="Event" minOccurs="0" maxOccurs="unboun
ded"/></xs:sequence></xs:complexType></xs:element>
thanks kind souls
Can you post a sample of the XML you're trying to import as well as the
table defs you're importing into?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi Morillo" <mmorillo@.mbcsystems.org> wrote in message
news:4B3F602E-394B-46F7-B711-FDDC69558389@.microsoft.com...
> Im new to everything I'm tring to import an XML file using the bulkload
method in an vb script within a DTS package. It initally gave me errors of
"relationship expected on 'EventList'" but after some research I specified a
relationship within my xsd file for events with an annotation. However, it
still gives me "the parent/child table of the relationship on 'EventList'
does not match." I think the reason is the parent child relationship is not
necessarly linked by any subelement key. Rather the one of the two "main"
elements in this group "Event" appears in as a "sub"element or referenced
element for the other "main"element "EventList." What code can i use to
establish the SQL:relationship in a way that correctly describes the
elements' relationship? Here's part of the xsd, a little long I apologize:
> <xs:annotation><xs:appinfo><sql:relationship name="Events" parent="Event"
parent-key="Event" child="EventList"
child-key="Event"/></xs:appinfo></xs:annotation><xs:element name="Event"
sql:relation="Event"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Action"/><xs:element ref="Date"/><xs:element ref="Time"/><xs:element
ref="SeqNo"/><xs:element ref="ConnID"/><xs:element ref="User"/><xs:element
ref="Code"/><xs:choice><xs:element ref="UserInfo"/><xs:element
ref="EntryInfo"/></xs:choice></xs:sequence><xs:attribute name="ver"
type="xs:string"/></xs:complexType></xs:element><xs:element name="EventList"
sql:relation="EventList"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Event" minOccurs="0"
maxOccurs="unbounded"/></xs:sequence></xs:complexType></xs:element>
> thanks kind souls
>
|||Heres some of the xml file. By the way i converted the dtd to an xsd that i posted last time:
<?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo
><ConnID>1</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo
><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOUT</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGI
N</Action><Date>28-Feb-2004</Date><Time>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqN
o>5</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><En
tryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><Use
r>ADMIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event>
|||And the database tables?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi Morillo" <mmorillo@.mbcsystems.org> wrote in message
news:1C9D3C7F-C25E-46FC-A35E-2845454D5B5B@.microsoft.com...
> Heres some of the xml file. By the way i converted the dtd to an xsd that
i posted last time:
> <?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail
SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail
Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Actio
n><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo><ConnID>1</Co
nnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></U
serInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>2
2:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</C
ode><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOU
T</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><Conn
ID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnTyp
e></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><T
ime>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code
>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Acti
on>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqNo>5</SeqN
o><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</
ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</D
ate><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User
><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Fe
b-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><User>AD
MIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event>
|||CREATE TABLE AUDIT_XML_IMPORT
([Action]VARCHAR(100) NULL,
[AnnotateInfo]VARCHAR(100) NULL,
[AnnotColor]VARCHAR(100) NULL,
[AnnotHeight]VARCHAR(100) NULL,
[AnnotID]VARCHAR(100) NULL,
[AnnotLinked]VARCHAR(100) NULL,
[AnnotStampID]VARCHAR(100) NULL,
[AnnotStampName]VARCHAR(100) NULL,
[AnnotType]VARCHAR(100) NULL,
[AnnotWidth]VARCHAR(100) NULL,
[AnnotX]VARCHAR(100) NULL,
[AnnotY]VARCHAR(100) NULL,
[BackColor]VARCHAR(100) NULL,
[Code]VARCHAR(100) NULL,
[ConnID]VARCHAR(100) NULL,
[ConnType]VARCHAR(100) NULL,
[CopyInfo]VARCHAR(100) NULL,
[CreateInfo]VARCHAR(100) NULL,
[Database]VARCHAR(100) NULL,
[Date]VARCHAR(100) NULL,
[DestVolumeID]VARCHAR(100) NULL,
[DestVolumeName]VARCHAR(100) NULL,
[EntryID]VARCHAR(100) NULL,
[EntryInfo]VARCHAR(100) NULL,
[EntryName]VARCHAR(100) NULL,
[EntryType]VARCHAR(100) NULL,
[Event]VARCHAR(100) NULL,
[EventList]VARCHAR(100) NULL,
[ExportComment]VARCHAR(100) NULL,
[ExportInfo]VARCHAR(100) NULL,
[ExportMethod]VARCHAR(100) NULL,
[ExportPages]VARCHAR(100) NULL,
[ExportReason]VARCHAR(100) NULL,
[FieldAfter]VARCHAR(100) NULL,
[FieldBefore]VARCHAR(100) NULL,
[FieldChange]VARCHAR(100) NULL,
[FieldList]VARCHAR(100) NULL,
[FieldName]VARCHAR(100) NULL,
[FieldsInfo]VARCHAR(100) NULL,
[FieldType]VARCHAR(100) NULL,
[FuzzyParameter]VARCHAR(100) NULL,
[HiliteInfo]VARCHAR(100) NULL,
[HiliteTextInfo]VARCHAR(100) NULL,
[LaserFicheAuditTrail]VARCHAR(100) NULL,
[MigrateInfo]VARCHAR(100) NULL,
[ModifyType]VARCHAR(100) NULL,
[MoveInfo]VARCHAR(100) NULL,
[NewName]VARCHAR(100) NULL,
[NewParentID]VARCHAR(100) NULL,
[NewRefID]VARCHAR(100) NULL,
[NoteInfo]VARCHAR(100) NULL,
[NoteText]VARCHAR(100) NULL,
[NumPages]VARCHAR(100) NULL,
[Page]VARCHAR(100) NULL,
[ParentID]VARCHAR(100) NULL,
[RedactColor]VARCHAR(100) NULL,
[RedactInfo]VARCHAR(100) NULL,
[RedactTextInfo]VARCHAR(100) NULL,
[RenameInfo]VARCHAR(100) NULL,
[SearchInfo]VARCHAR(100) NULL,
[SearchString]VARCHAR(100) NULL,
[SeqNo]VARCHAR(100) NULL,
[SourceID]VARCHAR(100) NULL,
[SourceVolumeID]VARCHAR(100) NULL,
[SourceVolumeName]VARCHAR(100) NULL,
[StampInfo]VARCHAR(100) NULL,
[StampMasterInfo]VARCHAR(100) NULL,
[StampPersonalInfo]VARCHAR(100) NULL,
[StartPage]VARCHAR(100) NULL,
[TemplateID]VARCHAR(100) NULL,
[TemplateName]VARCHAR(100) NULL,
[TextColor]VARCHAR(100) NULL,
[TextEnd]VARCHAR(100) NULL,
[TextStart]VARCHAR(100) NULL,
[Time]VARCHAR(100) NULL,
[User]VARCHAR(100) NULL,
[UserInfo]VARCHAR(100) NULL,
[Version]VARCHAR(100) NULL,
[VolumeID]VARCHAR(100) NULL,
[VolumeName]VARCHAR(100) NULL,)
GO
|||The problem is that you've only got a single table - the relationship you've
declared in the schema is trying to join a table called Event to a table
called EventList - neither of which exist in your database.
I'd suggest that the easiest approach would be to "flatten" the data using
an XSLT before performing the bulk load. See
http://sqlxml.org/faqs.aspx?faq=49.
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi" <mmorillo@.mbcsystems.org> wrote in message
news:ECC157F3-BD9E-4415-A248-8D85E6EE7A7B@.microsoft.com...
> CREATE TABLE AUDIT_XML_IMPORT
> ([Action]VARCHAR(100) NULL,
> [AnnotateInfo]VARCHAR(100) NULL,
> [AnnotColor]VARCHAR(100) NULL,
> [AnnotHeight]VARCHAR(100) NULL,
> [AnnotID]VARCHAR(100) NULL,
> [AnnotLinked]VARCHAR(100) NULL,
> [AnnotStampID]VARCHAR(100) NULL,
> [AnnotStampName]VARCHAR(100) NULL,
> [AnnotType]VARCHAR(100) NULL,
> [AnnotWidth]VARCHAR(100) NULL,
> [AnnotX]VARCHAR(100) NULL,
> [AnnotY]VARCHAR(100) NULL,
> [BackColor]VARCHAR(100) NULL,
> [Code]VARCHAR(100) NULL,
> [ConnID]VARCHAR(100) NULL,
> [ConnType]VARCHAR(100) NULL,
> [CopyInfo]VARCHAR(100) NULL,
> [CreateInfo]VARCHAR(100) NULL,
> [Database]VARCHAR(100) NULL,
> [Date]VARCHAR(100) NULL,
> [DestVolumeID]VARCHAR(100) NULL,
> [DestVolumeName]VARCHAR(100) NULL,
> [EntryID]VARCHAR(100) NULL,
> [EntryInfo]VARCHAR(100) NULL,
> [EntryName]VARCHAR(100) NULL,
> [EntryType]VARCHAR(100) NULL,
> [Event]VARCHAR(100) NULL,
> [EventList]VARCHAR(100) NULL,
> [ExportComment]VARCHAR(100) NULL,
> [ExportInfo]VARCHAR(100) NULL,
> [ExportMethod]VARCHAR(100) NULL,
> [ExportPages]VARCHAR(100) NULL,
> [ExportReason]VARCHAR(100) NULL,
> [FieldAfter]VARCHAR(100) NULL,
> [FieldBefore]VARCHAR(100) NULL,
> [FieldChange]VARCHAR(100) NULL,
> [FieldList]VARCHAR(100) NULL,
> [FieldName]VARCHAR(100) NULL,
> [FieldsInfo]VARCHAR(100) NULL,
> [FieldType]VARCHAR(100) NULL,
> [FuzzyParameter]VARCHAR(100) NULL,
> [HiliteInfo]VARCHAR(100) NULL,
> [HiliteTextInfo]VARCHAR(100) NULL,
> [LaserFicheAuditTrail]VARCHAR(100) NULL,
> [MigrateInfo]VARCHAR(100) NULL,
> [ModifyType]VARCHAR(100) NULL,
> [MoveInfo]VARCHAR(100) NULL,
> [NewName]VARCHAR(100) NULL,
> [NewParentID]VARCHAR(100) NULL,
> [NewRefID]VARCHAR(100) NULL,
> [NoteInfo]VARCHAR(100) NULL,
> [NoteText]VARCHAR(100) NULL,
> [NumPages]VARCHAR(100) NULL,
> [Page]VARCHAR(100) NULL,
> [ParentID]VARCHAR(100) NULL,
> [RedactColor]VARCHAR(100) NULL,
> [RedactInfo]VARCHAR(100) NULL,
> [RedactTextInfo]VARCHAR(100) NULL,
> [RenameInfo]VARCHAR(100) NULL,
> [SearchInfo]VARCHAR(100) NULL,
> [SearchString]VARCHAR(100) NULL,
> [SeqNo]VARCHAR(100) NULL,
> [SourceID]VARCHAR(100) NULL,
> [SourceVolumeID]VARCHAR(100) NULL,
> [SourceVolumeName]VARCHAR(100) NULL,
> [StampInfo]VARCHAR(100) NULL,
> [StampMasterInfo]VARCHAR(100) NULL,
> [StampPersonalInfo]VARCHAR(100) NULL,
> [StartPage]VARCHAR(100) NULL,
> [TemplateID]VARCHAR(100) NULL,
> [TemplateName]VARCHAR(100) NULL,
> [TextColor]VARCHAR(100) NULL,
> [TextEnd]VARCHAR(100) NULL,
> [TextStart]VARCHAR(100) NULL,
> [Time]VARCHAR(100) NULL,
> [User]VARCHAR(100) NULL,
> [UserInfo]VARCHAR(100) NULL,
> [Version]VARCHAR(100) NULL,
> [VolumeID]VARCHAR(100) NULL,
> [VolumeName]VARCHAR(100) NULL,)
> GO
>

Importing Pervasive SQL into SQL2005 via SSIS

In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.

In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net Providers\Odbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DB\SQL Native Client) to the SQL database. Both databases reside on the same machine.

I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.

Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.

This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie

connect to the data conversion task before inserting into the destination or provide false for metadata in the properties of both the task.|||

The issue is still with the Data Conversion. I've been reading many of the forums and it seems that, like myself, there are a lot of frustrated developers out there trying to make simple sql2000 dts packages work in sql2005 SSIS. Almost all of them point to the data conversion issue. What a real pain in the arse. I surely hope that Microsoft does something about this, SOON...

Something simple that would take less than an hour could turn into weeks and weeks of fruitless effort.

The client has a pervasive sql database that i did many tests on using sql2000 dts. There's approximately 150 tables. My testing went very well. I recommended to the client that they purchase a new server Win2003 R2 along with SQL2005, advising them that my development time in SQL2005, converting the tables with DTS would be a much better process with the upgrade (is it an really an upgraded when you have all this bickering on unicode (data conversion) issues).

I have spent countless hours trying to get this working in sql2005 ssis, with no success.

I wish in retrospect that i'd just had them get Win2000 & SQL2000. I would have been done with the project by now and i would have had a happy client. I feel like a damn fool, recommending a package that i thought would be a significant DEVELPOR improvement, and have yet to show any results. This does not bode well for others like me at all.

|||

I have been testing and trying for days, almost a week trying to create a simple, single table SSIS package that i created in DTS in minutes. I'm very frustrated with how the whole process gets hung up in the data conversion.

I have a DataReader Source (an ODBC connection (System DSN) to a Pervasive SQL Database). It pulls the table in just fine. I have a DataFlow Task, using a Data Conversion. I have a SQL Server Destination (The table already exists in the SQL 2005 database).|||

Please do this, I had did same thing in my package.

double click on data task flow,

then click on source task go to it's properties windows

there is a properties called ValidateExternalMetadata :- set is False

repeat for destination task properties.

|||

Your recommendation seems to work for Numerical Conversions.

BUT...

Not for string transformations. The strings come in as unicode DT-WSTR.

In the Data Conversion Transformation Editor, I have to manually create a transformation for each and every instance, from DT_WSTR to DT_STR. Once i've done this, it passes muster with running in Debug. This would still require lots of tedious manual, repetitive tasks.

Is there a way to force the DT_WSTR to DT_STR for all of these so i don't have to manually create a Transformation for each?

Or...

Do you have another suggestion on the STR issue that i might try?

Thanks too very much for your assistance!!!

Importing Pervasive SQL into SQL2005 via SSIS

In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.

In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net Providers\Odbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DB\SQL Native Client) to the SQL database. Both databases reside on the same machine.

I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.

Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.

This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie

connect to the data conversion task before inserting into the destination or provide false for metadata in the properties of both the task.|||

The issue is still with the Data Conversion. I've been reading many of the forums and it seems that, like myself, there are a lot of frustrated developers out there trying to make simple sql2000 dts packages work in sql2005 SSIS. Almost all of them point to the data conversion issue. What a real pain in the arse. I surely hope that Microsoft does something about this, SOON...

Something simple that would take less than an hour could turn into weeks and weeks of fruitless effort.

The client has a pervasive sql database that i did many tests on using sql2000 dts. There's approximately 150 tables. My testing went very well. I recommended to the client that they purchase a new server Win2003 R2 along with SQL2005, advising them that my development time in SQL2005, converting the tables with DTS would be a much better process with the upgrade (is it an really an upgraded when you have all this bickering on unicode (data conversion) issues).

I have spent countless hours trying to get this working in sql2005 ssis, with no success.

I wish in retrospect that i'd just had them get Win2000 & SQL2000. I would have been done with the project by now and i would have had a happy client. I feel like a damn fool, recommending a package that i thought would be a significant DEVELPOR improvement, and have yet to show any results. This does not bode well for others like me at all.

|||

I have been testing and trying for days, almost a week trying to create a simple, single table SSIS package that i created in DTS in minutes. I'm very frustrated with how the whole process gets hung up in the data conversion.

I have a DataReader Source (an ODBC connection (System DSN) to a Pervasive SQL Database). It pulls the table in just fine.

I have a DataFlow Task, using a Data Conversion.

I have a SQL Server Destination (The table already exists in the SQL 2005 database).|||

Please do this, I had did same thing in my package.

double click on data task flow,

then click on source task go to it's properties windows

there is a properties called ValidateExternalMetadata :- set is False

repeat for destination task properties.

|||

Your recommendation seems to work for Numerical Conversions.

BUT...

Not for string transformations. The strings come in as unicode DT-WSTR.

In the Data Conversion Transformation Editor, I have to manually create a transformation for each and every instance, from DT_WSTR to DT_STR. Once i've done this, it passes muster with running in Debug. This would still require lots of tedious manual, repetitive tasks.

Is there a way to force the DT_WSTR to DT_STR for all of these so i don't have to manually create a Transformation for each?

Or...

Do you have another suggestion on the STR issue that i might try?

Thanks too very much for your assistance!!!

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
Thad
DId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad
|||Hey Jens,
It worked! It was great help!
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in m
y
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens Sü?meyer" wrote:

> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
>
>sql

Importing only data from Access to SQL Tables

Hi all,
I am having problems importing data from Access 97 .mdb files into
my SQL Tables.
Using DTS, I invoked the Import/Export wizard.
When prompt for the source, I specified Microsoft Access and it's
path/filename.
I did not enter username and password for the source.
But when DTS executes the query to import data, it prompts me this error:
"Records cannot be read, no read permission on tablename"
When i entered the userid and password for the workgroup file(mdw),
it says that "cannot start application. Workgroup information file is
missing or open exclusively"
How can I work around this? Or Is there other way to import data into SQL
table? I tried to perform export from Access 97. It creates a new table in my
SQL Server instead, without keys, indexes, relationship.
Any thoughts/help greatky appreciated.
--
With Thanks,
ThadDId you specified the workgroup file ? You can do that by opening the
extended properties and putting in the location and the name of the
Workgroup file in the system database section. That should work.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> Hi all,
> I am having problems importing data from Access 97 .mdb files into
> my SQL Tables.
> Using DTS, I invoked the Import/Export wizard.
> When prompt for the source, I specified Microsoft Access and it's
> path/filename.
> I did not enter username and password for the source.
> But when DTS executes the query to import data, it prompts me this error:
> "Records cannot be read, no read permission on tablename"
> When i entered the userid and password for the workgroup file(mdw),
> it says that "cannot start application. Workgroup information file is
> missing or open exclusively"
> How can I work around this? Or Is there other way to import data into SQL
> table? I tried to perform export from Access 97. It creates a new table in
> my
> SQL Server instead, without keys, indexes, relationship.
> Any thoughts/help greatky appreciated.
> --
> With Thanks,
> Thad|||Hey Jens,
It worked! It was great help!
--
With Thanks,
Thad
"Jens Sü�meyer" wrote:
> DId you specified the workgroup file ? You can do that by opening the
> extended properties and putting in the location and the name of the
> Workgroup file in the system database section. That should work.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Thaddeus" <Thaddeus@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FCEF3896-D51F-4074-B704-4EAA1EC99924@.microsoft.com...
> > Hi all,
> > I am having problems importing data from Access 97 .mdb files into
> > my SQL Tables.
> > Using DTS, I invoked the Import/Export wizard.
> >
> > When prompt for the source, I specified Microsoft Access and it's
> > path/filename.
> > I did not enter username and password for the source.
> >
> > But when DTS executes the query to import data, it prompts me this error:
> > "Records cannot be read, no read permission on tablename"
> >
> > When i entered the userid and password for the workgroup file(mdw),
> > it says that "cannot start application. Workgroup information file is
> > missing or open exclusively"
> >
> > How can I work around this? Or Is there other way to import data into SQL
> > table? I tried to perform export from Access 97. It creates a new table in
> > my
> > SQL Server instead, without keys, indexes, relationship.
> >
> > Any thoughts/help greatky appreciated.
> >
> > --
> > With Thanks,
> > Thad
>
>

Importing from SQL7 using DTS wizard

We are trying to import data from a sql 7 machine into sql 2000 using
import wizard in DTS. One of the tables has in excess of 80 million
rows.

The first time we did this it worked fine no problems. However we had
to recreate the database, and it has not worked since.

The error message is reported as ' the log file for 'dbname' is full'.
This happens regardless of the fact that there is 100GB free on disk,
and that the database data and log files are both set to autogrow. The
recovery model is set to simple.

When imported the data.mdf file should be around 20GB.

Would anyone know what is causing this or how to get around this
without going down the SQL7 install, restore and upgrade to sql2000
route?

Any help would be appreciated greatly.Hi

You may want to try attaching the mdf and ldf files from the SQL 7 box onto
the SQL 2000 box and then making any modifications required or restore a
backup.

You don't say how big the log file is. If it is set to grow by a percentage
that value may be larger than the available disk space.

Check out

http://support.microsoft.com/defaul...b;en-us;Q314546
http://support.microsoft.com/defaul...ben-us%3b224071
http://msdn.microsoft.com/library/d...ar_da2_1uzr.asp

John

"John Gill" <pizzaboyo@.yahoo.co.uk> wrote in message
news:a8be1015.0311070405.17c37eb@.posting.google.co m...
> We are trying to import data from a sql 7 machine into sql 2000 using
> import wizard in DTS. One of the tables has in excess of 80 million
> rows.
> The first time we did this it worked fine no problems. However we had
> to recreate the database, and it has not worked since.
> The error message is reported as ' the log file for 'dbname' is full'.
> This happens regardless of the fact that there is 100GB free on disk,
> and that the database data and log files are both set to autogrow. The
> recovery model is set to simple.
> When imported the data.mdf file should be around 20GB.
> Would anyone know what is causing this or how to get around this
> without going down the SQL7 install, restore and upgrade to sql2000
> route?
> Any help would be appreciated greatly.

Importing from Excel

I am having a problem with DTS importing NULL values from an Excel spreadsheet when there are numbers and text in the same column. Other than that, the import works fine. I am using VBScript in an ASP web page to create and execute a DTS package to import an Excel file.

I have read Allan Mitchell's article about using IMEX=1 (http://www.sqldts.com/default.aspx?254). I added IMEX=1 and modified the registry setting, but when I do that NO data at all is imported.

Any ideas? My code is very long, so I'll only post it if you'd really like to see it.

Much thanks in advance!

BillI would export the excel file into a delimited file - the ms driver for excel causes too many headaches. Also, did you modify the TypeGuessRows setting in your registry ?|||Originally posted by rnealejr
I would export the excel file into a delimited file - the ms driver for excel causes too many headaches. Also, did you modify the TypeGuessRows setting in your registry ?

I did modify the TypeGuessRows setting to 0. Is there a way to programmatically export the Excel file to a CSV file?

Thanks,

Bill|||It has been a while since I used the TypeGuessRows - but I believe setting it to 0 will not work - you have to explicitly supply a value (something big enough to sample your data).

Programmatically - Do you mean from within sql server or any executable ?|||Originally posted by rnealejr
It has been a while since I used the TypeGuessRows - but I believe setting it to 0 will not work - you have to explicitly supply a value (something big enough to sample your data).

Programmatically - Do you mean from within sql server or any executable ?

I read somewhere to set it to 0 and use IMEX=1. I just tried setting it to 16 and 8, but neither of those worked either.

Actually, when I said programatically I meant VBScript, but I guess that's for the VBScript forum!

Thanks,

Bill|||You can create an excel object - open the workbook - use the saveas method for the workbook.|||Originally posted by rnealejr
You can create an excel object - open the workbook - use the saveas method for the workbook.

Where can I find information on using the excel object's saveas function? I'm sure there are a number of options, and I'd like to take a look at them.

Thanks!

Bill|||Here is the snapshot from the help (Also, look for a vbaxl9.chm file on your computer):

Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.

Syntax 1

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Syntax 2

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)

expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2).

Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you dont, Microsoft Excel saves the file in the current folder.

FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.

WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isnt supplied when the file is opened, the file is opened as read-only.

ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.

CreateBackup Optional Variant. True to create a backup file.

AccessMode Optional Variant. The workbook access mode. Can be one of the following XlSaveAsAccessMode constants: xlShared (shared list), xlExclusive (exclusive mode), or xlNoChange (dont change the access mode). If this argument is omitted, the access mode isnt changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.

ConflictResolution Optional Variant. Specifies the way change conflicts are resolved if the workbook is a shared list. Can be one of the following XlSaveConflictResolution constants: xlUserResolution (display the conflict-resolution dialog box), xlLocalSessionChanges (automatically accept the local users changes), or xlOtherSessionChanges (accept other changes instead of the local users changes). If this argument is omitted, the conflict-resolution dialog box is displayed.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel.

TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel.

The following are the available file formats:

xlAddIn
xlCSV

xlCSVMac

xlCSVMSDOS

xlCSVWindows

xlCurrentPlatformText

xlDBF2

xlDBF3

xlDBF4

xlDIF

xlExcel2

xlExcel2FarEast

xlExcel3

xlExcel4

xlExcel4Workbook

xlExcel5

xlExcel7

xlExcel9795

xlHTML

xlIntlAddIn

xlIntlMacro
xlSYLK
xlTemplate

xlTextMac

xlTextMSDOS

xlTextPrinter

xlTextWindows

xlUnicodeText

xlWJ2WD1

xlWK1

xlWK1ALL

xlWK1FMT

xlWK3

xlWK4

xlWK3FM3

xlWKS

xlWorkbookNormal

xlWorks2FarEast

xlWQ1

xlWJ3

xlWJ3FJ3|||Awesome, thanks!

So if I do this:

<code>
set xlApp = CreateObject("excel.application")
xlApp.Workbooks.Open <filename>
xlApp.ActiveWorkbook.SaveAs <newFileName>, xlCSVWindows
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
</code>

It would save it as a CSV file?

Thanks!

Bill|||Yes, that looks good. Let me know if there are any problems.|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Thanks, I'm going to try to give it a test today!|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Okay, I tried that code and I'm getting the following error message:

Microsoft Excel error '800a03ec'
SaveAs method of Workbook class failed

Any ideas?

Thanks!

Bill|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Okay, I may have found the issue, but I'm not sure. I saved the current workbook file format value to a variable and printed it to the screen. The value was -4143. Is the fileformat value supposed to be a number or the values from the help file (i.e., xlCSV)?

Thanks for all your help!

Bill|||I do not have to vb at the moment to confirm but the xlCSV value should be and the xlWindowsCSV is 23.|||Also, sorry for the delay - your post got buried.|||Originally posted by rnealejr
I do not have to vb at the moment to confirm but the xlCSV value should be and the xlWindowsCSV is 23. \

No problem on the delay, I really appreciate the help - is there a list of these values anywhere? I've been looking, but I must not be using the right terminology.

Thanks!

Bill|||This link might be helpful:

link (http://techsupt.windowware.com/TS/T000001033005F9.html)

Importing from access

Hallo.

Is there a way to create a general dts to import tables from access mdb file?
so that i will not have to change the dts for every table i am adding to the access file?
something like foreachtable? or a way to read the tables list from the access file in the sql connection?

i am importing to sql server 2000.

thanksI'd just use a stored procedure. Have it take an argument for the linked server name for the access file (or a UNC pathname and have your procedure create the linked server too using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp)), then use sp_table_ex (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_9q60.asp) to put the catalog into a temp table, use a cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) to traverse the list of tables, and do a SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) from each of the foreign tables into a SQL Server table.

Then again, I'm lazy!

-PatP