How does one retrieve the COUNT(*) value from the following SSIS Ole Db
Command query ??

SELECT COUNT(*) FROM wr_xfer_application WHERE xfer_char = ? AND xfer_nbr =
?

In the current case I need to determine if a record exists for xfer_char /
xfer_nbr ...

if it doesn't exist I need to direct the script to create a new
wr_xfer_applicaiton record.

Thanks in advance !!!

Barry
in Oregon

I think you may want to look at using a Lookup transform to do this, or use
a stored proc to wrap the check and insert in one go from the OLEDB Command


"frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote

Darren,

Thanks for the quick reply.

Are you saying that its not possible to retrieve the "wr_xfer_application"
COUNT(*) value from the SSIS Ole Db
Command query " SELECT COUNT(*) FROM wr_xfer_application WHERE xfer_char =
? AND xfer_nbr
= ? "

We are in the process of migrating data from our old informix system and
we're splitting one table on the informix table into 2 tables on the Sql
Query side. As a result we need to look at the xfer_char and xfer_nbr in
each record from to the incoming Informix table. I can create a small
stored proc that returns the count value but it seems kind of silly to
create a stored proc just to determine whether a record exists for a 'one
off' migration process. Also, I can envision wanting to query the db to
determine a value from time to time and then perform an action depending on
the return result. Having to write a stored proc for every data db check
seems to be a little over the top.

I'll spend a little time with the Lookup Transform but at 1st glance I'm not
sure how I can make it work for our purposes.

Thanks.

Barry in
Oregon


"Darren Green" <darren.green (AT) reply-to-newsgroup (DOT) sqldts.com> wrote

Hi Barry,
My understanding of your issue is that:
You wanted to create a new wr_xfer_application record if it didn't exist
by using script in SSIS.
You tried OLE DB Command, but it seemed not appropriate for your task.
If I have misunderstood, please let me know.

I am not sure if you want a parameterized query or constant query.
If you just want a constant query, you can use OLE DB Command like:
IF NOT EXISTS ( SELECT * FROM wr_xfer_application WHERE xfer_char = 'A1'
AND xfer_nbr= 'B1')
BEGIN
INSERT INTO wr_xfer_application VALUES(...)
END

If you want a parameterized query, I recommend that you use Execute SQL
Task at the Control Flow panel.
You can set the SQLStatement like:
IF NOT EXISTS ( SELECT * FROM wr_xfer_application WHERE xfer_char =? AND
xfer_nbr= ?)
BEGIN
INSERT INTO wr_xfer_application VALUES(...)
END

You can set the parameters according to the section "Using Excute SQL Task"
in this article:
SQL Server 2005 Integration Services, Part 1: Lessons from Project REAL
http://msdn.microsoft.com/library/de...us/dnsql90/htm
l/SQL05InSrREAL.asp

If you have any other questions or concerns, please feel free to let me
know. It's 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.
================================================== ====

Charles,

Many thanks for you prompt and helpful answer. Your time and effort are
VERY much appreciated.

I finally took the approach of creating a stored procedure that determined
whether or not the transfer_application record existed and if it did not
exist the procedure then created the appropriate record using paramater
values passed in from the OleDB Command control. Since this will be a 'one
time' migration of data, it doesn't really have to be fast or efficient
(though it creates 27,000 records in less than a minute)

I finally realized that the approach I was trying to use won't work because
of SSIS batch approach to record processing.

I appreciate your and the group's patience and help while I'm trying to
understand how SSIS approaches the various problems I'm tring to solve.

Sincerely,

Barry
in Oregon

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Dear Barry,
Thanks for your detail response.

I am interested at the parameters that you set.
For further research, could you please let me know:
1. What is the source of the parameters, manual input or a result set from
other query?
2. Could you describe your whole data transfer process for me including how
to set the parameters?

Sincerely,
Charles Wang
Microsoft Online Community Support

Charles,

I'll try my best to outline the basics of the process I used. Everything
'fell into place' once I 'bit the bullet' and wrote the necessary stored
procedure.


==============

The SSIS process follows roughly the following path

(01) [ Data Reader - informix table "xfer"]
[Multicast]
(02) [OleDb Command] (03) [OleDb Destination]

==================

(01) [Data Reader] connected via ODBC to our Informix xfer (transfer) table

(02) [OleDb Command] uses the following expression to call
migration_xfer_application_insert stored procedure

EXEC migration_xfer_application_insert ?, ?,?,?,?,?,?,?,?,?,?,?,?,?
(see stored proc code below)

The parameters come from the [Multicast] result set. I used the "Advanced
Editor for OleDb Command" to map the input Columns from the [Multicast]
control to the "migration_xfer_application_insert" stored proc parameters.
The [OleDb Command] control conveniently discovered and displayed stored
proc parameter names in the"Available Destination Columns" list of the
Advanced Editor "Control Mappings" tab. The auto discovery of the stored
proc parameter names made the mapping the input columns to the stored proc
parameters a quick & simple process.

(03) [OleDb destination] creates an Sql Server "transfer" table record for
each record from the Informix table using column data from the [Multicast]
result set.

CREATE PROCEDURE migration_xfer_application_insert
(
@xfer_col1 varchar(2) = NULL,
@xfer_col2 int = NULL,
@xfer_col3 varchar(3) = NULL,
@xfer_col4 varchar(3) = NULL,
@type_aaa bit = NULL,
@type_bbb bit = NULL,
@type_ccc bit = NULL,
@type_ddd bit = NULL,
@type_eee bit = NULL,
@type_fff bit = NULL,
@type_ggg bit = NULL,
@type_hhh bit = NULL
)
AS
BEGIN

DECLARE @ProcName varchar(128)
DECLARE @ErrorMsg varchar(4000)
DECLARE @LocalErrorNbr int

