Import from excel error

[mayurgogia]

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."

[rmiao]

You can take xyz out of transformation.

[bondi]

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'.

[mayurgogia]

Hi Rmiao,
Thanks for reply. But that is my prime requirement. i want to add a field while data transformation.

[mayurgogia]

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

[bondi]

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!

[mayurgogia]

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.