i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.
in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.
has anybody try to do this ?
is there any way to get the row number other then this way?
I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package.
Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message?
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 am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the following with indexes, RI, ect. :CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL , [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]GO CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]GO CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]GO ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ( [CustomerID] ) REFERENCES [dbo].[Customers] ( [CustomerID] ), CONSTRAINT [FK_Orders_Employees] FOREIGN KEY ( [EmployeeID] ) REFERENCES [dbo].[Employees] ( [EmployeeID] ), CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY ( [ShipVia] ) REFERENCES [dbo].[Shippers] ( [ShipperID] )GO
I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.
I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:
Now it will compile. But when I run it I get the error:
"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"
Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?
-Jamie
P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!
I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.
my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.
My vb .net app creates programatically a package. I mean, you can provide name, description, number of OLEDB/ADO connections, variables and so on, where it's stored, from a WinForm.
Up to the moment, process only creates boxes separately wihtout links.
My concern is after this successful cycle I only can open that DTSX and see its components but executing button is disabled (only possible with DTEXECUI.exe) from BIDS.
Only when I create a project (DTSPROJ) and then I put that same .DTSX inside I am be able to execute it.
Well, up to here all of this is very good. Things were created this way for some reason.
My question now is how do I from my application in order to create dtsproj and dtsproj.user (I don't know how many files are needed) and be able to create a package and its "father project". My idea is that the user can choose if create a standalone DTSX or a full project.
I have to create a DSN for SQL-Server. Therefor I use the function "SQLConfigDataSource" from "ODBCCP32.DLL". But the problem is that the SQL-Server does not use the Standard-Portnumber 1433. I've tried to make the DSN with "SERVERNAME xxxx" where xxxx is the Portnumber, sometimes it works, but often it does not. When it does not work, then the Checkbox in the ODBC-Configuration-Tool is checked, so the connection is searching for the standard-port. How can I tell the system that it shall NOT use dynamic configuration?
Just curious if there are any tutorials/help files/forums on using this and other Konesans control Flow and data flow task? most import the flow watcher task.
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.
Is this possible in SSIS?
The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.
Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.
If I create an Active Script like this : function Main() Dim fso Dim a Set fso = CreateObject("Scripting.FileSystemObject") Set a = fso.CreateTextFile("c:est.txt") a.WriteLine("This is a test") a.Close end Function
I got an error ErrorCOde: 0 ErrorSource : VBScriptENgineError Error Description : ActiveX Component can't create object : CreateObject
Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns. I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer). Are there any good examples for creating output columns dynamically based on the input buffer? Should I just give up on on the transform and create a custom source component instead?
I am trying to write a stored procedure to be used for custompaging and I get error with the below SP. "Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'." What am I doing wrong? CREATE PROCEDURE dbo.GetDealerSP (@startRowIndex int,@maximumRows int)AsSELECT installersemaid,dealerid,[name],address1,address2,city,[state],zip,phone,faxFrom(SELECT installersemaid,dealerid,[name],address1,address2,city,[state],zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)AS RowbankFROM dealerenrollment)as DealerWithRowNumbersWHERE Rowbank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)Go
I understand that if using this function with "nothing" between parentheses, then the running row total never resets.
However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.
What is the correct syntax for accomplishing this?
We have a requirement of creating a report which has one data set And the same data set is being used accross different tables in the report. The tables differ by the type of grouping applied on them.
My situation is 1. The table has two groups applied a) table level group is on DIVISION b) detail grouping is done based on two columns: SYSTEM and NAME 2. When i use the expression rownumber("DIVISION") or rownumber("SYSTEM_NAME"), it does not return a proper sequence
I need the row numbers to be generated based on the grouping on (SYSTEM and NAME) for each DIVISON
I got problem with using custom paging in sql 2005 SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[searchperson_view_general] @Search nvarchar(2000) ,@OrderBy nvarchar (2000) ,@PageSize int ,@PageIndex int AS DECLARE @PageLowerBound int DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
--Default order by to first column IF (@OrderBy is null or LEN(@OrderBy) < 1) BEGIN SET @OrderBy = 'p.[person_id]' END
-- SQL Server 2005 Paging declare @SQL as nvarchar(4000) SET @SQL = 'WITH PageIndex AS (' SET @SQL = @SQL + ' SELECT distinct' IF @PageSize > 0 BEGIN SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound) END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex ' SET @SQL = @SQL + ', p.[person_id]' SET @SQL = @SQL + ', p.[userType_id]' SET @SQL = @SQL + ', p.[fullName]' SET @SQL = @SQL + ', p.[gender_nm]' SET @SQL = @SQL + ', p.[dateOfBirth] ' SET @SQL = @SQL + ', p.[positionTitle]' SET @SQL = @SQL + ' FROM dbo.[person_view] p '
IF LEN(@Search) > 0 BEGIN SET @SQL = @SQL + @Search END SET @SQL = @SQL + ' ) SELECT distinct' SET @SQL = @SQL + ' p.person_id' SET @SQL = @SQL + ', p.userType_id' SET @SQL = @SQL + ', p.fullName' SET @SQL = @SQL + ', p.gender_nm' SET @SQL = @SQL + ', (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth] ' SET @SQL = @SQL + ', p.positionTitle' SET @SQL = @SQL + ' FROM PageIndex p ' SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @PageSize > 0 BEGIN SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound) END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy exec sp_executesql @SQL
I checked my store procedure with parameters exec [hr2b_searchperson_view_general_load] 'LEFT OUTER JOIN qualification ON p.person_id = qualification.person_id WHERE qualification.institutionName like N''%ABC%''' ,' p.person_id asc ', 25 , 1
This is a actual query show :
WITH PageIndex AS ( SELECT distinct TOP 49 ROW_NUMBER() OVER (ORDER BY p.person_id asc ) as RowIndex , p.[person_id] , p.[userType_id] , p.[fullName] , p.[gender_nm] , p.[dateOfBirth] , p.[positionTitle] FROM person_view p LEFT OUTER JOIN qualification ON p.person_id = qualification.person_id WHERE qualification.institutionName like N'%ABC%' ) SELECT distinct p.person_id , p.userType_id , p.fullName , p.gender_nm , (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth] , p.positionTitle FROM PageIndex p WHERE RowIndex > 25 AND RowIndex <= 49 ORDER BY p.person_id asc
If I used this query without using DISTINCT it will return extactly number of records which I expected but It is duplicated. Then I tried to use DISTINCT in this query number of records return is less than 25 records . Because it was duplicated records when I used LEFT OUTER JOIN.But my query will be able to use more LEFT OUTER JOIN than this query. Please help me get exactly 25 records?
This is my tables person_view(person_id, fullname, userType_id, gender_nm, dateOfBirth, positionTitle)
I have a detailed report ..with summary lines and detailed lines (drill down).
I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows
when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).
I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.
How do I modify RowNumber(Nothing) to exclude counting the detailed rows....???
Hi, I create a matrix table with wizard. I want to write rownumber() as a new column near my column. But when =Rownumber(nothing) it returns the value of the record in the dataset. If the data in the 56.row then =Rownumber(nothing) =56 But in the preview of table it is in 1.row.
I have a requirement to find the rowmumbers based on a group. I know there is Rownumber function in sql. How can I implement the same in DAX. PFA screenshot. Rownumber is the calculated field i want based on the id and name column group.
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
