Solved

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

Posted on 2009-07-08
6
392 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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