October 27, 2010 at 10:31 am
Is there a way to have a query that generates dynamic columns exported to an excel spreadsheet? The problem that I've run into is that in the Data Flow, you have to have the source and destination columns pre-defined... is there a way around this?
I've read about having a script that essentially generates a SSIS package on the fly... this is way too much complicated.
What I've got:
1. Build dynamic filename for Excel Spreadsheet.
2. Check for existance of the spreadsheet file; delete it if present.
3. Execute SQL task (connected to a SQL Server) runs a query with dynamic column names (based on the year being executed for). The results are stored in a permanent table.
4. Execute SQL task (connected to an Excel Connection) runs a create table statement to create the spreadsheet and necessary tabs within it.
5. Data Flow task:
a. OLE DB Source with a dynamically generated select statement to get the data from the dynamically generated table.
b. Data conversion - to convert the char columns to nchar (if I can get this dynamic thing working, I'll change the previous select to use the convert function to nchar there, and eliminate this step in the package).
c. Excel Destination to the spreadsheet.
When I run this package, it errors out in the Data Flow task - the excel destination won't validate, due to differences between design time and run time. However, you have to set this up at design time. I also anticipate issues with the data source being different from design time and run time.
One thing that I had tried, and it works on every server except the one I need it to work on... using OpenDataSource to select * from the dynamically created table, and into the spreadsheet (and ad hoc querying is enabled...).
So, is there a way to have a dynamically created source be exported to an Excel spreadsheet with dynamically created column names?
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 27, 2010 at 11:19 am
Dynamic name can be done either via script or via a Variable set as "Evaluate as Expression".
Using the File Connection (not the Flat File Connection), you can dynamically create the file from scratch and populate it that way.
I'd give more details, but I'm about to walk into back-to-back meetings. You can google these items and find articles, check Books Online, etc. I'll check this topic later when I have a chance.
October 27, 2010 at 1:07 pm
Brandie, if I'm understand you correctly, you are saying to dynamically set the file name.
This part is fine... what I need is that columns associated with both the source SQL table, and the destination Excel spreadsheet have some dynamic names associated with it. These cannot be set by an expression.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 27, 2010 at 2:26 pm
There's an option in SSIS to delay validation of the data sources till they are called. I've used that to avoid the error about design-time vs run-time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 4:16 am
WayneS (10/27/2010)
Brandie, if I'm understand you correctly, you are saying to dynamically set the file name.
Nope. That's a different, separate thing. You can dynamically create the file, and all its columns, by using a File Connection.
It's been a while since I've done it. I'll play with it when I get time so I can tell you how to do it.
EDIT: Wayne, is it only column names you're having issues with? Or are you also trying to dynamically set different data types?
October 28, 2010 at 5:06 am
WayneS (10/27/2010)
3. Execute SQL task (connected to a SQL Server) runs a query with dynamic column names (based on the year being executed for). The results are stored in a permanent table.
Real quick question, Wayne. Is this like a Rolling 12/13 months kind of column naming?
If not, could you give more details on what you're doing for the dynamic column names?
October 28, 2010 at 6:35 am
October 28, 2010 at 7:10 am
If the number and type of columns doesn't change, but you want the column headers to change, you might also be able to include "column headers" as a row of varchar fields in your query, using a Union operator.
select 'Region' as Col1, 'March2009' as Col2, 'April2009' as Col3
union all
select MyRegionColumn, MarSales, AprSales
from MyPivotOperator;
Something like that. Then just don't include column headers in the export definition. Voila, "fake" column headers that Excel will treat exactly the same way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 7:30 am
Brandie Tarvin (10/28/2010)
WayneS (10/27/2010)
Brandie, if I'm understand you correctly, you are saying to dynamically set the file name.Nope. That's a different, separate thing. You can dynamically create the file, and all its columns, by using a File Connection.
It's been a while since I've done it. I'll play with it when I get time so I can tell you how to do it.
EDIT: Wayne, is it only column names you're having issues with? Or are you also trying to dynamically set different data types?
It's only the column names - the data types are the same.
Real quick question, Wayne. Is this like a Rolling 12/13 months kind of column naming?
If not, could you give more details on what you're doing for the dynamic column names?
The procedure is doing a forecast of expenses for the specified (school) year, plus the next 4. So, if year 2009 is specified, I'll have columns like Y09_Cnt, Y09_Paid, Y10_Cnt, Y10_Paid... Y13_Cnt, Y13_Paid
If run for year 2010, they will be Y10_Cnt, Y10_Paid, Y11_Cnt, Y11_Paid...Y14_Cnt, Y14_Paid
The data types will be the same, just the name of the column at that position will be different.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 7:34 am
Brandie Tarvin (10/28/2010)
Last post until I get more info from you on how you're dynamically generating your column names (code sample?) from the database.Looks like there was an article on SSC for this before.
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61542/[/url]
Does this help?
I had looked at this - I've even used it in the past. However, it is about making multiple tabs in spreadsheet of the same format, with the same (unchanging) column names, so it's not doing what I'm trying to do.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 7:35 am
In that case, either a Union with "fake" column names, or exporting the column names once, then the data for them and appending it, will work just fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 7:39 am
Are you using PIVOT or COALESCE in your code to generate the column names?
I ask because the closest thing I've got to what you're doing sets a variable using COALESCE and then spits out the column names in a PIVOT. If I can use that as the basis for any solution I give you, it'd cut down my work load and enable me to give you a solution relevant to what you're doing.
October 28, 2010 at 8:00 am
Brandie Tarvin (10/28/2010)
Are you using PIVOT or COALESCE in your code to generate the column names?I ask because the closest thing I've got to what you're doing sets a variable using COALESCE and then spits out the column names in a PIVOT. If I can use that as the basis for any solution I give you, it'd cut down my work load and enable me to give you a solution relevant to what you're doing.
I can look at the code in a few hours when I get to work. (It's inherited code.) As I recall, it's doing more of a dynamic cross-tab. I'll post code later.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 11:15 am
The code that creates the dynamic tables is run through an Execute SQL Task, connected to the SQL db:
CREATE PROCEDURE dbo.MyProc(
@SY varchar(10)
)
As
declare @SY_0 varchar(10)
declare @SY_1 varchar(10)
declare @SY_2 varchar(10)
declare @SY_3 varchar(10)
declare @SY_4 varchar(10)
set @SY_0 = right(@SY,2) + 1
set @SY_1 = @SY_0 + 1
set @SY_2 = @SY_0 + 2
set @SY_3 = @SY_0 + 3
set @SY_4 = @SY_0 + 4
declare @sql_2 varchar(max);
set @sql_2 =
'
select
d.region_cd,
d.brigade_cd,
d.dir_sch_cd,
d.sch_nm,
0 as MS' + @SY_0 + '_CNTS,
0 as MS' + @SY_0 + '_Books,
0 as MS' + @SY_0 + '_Estimated,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') and c.comm_dt BETWEEN msy0.fiscal_yr_start_dt AND msy0.fiscal_yr_end_dt
then isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as MS' + @SY_0 + '_Paid,
0 as MS' + @SY_1 + '_CNTS,
0 as MS' + @SY_1 + '_Books,
0 as MS' + @SY_1 + '_Estimated,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') and c.comm_dt BETWEEN msy1.fiscal_yr_start_dt AND msy1.fiscal_yr_end_dt
then isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as MS' + @SY_1 + '_Paid,
0 as MS' + @SY_2 + '_CNTS,
0 as MS' + @SY_2 + '_Books,
0 as MS' + @SY_2 + '_Estimated,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') and c.comm_dt BETWEEN msy2.fiscal_yr_start_dt AND msy2.fiscal_yr_end_dt
then isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as MS' + @SY_2 + '_Paid,
0 as MS' + @SY_3 + '_CNTS,
0 as MS' + @SY_3 + '_Books,
0 as MS' + @SY_3 + '_Estimated,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') and c.comm_dt BETWEEN msy3.fiscal_yr_start_dt AND msy3.fiscal_yr_end_dt
then isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as MS' + @SY_3 + '_Paid,
0 as MS' + @SY_4 + '_CNTS,
0 as MS' + @SY_4 + '_Books,
0 as MS' + @SY_4 + '_Estimated,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') and c.comm_dt >= msy4.fiscal_yr_start_dt then
isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as MS' + @SY_4 + '_Plus_Paid,
count(distinct so.student_id) as Total_Contracted,
sum(case when so.obligation_cd in (''VAL'',''ADD'',''ADJ'') then isnull(so.tuition_qy,0)+isnull(so.fee_qy,0) end) as Estimations_Paid
into dbo.SCLR_Contracted_Cmd_Total
from west_ccims_prod.dbo.sdmt_student ss
inner join west_ccims_prod.dbo.cadet c on c.cadet_id = ss.cadet_id
inner join west_ccims_prod.dbo.directory d on d.dir_sch_cd = ss.rotc_sch_cd
inner join west_ccims_prod.dbo.sdmt_scholarship sch on sch.student_id = ss.student_id
left outer join west_ccims_prod.dbo.scht_obligations so on so.student_id = ss.student_id
left outer join west_ccims_prod.dbo.Mission_Set_Yrs msy0 ON msy0.mission_cd = 0
left outer join west_ccims_prod.dbo.Mission_Set_Yrs msy1 ON msy1.mission_cd = 1
left outer join west_ccims_prod.dbo.Mission_Set_Yrs msy2 ON msy2.mission_cd = 2
left outer join west_ccims_prod.dbo.Mission_Set_Yrs msy3 ON msy3.mission_cd = 3
left outer join west_ccims_prod.dbo.Mission_Set_Yrs msy4 ON msy4.mission_cd = 4
left outer join west_ccims_prod.dbo.DRCT_Missioning dm0 ON dm0.dir_sch_cd = d.dir_sch_cd AND dm0.mission_set_yr_dt =
msy0.mission_set_yr_dt
left outer join west_ccims_prod.dbo.DRCT_Missioning dm1 ON dm1.dir_sch_cd = d.dir_sch_cd AND dm1.mission_set_yr_dt =
msy1.mission_set_yr_dt
left outer join west_ccims_prod.dbo.DRCT_Missioning dm2 ON dm2.dir_sch_cd = d.dir_sch_cd AND dm2.mission_set_yr_dt =
msy2.mission_set_yr_dt
left outer join west_ccims_prod.dbo.DRCT_Missioning dm3 ON dm3.dir_sch_cd = d.dir_sch_cd AND dm3.mission_set_yr_dt =
msy3.mission_set_yr_dt
left outer join west_ccims_prod.dbo.DRCT_Missioning dm4 ON dm4.dir_sch_cd = d.dir_sch_cd AND dm4.mission_set_yr_dt =
msy4.mission_set_yr_dt
where so.student_id is not null
and sch.sclr_award_cd in (''2'',''3'',''4'',''G'')
and so.school_yr_dt = ''' + @SY + '''
group by d.region_cd, d.brigade_cd, d.dir_sch_cd, d.sch_nm
order by d.region_cd, d.brigade_cd, d.dir_sch_cd, d.sch_nm
'
The code that creates the Excel spreadsheet is run through an Execute SQL Task, connected to an Excel Connection. The code is build through a variable, through it's expression, which is defined as:
"
CREATE TABLE `SCLR_Contracted_Cmd_Total` (
`region_cd` VarChar (1) ,
`brigade_cd` VarChar (1) ,
`dir_sch_cd` VarChar (6) ,
`sch_nm` VarChar (30) ,
`MS" + MS0 + "_CNTS` Long ,
`MS" + MS0 + "_Books` Currency ,
`MS" + MS0 + "_Estimated` Currency ,
`MS" + MS0 + "_Paid` Currency ,
`MS" + MS1 + "_CNTS` Long ,
`MS" + MS1 + "_Books` Currency ,
`MS" + MS1 + "_Estimated` Currency ,
`MS" + MS1 + "_Paid` Currency ,
`MS" + MS2 + "_CNTS` Long ,
`MS" + MS2 + "_Books` Currency ,
`MS" + MS2 + "_Estimated` Currency ,
`MS" + MS2 + "_Paid` Currency ,
`MS" + MS3 + "_CNTS` Long ,
`MS" + MS3 + "_Books` Currency ,
`MS" + MS3 + "_Estimated` Currency ,
`MS" + MS3 + "_Paid` Currency ,
`MS" + MS4 + "_CNTS` Long ,
`MS" + MS4 + "_Books` Currency ,
`MS" + MS4 + "_Estimated` Currency ,
`MS" + MS4 + "_Plus_Paid` Currency ,
`Total_Contracted` Long ,
`Estimations_Paid` Currency ,
`Estimations_Unpaid` Currency ,
`Total` Currency
)
"
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 9:57 am
Wayne,
When I removed the double-quotes at the beginning and end of your create table statement, I was able to create the Excel spreadsheet with no problems. However, it gave me column names like MS" + MS0 + "_CNTS.
What are the MS0 - MS4? Are they variables in your package?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply