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 Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing Data Into Sql Server Database

I am experiencing intermittent connection problems with the Northwind database and sql server express. The most frequent error message indicates that I am already logged in and will not allow another user.

The odd thing is that even when I am locked out I can access a Northwind database from another connection on Server Explorer.

I ran code to demonstrate simple binding for two text boxes, no problem. I added a project to show complex binding using the datagrid view tool, but could not get past selecting the database.

I tried the same simple binding project on another computer running a beta version of VS 2005 Pro and could not connect to Northwind on that computer also. (Two different Northwind databases, different computers)

A couple days ago I restored the database in Solutions Manager and that helped for a while, but I should not have to resort to that, I know.

How do I remove the user lock? I tried Solutions Manager Express with the first computer but could not access the database in the management module.

The exact text of the error would help. It sounds like you might be trying to access the data through both a User Instance and through the parent instance. This doesn't work since only one copy of SQL can access a database at a time.

Mike

|||

Mike-

I learned that whenever I had a problem with connecting to a database, it was because I was using the dataset from the prior project in the solution, in a new project.

Thanks for replying.

Ruel

P.S.

I don't know if I should start a new thread, but how do I pull data from an Excel spreadsheet into a sql server database table?

|||

Hi,

i would suggest to refer

http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

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

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

HTH

Hemantgiri S. Goswami

|||

Thanks Hemantgiri -

The links were helpful for understanding the process of pulling data files into sql server. However, I am still running into a roadblock. For instance, the following:

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

bulk insert

AAS Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

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

Where:

AAS = Database Name

Accounts = Table Name

Gives the following error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Accounts'.

|||

Hi,

run it like,

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

or

bulk insert

AAS.ObjectOwner.Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

Hemantgiri S. Goswami

|||

Tried the following as you suggested

bulk insert AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

With the resulting error message:

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (AccountNo).

Column 1 of table Accounts is nchar(15) - I raised it from 10 to 15 to see if that would take care of the truncation issue.

The format for column 1 of the xls file is general.

I have had a similar experience with an xml file.

I also tried the bcp utility to format a file for input but I get a message telling me that either the server does not exist or I do not have access.

Thanks for your assistance

Ruel

|||

Could not get to the server with the bcp utility so I tried bulk insert. I found that I had to use "firstrow=2" to get past error messages.

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

with

(

firstrow=2

)

However, the above returns the following message in SSMSE:

(0 row(s) affected)

|||

Hi,

Could ensure the length of the all field!!!!

Hemantgiri S. Goswami

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

Please let me know.

Thanks

MSDAORA does not support 10g, have a look at INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
Try using OraOLEDB.Oracle instead.|||

Hi Anton,

Thanks for speedy reply.

Anton in my procedure if I remove transaction(Begin Tran, Commit, Rollback) then this procedure is working fine, but removal of it not possible, so it is not related with provider, I hope.

If I put tranaction it shows above maintain error.

Thanks

|||Is there any reason you cannot try Oracle's provider?|||

Hi Anton,

I am using OLEDB.ORACLE but it is not allowing me to make new Linked server.

It shows me this error:

TITLE: Microsoft SQL Server Management Studio

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "OraOLEDB.Oracle" for linked server "AA" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "AA". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

&Yes
&No

|||Have you enabled AllowInProcess option in the provider options?|||

Hi Anton,

Yes, I did but it returns another error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||But MSDAORA is not an Oracle's provider. Did you try it for the OraOLEDB?|||

Hi Anton,

Yes, I did

But it is not allowing me to make a new linked Server.

Presently I am facing this problem:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||

Hi Anton,

Now I solved ORA-12154 Error but when I enabled "Allow InProcess" this gives ORA-12154 Error again.

After disable this option, I can connect Oracle server successfully but when I used to fire my procedure that is used to import records from oracle 10g to sqlserver 2005 using linked server.

It Shows me this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" reported an error. The provider did not give any information about the error.
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" was unable to begin a distributed transaction.

Thanks for your nice support.

|||http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701|||

Hi Anton,

I got 2 kind of error:

checked "Allow inprocess" in the provider options, then it Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

If NOT CHECKED then Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

I used later one because by this i can access records from oracle but when i used this linked server in my Procedure using transaction then this error is reflected.

I find out a link of support.microsoft.com/kb/906954 but it can not make me out from this trouble.

Please advise me the solution.

Thanks for your support.

Importing data from MS Access using DTS 2005

Guys,

I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.

Thanks

You will need to set your connection manager to use OLE DB and the Microsoft Jet 4.0 OLE Provider. This will connect you to any version of Access above version 4 from memory.

Once you have added the dataflow task, under the dataflow tab add you OLD db source, and then your destination. If you do need to do any transforms you would need to add them from the tool box under the data flow tab.

