Can anyone provide any direction in extending existing SSIS components with a custom component, if it's possible at all. In some cases there are just slight bits of functionality missing that I think I could add in myself. But, I'm not much of a programmer and need a bit of help in the declaration of the component in my own custom component.
Thanks.
Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
How can I create a source extension that reads a text file and returns a XML file? I would like to have an xml File as output, because the information is already normalized.
If I extend the class €śPipelineComponent€? I can just define IDTSExternalMetadataColumn90 as output. And I do not want to have columns. I would like to return a xml file
Is it possible to achieve this?
Hi all,
I have downloaded Microsoft SQL Server 2005 Enterprise Evaluation Edition ISO from the following site and burned it onto a DVD.
http://www.microsoft.com/downloads/details.aspx?FamilyId=6931FA7F-C094-49A2-A050-2D07993566EC&displaylang=en
Prior to this I have installed Visual Studio 2005 Professional Edtion. During setup I have selected all the native client components i.e. Management Studio, Business Intelligense Studio. But after the install I am not able to see them under All Programs-> Microsoft SQL Server 2005. On further searching I have come across this link
http://channel9.msdn.com/ShowPost.aspx?PostID=142941
First of all, there is no sqlrun_tools.msi under setup folder of the dvd. And also when I tried to change component from Add/Remove programs and following the instructions as specified on clicking Change Installed components all that I see are: Connectivity Components and Software Development kit.
Could someone tell me how can I install Management Studio/ Business Intelligence Studio seperately? I don't want to go through the process of re-installation.
Hi,
In another thread Jamie Thomson very informatively said "The components in SSIS
are deliberately atomic (i.e. they do something very specific) so that
its easy to put them together to build something greater than the sum
of the parts". Which does make a lot of sense. However, I've been finding that I end up having to create exactly the same "pattern" of combined transform components again and again in order to solve the same problem but in different dataflows (or even within the same dataflow). Cut-and-paste-tastic! In order to obtain real re-use, it seems to me like SSIS is crying out for an easy way to create new components by using composition - i.e. the ability to take commonly-used combinations of existing components and create new "super" components (without having to write Custom Transform Components in C#/VB.Net and handle everything in code).
Does anyone know if this sort of functionality is likely to make it into SSIS in the forseeable future?
Regards,
Lawrie
Is there a way to add functions to SQL (other than stored proceedures) to emulate functions in other systems?
I would like to create a FOXPRO type OCCURS function:
Select * FROM Table1 WHERE OCCURS('Y', FLAGS) > 3
If FLAGS is a VARCHAR(7) and equals "YYYYNNN" then OCCURS would return 4 and the row would return. The database that I am
accessing has >165,000,000 rows and >500 columns per row so you see that I need all the speed I can get!
'
' This is a VB function which works.
' Can this be compiled into a DLL and
' somehow made available to SQL?
'
Public Function OCCURS(expr1 As String, expr2 As String) As Integer
Dim i As Integer
Dim count As Integer
For i = 1 To Len(expr2)
count = count + Abs((Mid(expr2, i, 1) = expr1))
Next
OCCURS = count
End Function
I seem to have a problem extending the log portion of tempdb onto a log device used by another user defined database. Is this a known problem?
Thanks
I need some help forming a query
I have 3 Tables...
Table1: Users
Fields: UserID Int
Username Varchar(50)
Table2: User_Categories
Fields: User_CatID Int
User_ID Int
Table3: Categories
Fields: CatID Int
Catname Varchar(100)
Now consider Table 3 has the following values
CatID | Catname
1 | cat1
2 | cat2
3 | cat3
4 | cat4
5 | cat5
6 | cat6
7 | cat7
8 | cat8
9 | cat9
Now I need to select users who fall into the categories with CatID 5, 1, 3. Also users can fall into more than one category.
The users should be sorted by the 1st the users who fall under 5, then users who fall under 1 and then the users who fall under 3 and then finally by the userID in the descending order.
SELECT userid, username
FROM Users, User_Categories
WHERE userid=user_catid AND user_catID IN (5, 1, 3)
ORDER BY userid DESC;
How do I extend this to get the above needed result? Any help will be highly appreciated.
Hi,
If anyone can help me extending the following query I'd be really grateful! :)
I have 4 tables, Product, RawProduct, RawProductPriceHistory and RawProductPromotionalHistory. The relationship is a Product can have multiple RawProducts (one for every retailer the product is stocked in), and the PriceHistory and PromotionalHistory tables keep track of when the RawProduct's price changes, and when it comes on or off of promotion.
I have the following SP to return prices for the given Product for the given Date.
I need to extend the SP so that it also returns details from the RawProductPromotionalHistory table if a PromotionalHistory entry occurs for the passed date (@Date). We determine whether a PromotionalHistory exists by whether the passed @Date >= RawProductPromotionalHistory.StartDateTime and @Date <= RawProductPromotionalHistory.EndDateTime. The EndDateTime can also be NULL - indicating an ongoing promotion.
Here is the schema of the RawProductPromotionalHistory table:
http://www.boltfile.com/directdownload/rawproductpromotionalhistory.jpg
And here is the current SP:
ALTER PROCEDURE [dbo].[GetProductPricesForDate]
-- Add the parameters for the stored procedure here
@Date datetime,
@ProductId uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
ph.[DateTime], p.Name AS 'ProductName', ph.UnitPrice, rp.RawProductId, r.LogoFileName AS 'ShopLogoFileName', r.ShopId, r.Name as 'ShopName'
FROM
Shops r
INNER JOIN
RawProducts rp
ON
rp.[ShopId]=r.ShopId
INNER JOIN
Products p
ON
p.[ProductId] = rp.ProductId
INNER JOIN
RawProductPriceHistory ph
ON
ph.[RawProductId] = rp.RawProductId
INNER JOIN
(SELECT RawProductId,MAX([DateTime]) as maxdate
FROM
RawProductPriceHistory
WHERE
[DateTime]<=@Date
GROUP BY
RawProductId
)temp
ON
ph.RawProductId=temp.RawProductId
AND
ph.[DateTime]=temp.maxdate
WHERE
p.ProductId = @ProductId
GROUP BY
ph.[DateTime], p.Name, r.Name, ph.UnitPrice, r.ShopId, rp.RawProductId, r.LogoFileName
ORDER BY
ph.UnitPrice DESC
END
If anyone can help me extend the query I'd be really grateful!
thanks in advance,
dan
I am confused with this topic extentded ssis.What i am diong here is creating a pipeline component and i am trying to use this component and transfer data.
What is happening here is that i create it but i dont find it when i try to include this(component) in the toolbox ->Choose items.I find my project with in tools->Attach process.
Is this a problem with anyone too....
please help.
Hi,
View 5 Replies View RelatedHi, I am trying to write a stored proc that would allow word proximity. I would like to define the proximity of the word so that I could ask for a word1 within 10 of word2.
I am wondering if I could get some suggestions on how to go about writing this? Should I put this in a stored proc? Should I just cursor through each row and then cursor through each word after word1?
I hope that this is clear... Thanks in advance.
Has anyone investigated extending any of the SSIS Container classes?
I have been looking into it because we'd like to add a set of standard logging calls on events, standard startup procedures, etc. on any package that we execute.
I've been looking into the Sequence Container, For-Each, etc. They are all sealed classes. I'm not sure why MS has sealed them.
We're currently thinking of implementing our own version of the Sequence Container -- we'd really like to be able to extend the functionality of a standard container class, but we don't want to have to implement the actual container class itself.
Any insight appreciated.
Hi,
I need to write an add-in, that when installed it will add a virtual folder to the project tree (in which I can later add other stuff).
The problem is even more complicated since the project I want to extend is an SSIS project (SQL Server Integration Services), and it doesn't let you customize the project structure even from the project explorer itself (can't add folders).
I appreciate any help on this.
Thanks.
Hi All
We have requirement in Reports. When ever report is sent to file share using Reporting Services File Share extension than details of that file should be saved in database.
How can we achieve this functionality? What is the best possible way of achieving this?
Please let me know the solution.
Thanks in advance
Rehan Mustafa Khan
HCL Technologies
Noida,India
Hi,
I've had requests for a version of my product ViEmu, a VS.NET 2003-VS2005 add-in, which will work within SQL Server 2005 Management Studio. I had a look at the Mgmt Studio, and it seems to be a version of the VS2005 environment customized for the product. There is a registry hive along the line of the one in Visual Studio, but located in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsShell.
I looked for any hint online, but it seems largely undocumented.
I tried plugging my software by writing the right 'Packages' and 'AutoloadPackages' keys, and running "SqlWb /setup", which seems to do something. Nothing happened afterwards, anyway, the DLL doesn't seem to be loaded into the process. I tried using the '/log' option, but an error message comes up (which doesn't describe the /setup option either).
Is there a way to load a VS package into SQL Server 2005 Mgmt Studio? Is this a problem with the PLK? Is it unsupported? Has anybody been successful in such a feat? Would it be ok if I hack some way, no matter how obscure? Is this deliberately turned off, as in the Express editions?
Best regards and thanks in advance,
- J
----------------------------------------------------
ViEmu - vi/vim emulation for Visual Studio
Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.
Anyone out there have any thoughts on how this might be accomplished?
Thanks!
Travis
Hi,
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
Thanks,
Kim
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
I made a point type by C# for deploying in SQL2005.
It builds successfully every time.
When I deploy, it gives the following error:
"Error: Type "SqlServerProject1.TypePoint" is marked for native serialization, but field "x" of type "SqlServerProject1.TypePoint" is not valid for native serialization." although x variable is value type decimal.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered=true)]
public struct TypePoint : INullable
{
private bool m_Null;
private Decimal x;
private Decimal y;
public override string ToString()
{
// Replace the following code with your code
if (IsNull) // added part
{
return "null";
}
else
{
return x + ":" + y;
} // end of this part
// return "";
}
public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}
public static TypePoint Null
{
get
{
TypePoint h = new TypePoint();
h.m_Null = true;
return h;
}
}
public static TypePoint Parse(SqlString s) // Most Important Part
{
if (s.IsNull)
{
return Null;
}
else // added part
{
TypePoint p = new TypePoint();
string str = s.ToString();
string[] xy = str.Split(':');
p.x = Decimal.Parse(xy[0]);
p.y = Decimal.Parse(xy[1]);
return p;
}
}
public Decimal X
{
get
{
return x;
}
set
{
x = value;
}
}
public Decimal Y
{
get
{
return y;
}
set
{
y = value;
}
} // end of added part
// This is a place-holder method
public string Method1()
{
//Insert method code here
return "Hello";
}
// This is a place-holder static method
public static SqlString Method2()
{
//Insert method code here
return new SqlString("Hello");
}
// This is a place-holder field member
public int var1;
}
Is it possible to do a native BCP from 7.0 to 6.5? I'm having all sorts of data issues when I try.
Would a character bcp work (albeit slowly) instead?
Gurus,
I have a job that contains a step to run a DTS task (DTSRUN /S.....etc.) via a batch file.
I can run either the DTSRUN command line, or the batch file that contains it just fine,
but when I try to run the job, it reports success but does not actually do the work.
I dont get an error message. I am using the sa account to run the DTS task from the command line.
I know this must be a permissions issue. As I am working at a Defense contractor, I cannot get a
domain admin account to run SQL Agent.
Any ideas where to look?
Kevin
Hi,
How can I tell whether I have SQL Server 7 Components on my computer?
Thanks,
Denise
Hello.
Does anybody knowk if it's necessary to upgrade Sql Native Client to conect to a Server when you has upgrade it to Sql 2005 SP2? That is to say, can you conect to a Sql server 2005 SP1 and to a Sql Server 2005 SP2 from a client with the same Sql Native client or you have also to upgrade it?
Thanks.
We have a VB6 app that we're trying to connect to an SQL2005 server.
We're able to connect from dev machines that have SQLExpress, or SQL 2005 tools installed, but we cannot connect from other machines. We've installed sqlncli.msi from the MS site, but this still doesn't work. When we try to login, we get an error that reads "0[Microsoft][SQL Native Client]Fractional truncation" when we try writing to a smalldatetime field. This problem does not happen on the dev machines.
Connection string:
connectiontype = cnnSQLServer
database name = name
pasword = password
userid = uid
servername = servername
driver = {SQL Native Client}
Driver version on the dev machines is: 2005.90.3042.00
Driver version on the roll out machines is: 2005.90.1399.00
What are we doing wrong?
Zeke.
Hi
I need to browse SQL servers on local and network computers in a native C++ program (MFC / ADO).
I developped a .NET module based on SqlDataSourceEnumerator (works fine), but I cannot use it in my program (CLR is not allowed).
-> Is it possible to use SqlDataSourceEnumerator in a native C++ program (=without CLR) ?
Can any one give me brief useful explanations or guide me to a usefulresource of what Development Tools component and Profiler are?Is there anything I should be aware of if I want to install ClientConnectivity component (don't want to overwrite anything)?Thank you in advance.
View 1 Replies View RelatedI am not sure why when the packages are run, the components dissapear. The only thing I see is the result in the output.
So there is no visual on the tabs.
Thanks
Hi, help please!!
View 23 Replies View RelatedI am just a novice, and am curious.I
I was trying subscription, but an error occured: "Microsoft Server Management Studio is un able to accessreplication components because replication is not installed on this instance.
I went to change, located my setup exe, installed. but at the end, I get this message: No selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine......
I downloaded the Express edition via MSDN download.
May somebody please help? What wrong did I do? What did I miss to do?
I have a question about client components. I read someplace where if sql client components are preinstalled
(if you delete sql express-client components cannot install, even if they are preinstalled
I have sql express default installation from vs 2008 pro
I have not installed sql express (client components, database components
I wish to install cd standard edition on my computer for reporting services
Also wanted to add enterprise server so I need report manager
office 2007 installed by myself. I came across this article kb909967.
Is there anyway to check make sure I can install client components installs on standard edition
I also read kb 964164 installing sql on vs 2008 vista ultimate Thanks
Hi again, All!!
I'm having a problem with problems with some custom components I built. I have a custom Connection Manager and Data Flow Source that I have built.
My local PC is 64-bit, but I followed the MS instructions on building and deploying custom components. They run fine inside of VS2005, as long as I have 'Run64BitRuntime" set to 'False'. When I try to run packages using the custom components in 64-bit mode, I get:
Error: 0xC0014005 at : The connection type "<MyConnectionManagerType>" specified for connection manager "PRI" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
I suspect I need to either change the way I build or deploy the custom components. Can someone shed some light on this?
Thanks,
Frank
Hi
I have installed SQL Server 2005 but Integration Services is just not there. I have uninstalled and reinstalled several times and in the process uninstalled an old version of Visual Studio.NET (manually because it wouldn't uninstall automatically) and Visual Web Developer, just in case they were interfering with Integration Services. No luck, in fact it got worse, now Analysis Services also does not work because it can't find devenv.exe!
I have installed exactly this same software on another clean PC (ie. no previous Visual Studio variants on it) and it installs perfectly.
There seems to be some kind of clash between old versions of Visual Studio and SQL Server 2005 that prevents SQL Server from properly installing products like Integration Services that use the Visual Studio interface (ie. devenv.exe).
I now can't even reinstall the old version of Visual Studio.NET, and devenv.exe is nowhere to be found on my PC although it is referenced throughout the registry.
Has anyone got any suggestions, all I am after if a full and clean install of SQL Server 2005 so I can develop against it using Visual Web Developer.
Thanks