February 8, 2011 at 9:00 am
OK, I am not really a SQL Admin of any sort...
Now, I have a new app that somebody has dropped on my lap to which runs on top of SQL 2008
I have the app all installed and everything and now they want me to get all this data out of Excel Spreadsheets into this new app.
Rundown - SQL Server 2008 (Win 2008 R2) with all SQL options installed
I've gathered all the data they want from this one spreadsheet and made it into a somewhat usable spreadsheet. Of course I can save the spreadsheet into whatever I need (2007/10, 2003, CSV, or Tabbed TXT).
The spreadsheet, I've changed column headings to match the column names within the SQL table
Code - Our internal numbering system, Name, Description, Price, UnitOfMeasure, and Type
The spreadsheet contains over 22,000 records so I in no way want to do this manually.
I've tried the SQL Import/Export wizard using Excel '03 format, CSV, and Tabbed TXT and I just can't get this thing to import. An example of my records would be such as below:
CodeNameDescriptionPriceUnitOfMeasureIDRevenueTypeID
596011012 SIZE ADAPTOR LOCK DOWN KITLOCK DOWN KIT$6.0096f81381-74e9-4cdd-abab-10c2525bd5081ef5d58a-6da6-458e-b328-4490cd13f5e6
The last two columns are actually the UniqueIDs for the words EA and Product - The sql table shows those values instead of the words themselves.
Last result was using Tabbed TXT and errored with:
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (47)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (47)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Items" (34) failed with error code 0xC0209029 while processing input "Destination Input" (47). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
When I tried using BCP I came up with
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
Can anybody point me in a direction that may help me out a bit Something I thought would be somewhat painless is ending up not to be so... :angry:
February 8, 2011 at 9:09 am
It could be a number of things, but most likely a data-type error.
What i would do is save the import/export package and open it up in BIDS, this will allow you to check the datasource and destination and then run the package in debug mode to see where the error is occuring.
You can then change the error options for the compenets to pass the error rows to another desitination so then you can identify the problematic data.
I can't really help with the BCP as I tend to do everything now in SSIS.
February 8, 2011 at 9:15 am
SSIS is certainly a more advanced app if you are not familiar with SQL Server to begin with, but I would agree with Steve's idea.
If you are not comfortable with that, I would do this:
1. Try some sort of delimiter (such as a comma) to separate your row values and columns with.
2. Coat tailing on Steve's observation - verify that the column datatypes will accpet the type of data you are trying to put in there (i.e. varchars are variable length to the specified setting where a straight char is only going to take that exact amount of characters - be it alphanumeric or straight numeric).
Hope that helps.
February 8, 2011 at 9:19 am
Also - make sure if you have columns that do not allow for nulls that you are in fact sending something to that column. The ones that do allow for nulls, you obviously do not need to send anytning.
And just make sure if you have a PKey that is an identity column, that you are not trying to send something to it.
February 8, 2011 at 9:40 am
steveb. (2/8/2011)
What i would do is save the import/export package and open it up in BIDS, this will allow you to check the datasource and destination and then run the package in debug mode to see where the error is occuring.
OK, by BIDS I assume you are meaning the Business Intelligence Dev Studio
Now, by save the import/export package and open it up, to check datasource and destination... you aren't saying I should open up the CSV I'm trying to import into SQL are you?
Sorry for being such the newb
February 8, 2011 at 10:19 am
Another thing you might try is importing the data into a staging table, not your final destination table. The import wizard will create this table for you.
Then post the table definitions of your staging table and your destination table.
Then we can help you write an INSERT statement to move the data from staging to your destination table.
hth...
February 8, 2011 at 11:37 am
Chrissy321 (2/8/2011)
Another thing you might try is importing the data into a staging table, not your final destination table. The import wizard will create this table for you.Then post the table definitions of your staging table and your destination table.
Then we can help you write an INSERT statement to move the data from staging to your destination table.
hth...
OK...
Staged Table
dbo.ItemsStaged
Columns are
Code (varchar(50),null)
Name (varchar(50),null)
Description (varchar(250),null)
Price (varchar(50),null)
UnitOfMeasureID (varchar(150),null)
RevenueTypeID (varchar(150),null)
What I need it to be is:
dbo.Items
ItemID (PK)
Code (nvarchar(60),null)
Name (nvarchar(60),null)
Description (nvarchar(250),null)
Price (decimal(20,8),null)
UnitOfMeasureID (FK, null)
RevenueTypeID (FK, null)
There are other columns; however, not worried about them.
February 8, 2011 at 11:55 am
I would make a copy of the table using the CREATE TABLE statement. Say the new table is called ItemsTest. Then you could run this
INSERT INTO dbo.ItemsTest
(
Code,
Name,
Description,
Price,
UnitOfMeasureID,
RevenueTypeID
)
SELECT
Code,
Name,
Description,
Price,
UnitOfMeasureID,
RevenueTypeID
FROM dbo.ItemsStaged
If this works and you were comfortable trying to insert into you destanation table then change the script.
If it doesn't you probably have a conversion and/or a foreign key problem.
Does this run?
SELECT
CONVERT((decimal(20,8),Price)
FROM dbo.ItemsStaged
Are these values present in the other tables referenced in the foreign key?
SELECT DISTINCT UnitOfMeasureID FROM dbo.ItemsStaged
SELECT DISTINCT RevenueTypeIDFROM dbo.ItemsStaged
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply