wppiexperts
asked on
SSIS: working with Excel source data
I have a large Excel spreadsheet that I want to load into an SSIS package and output relational datasets.
My first throught was to load the data straight into a recordset and then through code and loops, pull out the data into new recordsets.
Next, I've been playing with the Excel source editor and trying to query the Excel data and then intending to just output the query results into recordsets (but I'm receiving the error: No column information was returned by the SQL command).
Which method would be better to work with and how should I go about doing it?
My first throught was to load the data straight into a recordset and then through code and loops, pull out the data into new recordsets.
Next, I've been playing with the Excel source editor and trying to query the Excel data and then intending to just output the query results into recordsets (but I'm receiving the error: No column information was returned by the SQL command).
Which method would be better to work with and how should I go about doing it?
The second option would be the way to proceed... Try to use the Query editor to build your query and also check the output on the same window
But then it might take a while to run, let us know what you are trying to do.. to help you better
But then it might take a while to run, let us know what you are trying to do.. to help you better
ASKER
the data is a non-normalized dataset...each Excel row essentially contains account data plus 50 columns of historical data. My intent is to pull out data to create header records and detail history records and then upload into a sql database, so my initial thought was to load it into a master recordset and work with it from there, once the data manipulation was done, then load it into the database.
any suggestions (or web articles to follow) that could get me started in the right direction?
any suggestions (or web articles to follow) that could get me started in the right direction?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
unfortunately, our policy is to not use temp tables, so recordsets are the method I have to work with.
I would still look in to the other ssis transformations. Will be much quicker. And do the same job.
Honestly, if u r using a recordset, u r defeating the purpose of using ssis.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Regarding your error:
see that the SQL query that you write against the Excel file has aliases for the column names and be very very sure that you use 32-bit. In BIDS you can set the Run64BitRunTime property to false in your project properties. At SQL Server, if it is a 64 bit machine, you must enforce 32-bit. In 2005: calling the package with a cdm command. In 2008: you can select an option in the job step properties to run the package in 32-bit.
see that the SQL query that you write against the Excel file has aliases for the column names and be very very sure that you use 32-bit. In BIDS you can set the Run64BitRunTime property to false in your project properties. At SQL Server, if it is a 64 bit machine, you must enforce 32-bit. In 2005: calling the package with a cdm command. In 2008: you can select an option in the job step properties to run the package in 32-bit.
this is not recommended when your excel file is large.
if you want to do operations on data and then feed in table or any other destination, you can use many transformation in SSIS for this purpose and usually you don't need to feed a recordset.
please let me know what you want to do in your data transfer exactly, maybe there be better ways.