Importing Excel File in SSIS

Okay... I am now about to pull my hair out: something that worked VERY EASILY in Server 2000 doesn't seem to work at all in 2005. I am trying to pump an Excel table into a 2005 database. I go into the Visual Studio Integration Services Project (this is so much easier... cynicism) and set up a project. I have my data source (Excel), I have my destination (SQL Natve Server, database). I set it up the same way that it worked (perfectly) in DTS and I run it... it grinds away and reports back that all is well... no errors. I go looking for the table... not there. I try with an SA login VS windows authentication... not there. I try with a different table... no there. I try with a different database... not there.

I am certain you can imagine the frustration... that is, if you are a user, not a programmer at Microsoft.

Okay, okay... I won't launch in to abuse here... but hey, how do I make this very complicated process now work?

Thanks...

Use the OLE DB Destination.

The SQL Server destination is for databases local to the package.|||

Thanks Phil... I did figure it out with more fidling (as is usually the case, when you breakdown and ask for help, you discover the answer and just how much of a dolt you are): you have to SAVE the package before it runs correctly. [so, what was previously a ONE STEP operation, is now 8 steps... joy, joy, joy]

Thanks, and adios...

Importing data from oracle to sql loosing data after the decimal point

I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.

In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.

thanks

What is the data type of the SSIS column that you are storing the Oracle results in? Also double check that you have a "scale" defined for that data type.|||

I have tried money, decimal (38,2) , float and numeric (18,2)

With no luck. What is really weird is that when I use the Destination of flat file the same thing happens I loose all digits after the decimal.

|||Are you just going source to destination? No other transformations?|||thats correct.|||Do you receive any truncation warnings when you run the package? (Or ANY warnings?)|||

I don't get any warnings if I use numeric (38,2)

I let the package create the table. The package wants to use numeric (38) but I changed to numeric (38,2) on the create table statement in the destination window.

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

Importing data from another table

Hi All,

I'm coming from using MySQL, and in their dialect you could pull data from one table to another using the following:

INSERTINTO Table1 (fname, lname)
VALUES(
SELECTfname, lname
FROMTable2
)

Let's assume Table1 is a simple table with the fields ID (PK/Identity), fname, and lname. This query would grab all the first and last names out of Table2 (fname and lname fields) and insert them into Table1, generating the ID for each new row.

How would I do this in T-SQL?

INSERTINTO Table1
SELECT fname, lname
FROMTable2

Limno