Solved

SSIS: working with Excel source data

Posted on 2010-08-17
10
1,547 Views
Last Modified: 2013-11-10
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?

0
Comment
Question by:wppiexperts
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33459480
why you want to feed data from excel to recordset ?
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.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33460405
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
0
 

Author Comment

by:wppiexperts
ID: 33465400
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?

0
 
LVL 16

Accepted Solution

by:
carsRST earned 67 total points
ID: 33465596
My opinion is you may be wasting resources if you load in to a recordset.  

A couple of other options:

1.  Just load the Excel file and manipulate AFTER the load.  Possibly put in to a temp upload table, make modifications, ect.. then migrate to a final destination.  Or load in to final table and use time stamp to massage the just loaded data.

2.  Or use expressions, derived columns, data conversion transformations.

I would avoid recordset as much as possible.  Just not efficient.
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 67 total points
ID: 33465803
That's right  you don't need to use record sets for doing that.. You can make use of Staging Table(Temp Table), Derived Column for writing expressions and converting Data types and Lookup Transformation (if necessary) ..
Check these links
http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
http://www.ssistalk.com/2007/01/23/derived-column/
http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx
http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part1_p1.aspx

Let me know if you need more help
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:wppiexperts
ID: 33466232
unfortunately, our policy is to not use temp tables, so recordsets are the method I have to work with.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33466262
I would still look in to the other ssis transformations.  Will be much quicker. And do the same job.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33466294
Honestly, if u r using a recordset, u r defeating the purpose of using ssis.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 66 total points
ID: 33467454
I am agree with carsRST which you should use TRANSFORMATION instead of record set,
tell us sample input data and sample output
0
 
LVL 1

Expert Comment

by:da-zero
ID: 33482970
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculated columns 13 60
Converting Teradata SQL to Oracle SQL (exadata) 3 28
How to use odbc in vb to connect to ms sql 14 31
SQL Server stored proc 2 10
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now