Solved

How to Create(populate) a Excel from a SQL Table(dynamic columns) Using SSIS

Posted on 2009-07-08
6
390 Views
Last Modified: 2013-11-10
Hello Experts,

I have an requirement of transferring Data from a SQL table, where table columns can varried weekly.

I know clearly how we transfer data from SQL Table to Excel using SSIS. but as columns are changing i had to remap the links(mata data) when changes occur in SQL Table.

thanks in advance
Naveen

0
Comment
Question by:jainnaveen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24801545
Yeah - that's a problem. SSIS needs to know its metadata.

You may prefer another solution, such as running a stored procedure which gets you something like:

Field1 + ',' + Field2 + ',' + Field3

This will just be a single field, but you can dump that out to a Flat File and have it look like something that Excel can open.

It's a workaround... but it should work.

Rob
0
 
LVL 2

Author Comment

by:jainnaveen
ID: 24809406
Hi Rob,

It's not going to solve my problem.

As the business user's are not ready to open in excel and do any additional work of converting text to columns and so forth.,

Lets hope other experts come with any solutions ??

thanks anyway.
Naveen
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24809901
Excel can open a CSV with no extra work - it should be just fine (and not require any extra work).

Try it - make yourself a .csv file that contains:

Field1,Field2,Field3
35,"Blah",7/8/2009
36,"Blah2",7/9/2009

...and see how it looks when you open it in Excel.

Rob
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Author Comment

by:jainnaveen
ID: 24810462
O yeah Rob,

I never knew before that this would open perfectly. And i will try doing ths way but my concern is would any other limitations in doing so. as i have many coloumns(say 100)  and when add up all columns as a single string ...

I have no doubt treating your comment as solution but i rather wait to see any other solution to this..

thanks Rob.
Naveen
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24811408
I know it's just a workaround, but it may help you get around one of the biggest limitations with SSIS.

Rob
0
 
LVL 2

Author Closing Comment

by:jainnaveen
ID: 31600987
Hi Rob,
I guess no one have any other solution, Well your workaround solution has worked for me in couple of reports.
thankyou you
Naveen
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server stored procedure parameter 10 20
SQL Consolidate rows 3 26
Need help with a query 14 36
Problem Backingh Up Transaction Log on Old SQL000 Database 3 21
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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