-
Import from excel error
Hi,
I am trying to import some data from excel file by changing some column names and adding some columns through DTS package. I have mapped all the transformations and it looks fine. But when i execute the package it gives an error "No value given for one or more required parameters." Can anybody tell me where i am mistaken....
Just follow steps for error:
-->create an excel file : Test with ABC,pqr column headings.
-->Import this to MS SQL and save the dts.
-->Add a column "xyz" to the sql table Test.
-->Now edit the dts transformation source tab sql query >> select `ABC`,`pqr`,`xyz` as 'null' from `Sheet1$`
You will find that Parse Query Would work. But when you click on preview. It will raise the error "No value given for one or more required parameters."
Last edited by mayurgogia; 11-24-2008 at 05:13 AM.
-
You can take xyz out of transformation.
-
You can try also setting IMEX=1 in Extended property of DTS designer.
or
try importing directly from the file
INSERT INTO TEST (desired_fields)
SELECT desired_fields
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=yes;
IMEX=1;Database=c:\filename.xls', 'SELECT desired_fields FROM [sheet1$]')
Or
you can import your file in a table with the exact structure and, after, alter the table adding your column 'XYZ'.
-
Hi Rmiao,
Thanks for reply. But that is my prime requirement. i want to add a field while data transformation.
-
Hi Bondi,
I have noticed that even the select query is not working when i am trying it for the excel file. Still i would try this thing. And i want to eliminate the involvement of physical tables. so i do not want to import file in a table and then adding column because i want to automate the task. that brings a long process within.
Thanks for your reply
-
Hi Mayurgogia,
But if you are able to import the file (no added column), in the same DTS you can add a SQL task that will alter the table for you and you can automate the process.
let me know!
-
Hi,
It is not the single table that i want to import. Destination table actually contains 'union all' of 3 different excel sheets with alias names for columns.
i have created the required destination table and want to import these sheets into a single table to avoid union all operation that i use in a view to combine data.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|