Getting The Value Of A Column From Input
Jul 11, 2007
I am trying to get the value of a column in the Input0_ProcessInputRow function and I have the column name.
There has to be an object in the pipeline that will allow me to do this right?
Something like "ComponentMetaData.InputCollection(0).InputColumnCollection([COLUMN NAME])"
Can someone recommend how I would do this. I have tried a few things, but can't seem to get to the Value or ToString of the column I want in this row.
Thanks
View 3 Replies
ADVERTISEMENT
Jan 11, 2008
Hi!
I have a "little" problem with nested case model:
-- "normal" database:
DROP TABLE [unitInfo] ;
GO
CREATE TABLE unitInfo (
unitID INT PRIMARY KEY
, beginDate SMALLDATETIME
, area VARCHAR(10)
, partSize INT
, y2predict MONEY
) ;
go
INSERT INTO unitInfo
VALUES (1, '2007-02-01', 'home', 42, 10.0) ;
INSERT INTO unitInfo
VALUES (2, '2007-03-05', 'home', 43, 11.0) ;
INSERT INTO unitInfo
VALUES (3, '2007-02-02', 'office', 11, 11.4) ;
INSERT INTO unitInfo
VALUES (4, '2007-02-01', 'office', 10, 33.6) ;
INSERT INTO unitInfo
VALUES (5, '2007-02-01', 'office', 42, 44.1) ;
CREATE TABLE unitLog (
id INT IDENTITY(1, 1)
PRIMARY KEY
, logtime SMALLDATETIME
, -- combination of logtime/unitID is unique
unitID INT
, -- "FK" on unitInfo
m1 FLOAT
, m2 FLOAT
)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 2, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 3, 63.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 4, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-03', 1, 423.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-04', 1, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-05', 2, 43.0, 441.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 2, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 3, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-07', 1, 4.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 3.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-09', 2, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-10', 3, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 4, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 5, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-12', 2, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-13', 4, 413.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 4, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-20', 1, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-22', 1, 43.0, 414.0)
-- SSAS:
CREATE MINING STRUCTURE NestedStructure
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure
ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
/* version 1*/
insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,
'Shape {select * FROM mydb.dbo.unitInfo }
Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }
Relate unitID to unitID ) as logdata ')
Parsing the query ...
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
Parsing complete
Where is the error?
/*version 2*/
CREATE MINING STRUCTURE NestedStructure1
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure1
ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }
Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }
Relate unitID to unitID ) as logdata
Parsing the query ...
Error (Data mining):
INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.
Parsing complete
Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.
What is wrong here...?
View 6 Replies
View Related
Feb 27, 2008
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.
Beli
View 4 Replies
View Related
Aug 7, 2007
I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
So how do I go about doing this the smart way?
thanks in advance!
View 4 Replies
View Related
Feb 23, 2006
Hi there,
I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.
For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -
update tableA
set columnA = ?,
column B = ?
where columnC = ?
AND (columnA != ? OR columnB != ?)
I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.
I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -
declare @ValueA varchar(50),
@ValueB varchar(50),
@ValueC varchar(50)
select @ValueA = ?,
@ValueB = ?,
@ValueC = ?,
update tableA
set columnA = @ValueA ,
column B = @ValueB
where columnC = @ValueC
AND (columnA != @ValueA OR columnB != @ValueB)
Any suggestions?
View 3 Replies
View Related
May 19, 2014
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)
[code].....
View 1 Replies
View Related
Jul 3, 2002
Hi,
I have a table with column size char(100), But As soon as I enter 60 charcters I get an error 'Maximum limit violation'
Any help please
Thanks
Raja Jayaseelan
View 1 Replies
View Related
Jul 22, 2007
Hi,
I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.
How do i use this inside the script?
cherriesh
View 3 Replies
View Related
Feb 6, 2007
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
View 6 Replies
View Related
Feb 5, 2007
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
HELP!!
View 5 Replies
View Related
Sep 10, 2007
Hi
I'm developing a Destination Component. I need to create the input columns in my ReinitializeMetadata() method. This is a small code sample of the method:
Code Snippet
IDTSInput90 input = ComponentMetaData.InputCollection[0];
input.InputColumnCollection.RemoveAll();
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach( IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection ) {
SetUsageType( input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY );
}
This piece of code works fine, but now i want to use a UI Type DataFlowDestinationUI with a Destination mapping page added by base.AddDestinationMappingsPageToForm( form, "" );.
When I open the mapping page - ReinitializeMetadata() is called BEFORE opening this page - this error is shown:
Code Snippet
===================================
Exception from HRESULT: 0xC0047041 (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSVirtualInputColumn90.get_UsageType()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowDestinationUI.mappingPage_MapColumns(Object sender, MapColumnsEventArgs args)
I think it's because the mapping page calls GetVirtualInput() itself. Do you know a solution for this problem?
Thank you very much.
Manuel Bauer
View 3 Replies
View Related
May 19, 2015
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
View 0 Replies
View Related
Jan 22, 2008
Hi All,
I want to run a loop for all the input columns in the script component. My requirement is, I have nearly 50 columns in the input columns list. For each row and for each column I need to do some operation. How Can I run a loop for each column. Please note in the script component I need to get the column names in the middle for some operations. Please see below.
Process Each Input Row
For each column in Input column list
....
....
If column.Name Starts with "Test" then set NULL to the column value
....
....
End Loop
End Loop for each input row
Can anybody tell me how can I do that.
Regards
Venkat.
View 5 Replies
View Related
Jun 1, 2007
Is there a maximum width for fixed width column?
I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.
Any ideas?
Jim Work
View 5 Replies
View Related
Jan 4, 2006
Using MSDE and OSQL
I begin with:
C:OSQL -D VID -i C:accepted.sql -o C:Resultsaccepted.txt -n -w500 -Usa
That gives me data such as this:
363 Cynthia KY 36
542 Charlene NC 3
594 Amanda NJ 9
592 Robert NJ 54
Then this command to create a table
CREATE TABLE accepted
(
Customer_idnvarchar(50)NULL,
Cust_Namenvarchar(50)NULL,
Cust_Statenvarchar(50)NULL,
Cust_Countnvarchar(50)NULL
)
GO
I've created this BCP format file:
8.0
4
1 SQLCHAR 0 50 "/t" 1 Customer_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "/t" 2 Cust_Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "/t" 3 Cust_State SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "/r/n"4 Cust_Count SQL_Latin1_General_CP1_CI_AS
Table is created. I can SELECT * FROM accepted and see my column names.
Then I try to BCP into the table using:
C:>BCP sales..east in C:Resultsaccepted.txt -t -f C:cpformataccepted.fmt -Usa -Ppwd
I get this error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
and so on......
In the .fmt file I've tried "", " ", " " and everthing I could think of as a delimiter. Still no luck. I've tried almost every switch available to both OSQL and BCP.
The data I am trying to BCP is a SQL result so I don't think any special delimiters are placed. I've tried not using the .fmt file and using the prompts but still no luck. Data is CAST in the query and doesn't excede 45 characters.
Hope I've explained my problem well enough.
-Deana
View 11 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Oct 5, 2006
Hi,I need to input Chinese character into the table of the database. I did try to install/run both Chinese/English version of Visual Studio into Chinese/English version of Server 2003 but it still didn't work.Please help !stephen
View 3 Replies
View Related
Feb 4, 2008
I use txtbox.Text = cmd.ExecuteReader() but it doesn't work. How can I fix it ?
View 4 Replies
View Related
Feb 25, 2008
I have two textboxes on the page...shipdate and duedate. When the page loads, shipdate has today's date loaded and duedate has a date that's 28 days later than today. When I change the dates and submit it's not updating in the database instead I'm getting the two dates in pageload. What am I doing wrong?Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ShipDateTxt.Text = Today()
DueDateTxt.Text = DateAdd(DateInterval.Day, 28, Today())
End SubProtected Sub LoanRequest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoanRequest.Click
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateloanerInfo".Parameters.AddWithValue("@requestorid", Integer.Parse(Request.QueryString("requestorid")))
.Parameters.AddWithValue("@shipdate", ShipDateTxt.Text).Parameters.AddWithValue("@duedate", DueDateTxt.Text)
End With
Here's updateloanerinfo stored procedure:
@requestorid int,@shipdate datetime,@duedate datetime
AS update LibraryRequest
set [shipdate] = @shipdate,[duedate] = @duedate
Where requestorid=@requestorid
View 3 Replies
View Related
Oct 13, 1999
Can I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
View 1 Replies
View Related
Aug 29, 2000
I am just learning SQL Server and I am stuck with a few things. I was wondering if you can define input masks for columns in the database in SQL Server. I have used this feature many times in Access and on the front end in Oracle Forms. Is there such a thing in SQL Server?? Is it just called something different.....? I will be trying to format telephone numbers and dates. Thank you for you time.
Nadine Sinclair
View 1 Replies
View Related
Aug 14, 2002
Hi all,
Can anyone tell me about how to prompt a user to type when running a sql statement through query analyzer.
thanks
Jay
View 1 Replies
View Related
Apr 18, 2008
create function quantprice
( @itemnmbr varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
(Select distinct t.itemnmbr,t.totalprice as totalcost
from
(
select distinct
vs.itemnmbr, sum(vs.totalsumprce) as totalprice
from vwquantityprice vs
Where vs.itemnmbr = @itemnmbr and (vs.docdate between @startdate and @enddate)
group by vs.itemnmbr
) as t
)
select * from quantityprice('06-5840','4/1/2007','4/1/2008')
well, guys, i have this function and obviously i will get one row from this in output...with that particular itemnumber but i want multi-itemnmbrs some times in output...some times 2..soemtimes 3...
example,
select * from quantityprice('06-5840,ab-4581,0a-1458,45-0945','4/1/2007','4/1/2008')
can you tell me what condition it will come to get this output..
like in where itemnmbr in('06-5840,ab-4581,0a-1458,45-0945')
and in starting input v ariable @itemnmbr..
create function quantprice
( @itemnmbr varchar(50)..
don't know what condition it will come and where i have to put condition in where clause or in input variable..
any help would be appreciated..thanks a lot!! guys plz reply.
View 17 Replies
View Related
Apr 23, 2008
I have created a query that has a "When" and an "And" function.
I know need to make the query run on user input, i want it to ask for the date, then name to run the query
View 2 Replies
View Related
Nov 1, 2006
Afternoon...
I have a database for a pretend dvd hire company and need to create a query that uses user input as part of the query.
Ie, select * from dvd where actor = 'Tom Cruise';
The 'Tom Cruise' part of the query needs to be user input every time the query is ran... Can you help?
Many thanks in advance!?
View 14 Replies
View Related
May 8, 2007
Hi there,
I'll be going through a training couse for SQL Server next month. The class I'm signed up for is M2780: Maintaining a Microsoft® SQL Server™ 2005. My company uses SQL Server 2000 now and unless I give good reasons to upgrade to 2005, we won't be upgrading. I have very little DBA experience, but since I was told SQL Server is my responsibility, I figured it would make sense to go through some type of training for it. My questions are: Is there enough the same between the two that it makes sense for me to take this class? Are there good business reasons I can give for upgrading? If we don't upgrade is it worth taking a class for 2000 vs 2005?
Thanks for any input.
Marcie
View 2 Replies
View Related
Oct 31, 2007
Need help.
I’m trying to pass inputs from one stored proc to another but I’m having problems in passing them.
First proc (input_passing) should pass the following inputs – FirstName, LastNmae and RecordID to the second proc (input_receive) – then the second proc will diplay the outcome – YES/NO if there is a match.
------------------------------------------------------------------------------------------
First proc (input_passing)
-------------------------------------------------------------------------------------------------------
DECLARE
@RecordIDVARCHAR(11),
@FirstNameVARCHAR(60),
@LastNameVARCHAR(60)
--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName
FROM
tbInputs
OPEN tbInputs
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
--GET RECORD
SET @RecordID = RecordID from dbo.tbInputs)
END
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
END
CLOSE tbInputs
DEALLOCATE tbInputs
exec input_receive
----------------------------------------------------------------------------------------------------------------- Second proc. (input_receive)
CREATE PROCEDURE input_receive
@RecordIDVARCHAR(11) = NULL,
@FirstNameVARCHAR(50) = NULL,
@LastNameVARCHAR(50) = NULL,
@RecordMatchBIT OUTPUT,
@MatchedOnVARCHAR(400) OUTPUT
AS
-- Declarations
DECLARE @CountINT
DECLARE@FoundBIT
DECLARE @AlertIDDECIMAL(18,0)
DECLARE@AlertCreateDateDATETIME
-- END Declarations
-- ******* Initializations **********
SET @Count = 0
SET @Found = 0
-- DEFAULT to No File Match
SET RecordMatch = 0
SET @MatchedOn = 'NO FOUND'
-- ******* END Initializations **********
-- First check to see if the RecordID Matches
IF( @RecordID IS NOT NULL AND @RecordID <> '' )
BEGIN
IF( CAST( REPLACE(@RecordID, '-', '') AS DECIMAL(18,0)) > 0 )
BEGIN
SELECT @Count = COUNT(*)
FROM tbAlert
WHERE REPLACE(RecordID, '-', '') = REPLACE(@RecordID, '-', '')
IF( @Count > 0 )
BEGIN
SET @RecordMatch = 1
SET @MatchedOn = 'FOUND : RecordID'
RETURN
END
END
END
Josephine
View 1 Replies
View Related
Sep 21, 2006
Hi ,
Im trying to build a package that will copy data from excel to SQL
in a program
unfortunately , when I open the package xml file and I drill into the
oledb destination I see that I have no available input columns
what could be the problem ?
thanks ahead
Eran
p.s
the script:
Dim p As Package = New Package()
Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = CType(e, TaskHost)
thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Create excel connection MANAGER
Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")
excelCon.Name = "ExcelSourceConn"
excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e: ry ry ry.XLS;Extended Properties=""Excel 8.0;HDR=YES"""
' Create sqldev connection Manager
Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")
sqlCon.Name = "sqldevConn"
sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEVSQLDEV"
''create source component
Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
excelSource.Name = "ExcelSource"
excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"
Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()
excelInstance.ProvideComponentProperties()
excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))
excelInstance.SetComponentProperty("AccessMode", 0)
excelInstance.SetComponentProperty("OpenRowset", "business_codes$")
excelCon.AcquireConnection(Nothing)
'excelInstance.ReinitializeMetaData()
excelInstance.ReleaseConnections()
Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
sqldev.Name = "sqldev"
sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()
sqldevInstance.ProvideComponentProperties()
sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))
sqldevInstance.SetComponentProperty("AccessMode", 0)
sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")
sqldevInstance.AcquireConnections(Nothing)
sqldevInstance.ReinitializeMetaData()
sqldevInstance.ReleaseConnections()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))
MsgBox(excelSource.OutputCollection.Count)
'For Each input As IDTSInput90 In sqldev.InputCollection
' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' ' Call the SetUsageType method of the design time instance of the component.
' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Next
'Next
Dim app As Application = New Application()
app.SaveToXml("c:myXMLPackage.dtsx", p, Nothing)
View 2 Replies
View Related
Aug 8, 2007
Hi
A script component receives some input. But I just can't get at the first row??
Basically, if i use the NextRow method in the in the Do statement, then it advances the row collection to the second row before it gets into the code inside the loop?? BUT, if I use the EndOfRowset property to define my loop then I get an error:
[PipelineBuffer has encountered an invalid row index value]
I'm guessing this means...I have to call NextRow before i access the data in the collection? But thats retarted because then I miss the first row?? what? What am I missing??
This is the code which works but I miss the first row:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Row.NextRow()
strConcept = Row.concept
updateDb(strConcept)
Loop
End Sub
This is the code which throws the invalid row index error:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Not Row.EndOfRowSet()
strConcept = Row.concept
updateDb(strConcept)
Row.NextRow()
Loop
End Sub
I've put some try catches in there an the error happens on the line which calls Row.concept....?
Can anyone help, it must be something I'm messing up
thanks!!
andy
View 17 Replies
View Related
Sep 19, 2007
I need to validate my input rows. The row is valid if there exist some other input rows in the same table (I am importing data from excel and access). I'll give an example to make everything clear:
Input table boys has following columns:First_Name ,Surname and Date_of_birth.
Output table is Twin_Triple_More_Brothers. I would like to insert into this table only boys that surnames are equal and difference in date of birth is less then one day.
I was thinking about lookup component, but I cannot use it in that way (or I just do not know how).
Maybe someone has an idea how to do this?
Thanks for help.
View 6 Replies
View Related
Mar 17, 2006
If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?
If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?
View 8 Replies
View Related
Oct 16, 2006
I'm very new to SQL Server so please forgive me if this question is ridiculously simple. I have to upgrade the report engine from one that was used in a legacy VB6 app to a C#.net app. In doing so, I'm looking at assorted reports that came out of the old app. Here is the SQL code for one of them:
SELECT (LTRIM(STR(From_To,10,0)) + '-' + LTRIM(STR(From_To + 49,10,0))) AS Bonus_Earned, COUNT (Empl_ID) AS Men, round(SUM (SumDollars),2) AS Group_Earn, round((SUM (SumDollars) / COUNT (Empl_ID)),2) AS Calc0 FROM (SELECT CONVERT (int, round (SumDollars / 50, 2)) * 50 AS From_To, SumDollars, Empl_ID FROM (SELECT round(SUM (Actual_Hours_Dollars.Incentivedollars * Contract_History.Bonus_Pct / 100), 2) AS SumDollars, employees.Empl_ID FROM ((Employees INNER JOIN Actual_hours_dollars ON Employees.Empl_ID = Actual_Hours_Dollars.Empl_ID_R) INNER JOIN Contracts ON Actual_Hours_Dollars.Contract_ID = Contracts.Contract_No) INNER JOIN Contract_History ON Contracts.Contract_Idx = Contract_History.Contract_Idx_R where employees.empl_idx = (SELECT max(empl_idx) FROM Employees AS OuterEmployees WHERE OuterEmployees.empl_id = employees.empl_id AND outeremployees.Datex = (SELECT max(datex) FROM Employees AS InnerEmployees WHERE InnerEmployees.empl_id = employees.empl_id AND Inneremployees.disabled = 0 AND Inneremployees.Datex < '~EndDate~')) AND Contracts.Datex = (SELECT max(datex) FROM Contracts AS InnerContracts WHERE InnerContracts.contract_no = Contracts.contract_no AND InnerContracts.disabled = 0 and InnerContracts.deleted = 0 AND InnerContracts.Datex < '~EndDate~') AND Actual_hours_dollars.Datex >= '~StartDate~' AND Actual_Hours_Dollars.Datex < '~EndDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) >= '~StartDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) < '~EndDate~' and Actual_Hours_Dollars.IncentiveHours <> 0 GROUP BY employees.empl_ID) AS InnerRS1 GROUP BY ROUND (SumDollars * 2, -2) /2, SumDollars, Empl_ID) AS InnerRS2 GROUP BY From_To
I'm only including it for completeness. The key thing I'd like to draw your attention to are two variables that are clearly input parameters: ~StartDate~ and ~EndDate~.
My question is this: If I want to copy this code into SQL Query Analyzer and run it to see what kind of results I get back, what's the simplest way to define these two input parameters? I'm hoping you could just show me the syntax to define them above the SELECT statement.
Robert Werner
Vancouver, BC
View 4 Replies
View Related
Sep 15, 2006
So I have a person who is adamant in tell me that SQL Server does not run on windows XP.
Now, I have already done all the research on this (i.e. sql server 2000 product page / requirements) and know the answer, but they insist on asking the question, so here it is .....
'Will SQL Server run on Windows XP'
A simple YES or NO will suffice; however, if you want to explain the answer (if it requires one ;) ), please feel free.
View 5 Replies
View Related
