troubleshooting Question

Dynamically Load SQL Query in SSIS and Export to Excel

Avatar of MJ121
MJ121 asked on
Microsoft SQL Server
2 Comments2 Solutions1240 ViewsLast Modified:
I have been tasked to automate our reporting process from an old forest and trees format to using SSIS and SQL.  The main goal is to automate hundreds of reports that get exported daily to excel.  I need a way to dynamically pass or read in a query, execute it and then export the results to excel.  I have a DB of the report names, report query locations (which are all in a .sql files), and the location the report needs to export to.  

I can successfully read the query file and execute it using an Execute SQL Task and stuff the results in an object variable.  One of the issues is getting the data out of the object and into excel.  The second issue is getting SSIS to deal with the column names.  I dont want to setup hundreds of packages and map the columns by hand to get this process to work.  (Yes, they have to be in excel and not .csv format).  The third issue I am running into is when I use a Data Task Flow with an OLE DB Source and pass in the SQL Query as a variable I am hitting the 4000 character limit on strings.  I have also tried using the Script Component as a source but am unsure if you can/ how to dynamically create output rows.  

Is this even possible?  

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros