asked on

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

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

Rob Farley

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

jainnaveen

ASKER

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

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jainnaveen

ASKER

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

Rob Farley

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

Rob

jainnaveen

ASKER

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