How To Change Column Names Dynamically In UNPIVOT TASK
Apr 10, 2008
Dear All,
We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.
Everytime we are checking and unchecking the column list in Unpivot Task.
can anybody help us to solve this issue that Unpivot task should take the column name dynamically.
Can someone please help I'm writting the following query. SELECT (SELECT c.column_name FROM information_schema.tables T JOIN information_schema.columns C ON t.table_name = c.table_name WHERE t.table_type = 'base table' and t.table_name like 'L_%' ) INTO #TempTable FROM TableA A LEFT JOIN [Server-Name].DB_Name.dbo.TableB B ON A.ID = B.ID
I'm trying to put commas between column names. How do I go about doing that?
I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
Hi All, I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example: Source table column Target table column
Name FN_Name Age FN_Age
The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.
Which tasks or approaches can be used to implement this?
I am developing several charts with column type and sub type as stacked. There is a requirement from the users that they want an option to choose the type of chart.
Is it possible to change chart type dynamically from say Column type to Line type based on user option in front-end?
I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005? Here's my post to the SQL devs to give you an idea what I'm trying to do. I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno Fldcaption----------- --------------11 9/07/200712 9/14/200713 9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
Hi, I have a SP which returns a select query on a temp table so I get to choose column names when I create the #table.
Can I determine column names myself based on the results of another query (in the SP) before, or (preferably) after I create the #table and populate it.
My query is used to bind to a datagrid so I could use....
dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want to keep the code in the SQL.
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?Â
I have an issue that feels like it requires an Unpivot task, but I'm struggling to visualise the best way to do it. I'd be grateful for any advice on this.
I have this dataset in my pipeline
moduleid startdate modenddate
Revenue MonthsOnModule
800091 05/09/2007 30/12/2007 150 3
800094 05/09/2007 30/11/2007 148 2
800095 05/09/2007 30/12/2007 300 3
Basically, each module has a start date and an end date. Therefore, with a simple datediff, we can get to MonthsOnModule.
Each module attracts an amount of revenue, which need to be allocated equally to each month over which the module runs.
In the example above, Module 800091 lasts 3 months, generates £150 revenue, so in September 2007, it attracts £50, in October 2007 It attracts £50, In November 2007 it attracts £50.
I'm using this package to populate a fact table in an OLAP Data Warehouse. The destination table needs to hold the data like this:
moduleid Month value
800091 200709 50
800091 200710 50
800091 200711 50
800094 200709 74
800094 200710 74
800095 200709 100
800095 200710 100
800095 200711 100
Now that looks like a pretty straight unpivot, but in order to unpivot, you have to have columns to unpivot into rows. In this case, I would need one column for each month that the module lasted. Modules can last any number of months. Some last 40 months, and there is no reason why one couldn't last 100 months or more.
I just can't really seem to visualise my approach to this transform. Anyone got any ideas?
I have a situation where i need to unpivot multiple columns using ssis. The data looks like
Name Age products1 products2 orders1 orders2 abc     23   cycle       radio         12        24 as Name  Age Products  orders abc    23     cycle      12 abc   23      radio      24
Is it possible to do this using the unpivot task in ssisMy actual data is has 18 columns which needed to be unpivoted into one and another 18 into another one.when using unpivot task it gives an error saying only one pivotvalue key is allowed.
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100) set @aw = '1990,1991' SELECT [name], [year], [data] FROM (SELECT * FROM t1 where [name] != 'name') p UNPIVOT ([data] FOR [year] IN (@aw) )AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
Hi All, We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100) set @aw = '1990,1991' SELECT [name], [year], [data] FROM (SELECT * FROM t1 where [name] != 'name') p UNPIVOT ([data] FOR [year] IN (@aw) )AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
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.
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders FROM (SELECT CisId, [Z_Id], [ModuleType] FROM CIS) p UNPIVOT (Orders FOR Employee IN (CisId, [Z_Id], [ModuleType]) )AS unpvt;
Error:Â The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
 I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview. Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx Here is some of my code..Private Sub ApplyFilter() Dim _filterExpression As String = "" If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then _filterExpression = "CategoryName = '{0}'" End If Me.SqlDataSource1.FilterExpression = _filterExpression End Sub Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged ApplyFilter() End Sub But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.
Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.
Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).
I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).
For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.
Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.
My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.
I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.
Hi. I have this kind of problem since I am not very conversant with SSIS and stripting in VB.NET.
The set-up is the following: Flat File Source -> Script Component -> Flat File Destination
The flat file source looks like this:
NameOfFile Headers Data Data Data and many more rows of Data NameOfAnotherFile Headers Data Data Data and many more rows NameOfAnotherFile Headers Data Data and so on in the same manner...
My stript looks like that (not very complicated): Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim strRow As String = Row.Column0.Trim() Dim strFolder As String = "Data" Dim strConn As String Dim charSep() As Char = {CChar(" ")}
If Mid(strRow.ToLower(), 1, 2) = "d0" Then
' This is the file name, so start a new connection to a new file ' which definitely does not exist yet strConn = strFolder + strRow + ".csv"
' DestinationFile is the name of the ' Flat File Connection Manager With Me.Connections.DestinationFile .ConnectionString = strConn End With
Else Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries)) End If End Sub
I have several questions regarding this one.
1. Is it ok to change the ConnectionString property of the manager to redirect the row to a different file? If not, what more to do?
2. If the new destination file does not yet exist, will it be created for me or will I get an error? If I do, what to do not to?
3. The most important: What steps to take so that a row is discarded from the pipeline in the script? I want some rows not to be directed to the file destination. These are the lines that contain the name of the file into which the data below belongs.
If you can optimize some steps in the script, then please do so by all means. Thank you for any comments and the knowledge you kindly agree to share with me. Darek
I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error. @TopX int ( or varchar) SELECT @TopX CompanyName, Amount FROM Sales Where..... Why will this not work?
Hello I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display. Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed. For instance in my page load event I would have a CASE statement with numerous SQLString Variables. Here is the current coding for my datasource <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter"> <SelectParameters> <asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" /> </SelectParameters> </asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the SelectCommand="SQLString" but this causes a syntax error Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'. Any help would be greatly appreicated!!
I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).
Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?
My test scenario contains:
- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)
- 1 Data Flow task in BIDS (SSIS)
- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)
- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01GWLINST1.TEST
I can dynamically change the name of the database via:
I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.