I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:
Code:
select
'<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>'
from tbl_ID
however this obviously just exports the text
<a href="[URL]" target="_blank" nav="web">[ID]</a>
This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?
Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.
I've created a report in SSRS 2005, where one of the fields is using 'Jump to URL'. I made that field the color blue and underlined it so the users can see it's a link.
How do I change the color of the field/link to a different color once it's viewed so the users know they already selected that field?
I want to store a hyperlnk in the MS-SQL table as in MS Access.I could not find any datatype for storing the hyperlink in MS-Sql. So Kindly please help me to store hyperlink in the MS-SQL Table.
Hi Folks, Is it possible to add <a href=""http://www.yahoo.com"">xxxxxx</a> tag in varchar column as a text and display it as html hyperlink xxxxx (hyperlink). Thanks in advance Regards
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time. I can't even get a static pivot to work.
This has probably been asked before and I am having a hard time finding an answer for my question.
What I'd like to do is create an SQL query script file that exports the records of a select statement to a delimited file. I used to know how to do this in Oracle but I can't find any way to do this in SQL Server.
In Oracle I used to call a command from the script to change the output to a named file, then call a command to set the field delimiter, and finally, execute the query. I don't want to use BCP because I want to get the results of a query that select only a subset of records and may gather data from multiple tables. I know I can save a query and then change the output from the Management Studio to a file but I don't want to have to do this manually every time. Being able to run the scrip from a command line would be great. Then I could schedule a task that would execute the script at a preset time.
Wanted to enquire how this is done. Tried doing it in the setUsageType method I have overwritten but only allows description to be changed. Basically need to change "Name".
Best option would be to change it instantly when a user selects a column from the inputs in the custom component, ie. it changes the Output Alias to a desired value. (Input tab in advanced editor)
All this is being done in a custom component which I would like to be synchronous, can achieve a similar result asynchronously.
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors).
I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message:
Property Value is not valid.
The details are:
Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
select hierarchy.hiername,devicefail.deviceid ,sum(DATEDIFF(minute,started,ended)) as duration ,100 - SUM(datediff(minute,started,ended))/(672 * 60.0000)*100 AS Uptime from devicefail LEFT JOIN device ON device.deviceid = devicefail.deviceid LEFT JOIN hierarchy ON device.hierlevel = hierarchy.hierlevel where devicefail.started >= '2013-02-01 00:00:00'and devicefail.ended <='2013-02-28 23:59:59' and devicefail.componentid like 201 or devicefail.componentid like 0 group by devicefail.deviceid,hierarchy.hiername,devicefail. componentid order by hiername
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (aexe.ReturnCode) as '# Patched' from WrkSta, AeXEvt_AeX_SWD_Execution aexe where WrkSta.WrkStaId=aexe.WrkStaId and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') and (aexe.returncode='0' or aexe.returncode ='3010') and aexe.AdvertisementName like 'MS05-035-043%' group by substring (WrkSta.[Name], 1,2)
--- Returns : Location # Patched EP 102 ES 1986 ET 19 AB 174 SU 6 EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (coll.WrkStaId ) as '# Workstation' from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596' and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') group by substring (WrkSta.[Name], 1,2)
--- returns:
Location # Workstations EP 178 ES 2299 ET 24 AB 215 SU 13 EM 582
What I need is :
Location # Workstations # Patched EP 178 102 EI 2299 1986 ET 24 19 AB 215 174 SU 13 6 EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else? Once again I appreciate your help in advance....
Hello,I was wondering if anyone can help me figure something out.Is it possible to do a querey in MS SQL server and have the resultsreturned so that each result in each row is preceeded by the columnname?eg. instead of usual output -colName1, colValue1,colName2,colValue2,colName3,colValue3 ?Also I would like to only have this for certain columns ie in theabove example only for columns 2 and 3Thank you! :-)Yas
I would like to get the actual name of the column that has the error. Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?
I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column. I am going from Flat File to OLE DB Destination. I have a Script Component getting the output to write to my sproc, and I just need to get the column name.
I have a Data Flow Task that extracts some data using a DataReader Source and loads it to a Raw File Destination. I am getting the following error message:
[DataReader Source [2357]] Error: The value was too large to fit in the output column "LASTCOL" (2558).
I thought that using a Raw File Destination would avoid this type of problem. How can I resolve this issue?
using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed? same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error: "The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38". the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
Is there a way to supress output on one column in a SP, using data from the same row?
Like This: SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null? DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning. I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS BEGIN SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility FROM dbo.tblResidents A WHERE DOT IS NULL UNION SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility FROM dbo.tblResidents I END GO
ID AMT 1001 1234.560 1001 34.560 1001 134.000 1002 45.000 1002 3456.000 1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt. 01 + ID 02 + AMT 03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....
Have a table with 5 columns
col1 int null,
col2 int null,
col3 char(3) null,
col4 bit null,
col5 int null
My data flow loads data from a flat file and has a record that looks like this
1 5 ABC R 3
I want the row to make it to the destination as follows....
1 5 ABC NULL 3
Then the offending data needs to go over to my error table
Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?
I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long. However, some data in this column is actually up to 15 characters long. This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :
Datatype : Unicode string [DT_WSTR] Length : 10
Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?
I have created a table and found that i miss named a column. All i want to do is change the column name. But I don't see anything in ALTER TABLE to do that.
