Dynamic Header Column Names In Excel
Jul 3, 2007
Folks,
I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.
With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.
When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).
How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...
Thank you.
View 5 Replies
ADVERTISEMENT
Nov 14, 2007
I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.
CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)
INSERT INTO #owner VALUES ('ME');
INSERT INTO #owner VALUES ('Other');
CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)
INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');
CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)
INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');
DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property
| NameId | Name |
| 1 | ME|
| 2 | other |
| TypeId | Name |
| 1 | Home |
| 2 | Car |
| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |
Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:
[Owner | Home | Car ]
[ME | Blue | Black ]
[Other| Red | Black ]
The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.
View 9 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Jan 1, 2008
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies
View Related
Jun 10, 2004
Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
View 2 Replies
View Related
Jan 19, 2005
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
View 2 Replies
View Related
Aug 28, 2007
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
Mike
"oh, that monkey is going to pay"
View 3 Replies
View Related
Aug 9, 2007
Hi guys 'n gals,
I am trying to achieve something a bit complex (or at least appears to be for me).
I have the following table structure:
UID, GroupID, ColumnName, ColumnValue
here is some example data:
UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven
and here is what I would like the example to come out like:
GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven
It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...
I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:
Code Snippet
DECLARE @ColumnName varchar(128)
DECLARE @Sql varchar(255)
SELECT @ColumnName = [ColumnName] FROM tblSourceData
SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData'
EXEC (@Sql)
Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?
Regards,
Justin
View 6 Replies
View Related
Oct 13, 2001
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
View 1 Replies
View Related
Dec 20, 2007
While trying to set up an unpivot transformation to load data from excel (2003) into sql server db, the dates as column headers get lost in the translation.
To simplify the problem I created a very simple package with an excel source and an excel destination.
The test Excel Source looks like
ID 1/1/2008 3/1/2008 5/1/2008
A 5 7 9
B 10 12 24
After running the package The destination looks like this:
ID F4 F5 F6
A 5 7 9
B 10 12 24
I need to keep the dates since I am loading a large volume of data often.
Any suggestions?
View 4 Replies
View Related
Apr 9, 2015
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Insert
('+@columns+',KickoutID) Values('+@columns+',1);
Dynamic code:
Declare @columns as nvarchar(max);
Declare @InvSQL as nvarchar(max);
SELECT @columns =
STUFF ((
SELECT ', [' + name + ']'
FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
[Code] ....
View 8 Replies
View Related
Oct 25, 2006
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View 2 Replies
View Related
Mar 13, 2008
I need some help. I am writing a report in SSRS 2005 that I then need to export to Excel. When I put a report header I would expect the header to not display in the Excel spreadsheet until the Print Preview or the Print. The report footer works just fine I put some text in the footer, and it shows up in the footer. The header though, shows up as a row in the Excel spreadsheet that then causes columns to merge. How do I get the report header to act like a page header?
View 1 Replies
View Related
Apr 29, 2015
I have multiple excel Files  each has one sheet (With same column names) need to be loaded in a single table. I tried For each loop but couldn't succeed. Â
As I am new to SSIS. How to configure For each loop container for this...
View 5 Replies
View Related
Sep 29, 2006
Hi,
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
Any help appreciated,
TIA, Anthony.
View 4 Replies
View Related
Mar 8, 2008
I have a data flow that consists of
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination
I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
View 6 Replies
View Related
May 18, 2011
Have to create SSIS package for the below requirement:
I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table.
The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table.
I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table.
I have given a sample data structure below:
Source Data
Excel1_Order.xls
OrderNumber    OrderQuantity    OrderDate
Order10001Â Â Â Â Â Â Â Â Â Â Â Â 100Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01-01-2011
Excel2_Customer.xls
CustomerNumber     CustomerName    CustomerAddress
[Code] ....
Is there any way to handle this in SSIS?
View 16 Replies
View Related
Feb 22, 2006
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
View 3 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Feb 27, 2008
In a crosstab, how do I make a header increase/decrease dynamically when the columns chosen increase/decrease?
Please reply ASAP
View 1 Replies
View Related
Jan 23, 2007
Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.
=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)
This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.
This is the error that is fires back when I run the report.
The Value expression for the textbox €˜textbox4€™ contains an error: The expression referenced a non-existing field in the fields collection.
I am quite new to SSRS and I am using VS2005 pro.
Thanks
D
View 1 Replies
View Related
Feb 28, 2007
I've built a fairly straight forward report in RS that looks normal in preview mode and in PDF format with out any issues.But when I export it to Excel report header is not appearing in each page.Any ideas as to why this is occurring?thanks in advance,Ramesh KS
View 1 Replies
View Related
Jan 10, 2008
Is there a way for the rdl to make a header/title and make it to be displayed in the excel header when exported?
View 6 Replies
View Related
Mar 13, 2008
Hi,
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
Please suggest how could i accomplish this
Regards,
Kalyan
View 8 Replies
View Related
May 7, 2015
Have a table with a structure like:
FIELD_A
FIELD_B
FIELD_C
FIELD_D
AMOUNT
DATA_A1
DATA_B1
DATA_C2
DATA_D2
37540
[Code] ....
In such a case, devising a dynamic cross-tab query, to cover all the items, and result like a pivot like the following to represent the data in a multi-header pivot like following:
DATA_C1
DATA_C1
DATA_C1
DATA_C2
DATA_C2
DATA_C2
[Code] ....
View 5 Replies
View Related
Jun 5, 2008
Hi all,
I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.
According to all of the searches and articles I have read today, the following should work:
DECLARE @rownumINT
DECLARE @tabNamNVARCHAR(100)
DECLARE @SQLStringNVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'
SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'
EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum
However, it fails with:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".
Anybody know why?
Cheers
View 1 Replies
View Related
May 9, 2007
Hi all,
Does anyone know of a way to keep from exporting a page header when rendering in Excel. We have a default header (title and logo) that I would like to suppress so that users don't need to delete the rows prior to sorting (to eliminate merged cells).
Thanks,
Simone
View 4 Replies
View Related
Nov 12, 2007
Is there a way to avoid exporting the page header to Excel (or any other renderer). My reports seem to lose their charm without the headers (setting Print on First/Last Page to false), and at the same time, the users don't want the headers exported to Excel.
Any input will be appreciated. Thanks.
View 5 Replies
View Related
Jul 20, 2005
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
View 1 Replies
View Related
Jul 28, 2006
I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.
I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.
thanks,
Scott
View 3 Replies
View Related
Aug 24, 2007
Has anyone else had this issue? Let me explain...
NOTE: Excel 07' is installed on my machine, but saved file as Excel 97-03
SSIS package-
-Execute sql task with sql statement that drops table (drop table [sheet1])
-Execute sql task with sql statement to create table
-Data flow task to send some rows to excel sheet
Running this package over and over will keep appending rows to the excel sheet.
Upon further examination I'm noticing that the drop table excecute sql task is only deleting the header record.
I know this used to work when I only had the previous version of excel installed on my machine.
Any ideas?
Is this a problem when working with Excel 2007 in compatibility mode?
I'm about to just delete the file altogether and re-create it.
View 3 Replies
View Related
Sep 20, 2007
I have a sum on a reportitems cell in my header:
=Sum(ReportItems!textbox1.Value)
When I run the report, it looks excellent. My issue is when I export it. When I export to Excel, it looks just like it did.
When I export to PDF, it gives me a total per page, not for the report.
Does ReportItem behave differently when rendered between excel and PDF? Or is it because I am putting a SUM on a ReportItems cell?
View 23 Replies
View Related
Jun 4, 2015
We have a process here that produces several dozen Excel Spreadsheets every day from SQL Server using the bcp utility. the problem is that we cannot include the column headers when producing the Output. We are therefore trying to find a method where we can append the column Headers to the Spreadsheets; is there a way of doing this?
View 2 Replies
View Related