DECLARE @new_serial_id int
DECLARE @return_code int
DECLARE @return_msg varchar(4000)
DECLARE @current_userid varchar(20)

SET @ProcName = 'migration_xfer_application_insert => '
SET @ErrorMsg = ''
SET @LocalErrorNbr = 0

SET @return_code = -1

-----------------------------------------------------------
-- Validate Input Parameters --
-----------------------------------------------------------
IF(dbo.wrd_fn_string_is_null_or_blank(@xfer_col1) != 0)
BEGIN
SET @LocalErrorNbr = 99020
SET @ErrorMsg = RTRIM(@ErrorMsg) + '=> xfer_col1 parameter cannot be
blank or NULL.' + CHAR(13)
END

IF(ISNULL(@xfer_col2,0) !> 0)
BEGIN
SET @LocalErrorNbr = 99030
SET @ErrorMsg = RTRIM(@ErrorMsg) + '=> xfer_col2 parameter must be an
integer greater than 0.' + CHAR(13)
END

-----------------------------------------------------
-- >>> IF ERRORS FOUND <<< RETURN ERROR CODE & MSG --
-----------------------------------------------------
IF(@LocalErrorNbr > 0) GOTO exit_procedure

--=========================--
-- INSERT new table record --
--=========================--

BEGIN TRY
------------------------------------------------------------------------------------------------
-- DETERMINE IF A wr_xfer_application record already exists for
xfer_col1 / xfer_col1
------------------------------------------------------------------------------------------------
SET @iRecCount = 0

SELECT @iRecCount = COUNT(*)
FROM wr_xfer_application
WHERE xfer_col1 = @xfer_col1
AND xfer_col2 = @xfer_col2

IF(@iRecCount > 0) RETURN -1

--=============--
BEGIN Transaction
--=============--

EXECUTE @return_code = wrd_wr_xfer_app_insert
@xfer_col1,
@xfer_col2,
@xfer_col3,
@xfer_col4,
@type_aaa,
@type_bbb,
@type_ccc,
@type_ddd,
@type_eee,
@type_fff,
@type_ggg,
@type_hhh

--=====================================--
IF(@@TRANCOUNT > 0) COMMIT Transaction
--=====================================--

END TRY
BEGIN CATCH

SET @ErrorMsg = RTRIM(@ErrorMsg) + '=> TRY-CATCH ERROR: on database
record INSERT attempt.' + CHAR(13)
EXECUTE @LocalErrorNbr = rtn_try_catch_err_msg @return_msg OUTPUT
SET @ErrorMsg = RTRIM(@ErrorMsg) + RTRIM(ISNULL(@return_msg,''))

--=====================================--
IF(@@TRANCOUNT > 0)
WHILE (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANStype
END
--=====================================--

END CATCH

---------------
exit_procedure:
---------------
IF (@LocalErrorNbr=0)
BEGIN
SET @return_msg = RTRIM(@ProcName) + ' Insert Successful'
END
ELSE
BEGIN
SET @return_msg = RTRIM(@ProcName) + ' Insert Failed!' + CHAR(13) +
RTRIM(@ErrorMsg)
SET @new_serial_id = -1
END

RETURN @LocalErrorNbr

END


Hope this helps.

Barry
in Oregon

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

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 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 (AT) microsoft (DOT) com) 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.
================================================== ====

Charles,



Hummm, I may have mislead you early on. If so, please accept my
apologies.



The process that I outlined for you in my last e-mail >> was SUCCESSFUL <<
it created about 10,000 xfer_app and 25,000 xfer records. I'm very sorry if
I wasted your time, I realize your time is very valuable.



W.R.T. SSIS processing data in 'batches', I encountered the 'batch' issue
after deciding that I was going to have to write a stored procedure to use
the OleDb Command since I could not retrieve the count(*) results directly
from an sql "SELECT COUNT(*)" statement in the OleDb Command expression.



For my 1st stored procedure test I simply moved the sql "SELECT COUNT(*)"
statement into the stored procedure to determine if a xfer_app record
existed for the current target xfer record being imported. If the record
existed the stored proc returned a 1 if the record didn't exist the stored
proc returned a 0.



I added the OleDB Command 0/1 result to the data stream that was passed to
the next step in the SSIS process. The next step looked to see if the OleDb
command returned a 0 or 1 for each record in the 'batch'.



What we found was that the SSIS process would read about 9500 records from
the Informix table and pass those 9500 records to the next step in the flow.
When the 9500 records passed thru the OleDb Command with the stored proc.
The stored proc would check for the existence of the appropriate xfer_app
record for all 9500 records before passing those 9500 records onto the next
step in the SSIS process that looked at the stored proc results and created
xfer app records accordingly. Since there could be multiple duplicate xfer
records in a 9500 record batch ... stored proc would check each xfer_app
record was never there because the OleDB Command stored proc checked all the
duplicate records in the step BEFORE the xfer_app record was created. As a
result the OleDb Command always returned 0's for the duplicate records and
the next step in the SSIS process would create an xfer_app record for each
duplicate record. Not what we wanted.



When I modified my stored proc to check for the existence of the xfer_app
AND then physically create the xfer_app record if necessary. I got a
SUCCESS and achieved the results I needed. i.e. an xfer_app record being
created for every unique 'key' in the incoming xfer record stream.



Many thanks for your time and attention. At least we will have documented
some common SSIS issues and solutions for the group at the cost of a little
extra time and effort.



Please consider this issue successfully solved.



Thanks again.



Barry

in Oregon


"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Dear Barry,
Thank you very much for your detailed updating and response.

I am very glad to hear that the problem has been fixed. If you have any
other questions or concerns, please do not hesitate to contact us. It is
always our pleasure to be of assistance.

Have a great day!


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.
================================================== ====