Re: SSIS - OLE DB Command - how to retrieve query results ???

Hi Barry,
Thank you for your detailed description.

I performed a similar test with SQL Server due to no informix database at
my side according to your process, and I managed to execute the task.
My test was as following:
Data Source
================================================
Data Source: <MySQLServer 2000 Instance>.Northwind
Transferring Table: Shipper
Record Table: transfer
create table transfer
(
RecordID int primary key IDENTITY(1,1),
ShipperID INT,
CompanyName nvarchar(50),
Phone nvarchar(50)
)

Data Destination
==================================================
Data Destination: <My SQL Server 2005 Instance>.TestDB
Stored Procedure of the destination: proc_test
CREATE PROCEDURE proc_test
(
@shipperId int,
@companyName nvarchar(50),
@phone nvarchar(50)
)
AS
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Shipper]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Shipper](
[ShiperID] [int] NOT NULL,
[CompanyName] [nvarchar](50) NOT NULL,
[Phone] [nvarchar](50) NULL,
CONSTRAINT [PK_Shipper] PRIMARY KEY CLUSTERED
(
[ShiperID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
INSERT INTO Shipper VALUES(@shipperId,@companyName,@phone)

Data Flow
===============================================
Same as yours:
Data Reader Source--------Multicast-------------------OLE DB Command

|------------------------OLE DB Destination

OLE DB Command:
exec proc_test ?,?,?

Connections:
Data Reader Source <------> mysqlinstance2000.Northwind (ADO.NET Connection)
OLE DB Destination <------> mysqlinstance2000.Northwind1 (OLE DB Connection
: OLE DB Provider for SQL Server)
OLE DB Command <-----> mysqlinstance2005.TestDB (OLE DB Connection : OLE
DB Provider for SQL Server)

Mappings:
OLE DB Command:
ShipperID <----> @shipperId
CompanyName <----> @companyname
Phone <----> @phone

OLE DB Destination:
ShipperID <---->ShipperID
CompanyName <----> CompanyName
Phone <----> Phone

After setting up above connections and relations, I switch to the Control
Flow, right click the Data Flow Task, and click Execute Task,
then I managed to execute the task. All the rows are imported into the
destination table ( Shipper ) and the monitor table ( transfer ).

From your previous reply, I noticed that you said "I finally realized that
the approach I was trying to use won't work because
of SSIS batch approach to record processing.". Based on my test, I didn't
encounter this issue.
Could you please lighten me more on your meaning? To be honest, I couldn't
understand that clearly.
If you encountered some error when you executed the task, you may mail me
(changliw@xxxxxxxxxxxxx) a screenshot for better understanding.

Hope this helpful.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================




.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Is ADO.NET OleDb .NET Data Provider a COM object?
    ... Okay, okay... ... OLE DB is a one-size-fits-all set of providers that can access anything from a relational database to a tuna salad. ... in order to access SQL Server we used OLE DB to access ODBC or SQL Server. ... In a similar way, just because you can connect to MySQL, Paradox, Oracle or FarkleStar databases from an OLE DB or other OSFA provider does not mean your code can interface with these other back-ends. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: SqlServer 2005 Transfer Flame
    ... database between the two servers. ... I do understand your concerns with SQL Server 2005. ... Drag a OLE DB Destination to the panel; ...
    (microsoft.public.sqlserver.dts)
  • Re: 3 Simple Security SQL Statements
    ... Kalen Delaney, SQL Server MVP ... window and execute. ... Builds a list of text commands. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)