|||

Glenn,

Thanks; still having some issues; am trying to connect from access and dump to an excel spreadsheet.

I used the OLE DB to connect; and in my data flow task, I created a connection manager for Excel (specifing file path where the spreadsheet was located). I then identified an Excel Spreadsheet as my data flow destination point. When I executed, I got the following errors:

Error: 0xC0202009 at Pull From Access, Excel Destination [357]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at Pull From Access, Excel Destination [357]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Pull From Access, DTS.Pipeline: component "Excel Destination" (357) failed the pre-execute phase and returned error code 0xC0202025.

Anything I am doing wrong?

|||

Can anyone throw some light on this issue. Im facing almost a similar problem. I have an OLEDB source which connects to SQL server pulls some records out of a table and i want them to be exported to a Excel File which i have already created. So i added a New connection using the Connection Manager for Excel Files and connected to the already existing destination in which i have defined some column names. When i maped the columns it initially gave me some conversion errors for teh varchar fields, then finally i converted all the varchar fields to "Unicode text stream [DT_NTEXT]", now there were no conversion errors. But when i executed the package i got the following errors:

[Excel Destination [185]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [185]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (185) failed the pre-execute phase and returned error code 0xC0202025.

Any help is appreciated. Thanks a lot in advance.

|||

I got most of the things done (I created an Excel File and each time im creating sheets i.e., Creating and dropping tables which deltes the data and gives a fresh sheet to insert the data) but in my package im creating an Excel sheet/TAble using the Execute SQL statement

"CREATE TABLE `CUSTOMER_ORDER_ITEM` (`TransferDate` DateTime,
`ErrCode` LongText,
`ErrDesc` LongText,
`ErrData` LongText,
`ErrorStatus` Short
)
GO
"

before that im dropping the sheet/table using the below Execute SQL statement

"DROP TABLE `CUSTOMER_ORDER_ITEM` "

So it is throwing an error for the first time when the package is running. I need to know whether there is a way to check that the Sheet/Table exists before deleting the Sheet/Table.

Thanks in advance. Any other work around is also appreciated

|||

You can use GetOleDbSchemaTable to check whether the sheet exists.

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

Importing data from MS Access using DTS 2005

Guys,

I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.

Thanks

You will need to set your connection manager to use OLE DB and the Microsoft Jet 4.0 OLE Provider. This will connect you to any version of Access above version 4 from memory.

Once you have added the dataflow task, under the dataflow tab add you OLD db source, and then your destination. If you do need to do any transforms you would need to add them from the tool box under the data flow tab.

|||

Glenn,

Thanks; still having some issues; am trying to connect from access and dump to an excel spreadsheet.

I used the OLE DB to connect; and in my data flow task, I created a connection manager for Excel (specifing file path where the spreadsheet was located). I then identified an Excel Spreadsheet as my data flow destination point. When I executed, I got the following errors:

Error: 0xC0202009 at Pull From Access, Excel Destination [357]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at Pull From Access, Excel Destination [357]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Pull From Access, DTS.Pipeline: component "Excel Destination" (357) failed the pre-execute phase and returned error code 0xC0202025.

Anything I am doing wrong?

|||

Can anyone throw some light on this issue. Im facing almost a similar problem. I have an OLEDB source which connects to SQL server pulls some records out of a table and i want them to be exported to a Excel File which i have already created. So i added a New connection using the Connection Manager for Excel Files and connected to the already existing destination in which i have defined some column names. When i maped the columns it initially gave me some conversion errors for teh varchar fields, then finally i converted all the varchar fields to "Unicode text stream [DT_NTEXT]", now there were no conversion errors. But when i executed the package i got the following errors:

[Excel Destination [185]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [185]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (185) failed the pre-execute phase and returned error code 0xC0202025.

Any help is appreciated. Thanks a lot in advance.

|||

I got most of the things done (I created an Excel File and each time im creating sheets i.e., Creating and dropping tables which deltes the data and gives a fresh sheet to insert the data) but in my package im creating an Excel sheet/TAble using the Execute SQL statement

"CREATE TABLE `CUSTOMER_ORDER_ITEM` (`TransferDate` DateTime,
`ErrCode` LongText,
`ErrDesc` LongText,
`ErrData` LongText,
`ErrorStatus` Short
)
GO
"

before that im dropping the sheet/table using the below Execute SQL statement

"DROP TABLE `CUSTOMER_ORDER_ITEM` "

So it is throwing an error for the first time when the package is running. I need to know whether there is a way to check that the Sheet/Table exists before deleting the Sheet/Table.

Thanks in advance. Any other work around is also appreciated

|||

You can use GetOleDbSchemaTable to check whether the sheet exists.

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