Can SSIS Work With An XML Web Service? (Verisign Payflow Pro)
Feb 20, 2007
I'm trying to figure out a solution for posting financial transactions against our Payflow Pro (Verisign) payment gateway (web service) using SSIS. The process I have in my mind goes like this...
1.) Select the appropriate records from our financial system DB.
2.) Iterate through each record and post the pertinent values against the payment gateway web service.
3.) Create log files for successful and failed transactions.
The log files would then be manually imported into our financial system.
I'm not able to get Service Broker to work. I've created the following sample and would excpect to get some data from "PreisanfrageQueue" or "PreisanfrageRequestorQueue". But both they are emtpy.
What do I do wrong?
Regards,
Manfred
create message type Preisanfrage validation = well_formed_xml;
create message type PreisanfrageAntwort validation = well_formed_xml;
create contract PreisanfrageContract ( Preisanfrage sent by initiator, PreisanfrageAntwort sent by target );
create queue PreisanfrageRequestorQueue with status=on;
create queue PreisanfrageQueue;
create service PreisanfrageRequestorService on queue PreisanfrageRequestorQueue ( PreisanfrageContract );
create service PreisanfrageService on queue PreisanfrageQueue (PreisanfrageContract );
create table debug_table; create table debug_table (id int primary key identity(1,1), msg varchar(100));
set @answer = '<preis>1</preis>'; ;send on conversation @conversation message type PreisanfrageAntwort (@answer);
end conversation @conversation;
insert into debug_table(msg) values('3');
alter queue PreisanfrageQueue with status=on, activation ( status=on, PROCEDURE_NAME = PreisanfrageAction, max_queue_readers = 100, EXECUTE AS OWNER );
-- Dialog starten
declare @conversation uniqueidentifier;
begin dialog conversation @conversation from service [PreisanfrageRequestorService] to service 'PreisanfrageService' on contract [PreisanfrageContract];
declare @request xml;
set @request = '<?xml version="1.0" encoding="UTF-8"?><Preisanfrage xmlns="4711101'">http://www.xyz.at/samples/Preisanfrage"><KundenId>4711</KundenId><ProduktId>10</ProduktId><Anzahl>1</Anzahl></Preisanfrage>';
;send on conversation @conversation message type Preisanfrage ( @request );
Some months ago I was playing with Service Broker and everything was ok. But now I just can't get it work. I opened a sample "helloworld" project and still cannot get a message in a target queue. No errors, just empty queues. We have two SQL servers sept CTP on different computers and both give the same issue. It looks like it should have a very simple solution that I cannot come up with.
The following script initializes service broker and sends a message, then selects both target and initiator queues. On my environment it just returns two empty selects.
--Initializing service broker objects use master GO
SET NOCOUNT ON GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'AdventureWorks' AND is_broker_enabled = 1) BEGIN ALTER DATABASE AdventureWorks SET ENABLE_BROKER END GO
USE AdventureWorks GO
IF EXISTS (SELECT * FROM sys.services WHERE name = 'InitiatorService') BEGIN DROP SERVICE InitiatorService END GO
IF EXISTS (SELECT * FROM sys.services WHERE name = 'TargetService') BEGIN DROP SERVICE TargetService END GO
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'HelloWorldContract') BEGIN DROP CONTRACT HelloWorldContract END GO
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'HelloWorldMessage') BEGIN DROP MESSAGE TYPE HelloWorldMessage END GO
IF OBJECT_ID('[dbo].[InitiatorQueue]') IS NOT NULL AND EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[InitiatorQueue]') AND type = 'SQ') BEGIN DROP QUEUE [dbo].[InitiatorQueue] END GO
IF OBJECT_ID('[dbo].[TargetQueue]') IS NOT NULL AND EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TargetQueue]') AND type = 'SQ') BEGIN DROP QUEUE [dbo].[TargetQueue] END GO
CREATE MESSAGE TYPE HelloWorldMessage VALIDATION = WELL_FORMED_XML GO
CREATE CONTRACT HelloWorldContract ( HelloWorldMessage SENT BY INITIATOR) GO
CREATE QUEUE [dbo].[TargetQueue] GO
CREATE QUEUE [dbo].[InitiatorQueue] GO
CREATE SERVICE InitiatorService ON QUEUE [dbo].[InitiatorQueue] GO
CREATE SERVICE TargetService ON QUEUE [dbo].[TargetQueue] (HelloWorldContract) GO
-- Starting conversation
USE AdventureWorks GO
--BEGIN TRANSACTION GO DECLARE @message XML SET @message = N'<message>Hello, World!</message>'
-- Declare a variable to hold the conversation -- handle.
DECLARE @conversationHandle UNIQUEIDENTIFIER
-- Begin the dialog.
BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE InitiatorService TO SERVICE 'TargetService' ON CONTRACT HelloWorldContract;
-- Send the message on the dialog.
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE HelloWorldMessage (@message)
print @conversationHandle
GO
--COMMIT TRANSACTION
GO
SELECT * FROM [dbo].[TargetQueue] SELECT * FROM [dbo].[InitiatorQueue]
SQL Express 2005, MUST work on windows XP Service pack 1, it should not need XP SP 2. Also it should not require windows server 2003 SP1. This is seriously limiting our possibilities as a development company, this is ruining business, ANYONE READING THIS MESSAGE WHO CARES ABOUT THIS ISSUE, AND THAT INCLUDES ANYONE WHO WANTS TO DISTIBUTE SQL EXPRESS, SHOULD ADD THEIR SUPPORT BELOW, COME ON GUYS, PLEASE LET M.S. KNOW HOW WE FEEL ON THIS ISSUE....
I'm trying to get this to work, but seem to be running into a problem.
I've used the wsdl.exe tool to generate a .VB class and added it as a reference to my Script Task
The Web service I want to call from my Script task has a method 'GetAndPublishPriceHistory()'.
When I code my Script task:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim MyService As New MyService()
MyService.<Method here>
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
When I type "MyService.", I expect Intellisense to show me the public method GetAndPublishPriceHistory() in my service, but I'm not seeing it. Instead I see GetAndPublishPriceHistoryCompletedEventArgs and GetAndPublishPriceHistoryCompletedEventHandler
So it seems the public methods that I can see when I do a 'http://<my server>/service.asmx?WSDL' are not getting included in my proxy class.
When trying to open the Family.MDF file in this program this error is displayed: Database cannot be upgraded because its non-release version (587) is not supported by this version of SQL Server. How can a current version of this file be obtained? Thank you, Tom
Hi there, I have a stupid question When I want to configure reporting service in a computer it needs Windows SharePoint Service. But When I installed and configured it in my own PC it asked for database server location instead. Anybody have any idea about this?
1) I have the Beta Preview of Roger Wolter's book on the subject. Has anything major changed from the beta version to the full version (of the book as well as the product)?
2) I want to build a front-end that queues requests for processing that is ultimately done by a standalone console application. The console app knows nothing about SQL Server 2005, so the database will have to fork/execute this app, pass it arguments on its command line, and pick up the result (written to either standard output or standard error) when it finishes. Is this possible with Service Broker? If so, how?
Hi all., I am new to SSIS,if it is a wrong place to post this, please forgive me. How to work with SSIS., I need to use SSIS in my project., Please can any one tell a link to learn how to work with SSIS.,
We have just installed a new SQL 2005 Clustered server and loaded integration services. However it appears that integrations services may not work properly in a clustered environment.
So can integration services be setup on a clustered SQL 2005 server?
the book "microsoft sql server 2005 integration services" by kirk haselden claims you can download examples and source code at www.samspublishing.com
er no
what happens is this
go to the site look for the 'downloads' link see there isn't one search for authors h etc.... lose a few minutes of your life find the book
yay
it forces you to sign up for an account in order to get the samples - outrageous
prepare for endless spam from sams
sign up
click on download samples find the configedit folder load into vs run it it doesn't work!!
thanks
Warning 1 The referenced component 'SourceLibrary' could not be found. Warning 2 The referenced component 'SourceGrid2' could not be found. Error 3 Unable to find source file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigurationsEditorinDebugConfigurationsEditor.exe' for assembly 'ConfigurationsEditor.exe', located in '[TARGETDIR]' K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditSetupConfigEditSetupConfigEdit.vdproj SetupConfigEdit Error 4 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 9 7 ConfigEdit Error 5 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 10 7 ConfigEdit Error 6 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 87 40 ConfigEdit Error 7 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 11 15 ConfigEdit Error 8 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 24 17 ConfigEdit Error 9 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 25 11 ConfigEdit Error 10 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.Designer.cs 318 17 ConfigEdit Error 11 Metadata file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlinDebugConfigEdit.dll' could not be found ConfigurationsEditor Warning 12 Could not resolve this reference. Could not locate the assembly "SourceGrid2". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit Warning 13 Could not resolve this reference. Could not locate the assembly "SourceLibrary". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit
one could argue that ssis should have a config editor supplied with it, instead of having to go into notepad
I am completely new to SSIS. Is there a way to get SSIS to work with a SQL server 2000 database. Is there a SQL 2000 Add in that can be utilized. Basically I want to save the SSIS package to the SQL 2000 database and schedule it to run.
I have configured Kerberos delegation for several web services. One of the web service calls SSIS packages, but the packages don't run with the expected impersonate user : the package starts with the imporsonate user, but continue with ASPNET user (which is not allowed to execute SSIS and connect to DB).
If the web service is called directly (no delegation), SSIS packages run with the correct user. It looks like than there is an autenthicate issue, but kerberos is configured and web services can run from one to another with the impersonate user. The issue occured only when I call SSIS packages.
Here is a extract of the SSIS log file :
Code Snippet <dtslog> <record> <event>PackageStart</event> <message>Beginning of package execution. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAPascal.Brun</operator> <source>ImportMonthlyCSV</source> <sourceid>{D053CB99-FDE4-492D-83BC-821E1B34704B}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>0</datacode> <databytes>0x</databytes> </record> <record> <event>OnError</event> <message>SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Data Warehouse" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAASPNET</operator> <source>Import CSV</source> <sourceid>{284D3166-F372-4B03-86C1-75A4D8DC9A5C}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>-1071611876</datacode> <databytes>0x</databytes> </record> ...
I have a query that joins with different table using link server in the database. SSSI doesn€™t take the query. How to make the SSIS package query to use the link server in the data base ?
I would like to setup SSIS project so that multiple developers can work on same project. I am having issues with protectionlevel properties while another developer opens the package created by other developer.
Can anyone guide me on setting up project so that multiple developers could open the package and run (not simaltaneously though). Also tips on setting up source safe or team foundation will be appreciated!
I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:
select 'AAA-'||OWNER AS SOURCE, table_name, column_name, SUBSTR(data_type,1,50) DATA_TYPE , SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH from all_tab_cols where owner='XXX' ORDER BY TABLE_NAME, COLUMN_ID
Here ARE the following errorS I get when running from SSIS:
[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".
[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.
I€™m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.
I create a Flat File Source task. After tweaking the task, the Source €śError Output€? tab shows all the input columns and all €śError€? and €śTruncation€? values are set to €śRedirect row€?
Next I add a €śSQL Server Destination€? task and connect the Source green arrow to it.
Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click €śOK€? in the €śConfigure Error Output€?, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: €śFlat File Error Output Column€?, ErrorCode, and ErrorColumn
I€™m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information?
Dear all, I've been searching the article for error handling in SSIS but seems no article have same problem exactly as mine. In my package there's container, it contains Data Flow task and some of Script tasks. I put an error precedence from Data Flow task into Script task that contains script for log an error that might be occured. The Data Flow task imports data from flat file into SQL Server 2005 and I've set a semicolon as the column delimiter. Unfortunately there is a data in flat file that had one column contain semicolon as its value. This would trigger an error and I hope the error would be logged into a table as I wrote inside Script task. But it didn't work. The error precedence won't work, the package stop in flat file source instead. I've been trying the event handler but it didn't work either. Maybe I got wrong implementation, anybody can help me explain the error handler and solve the problem ? Here is the capture of my package, since I didn't know how to attach the picture in this forum. Thanks in advance.
If in BIDS I set "work offline" on to change an SSIS package, the connections for source and destination had already been build. Can I move this package to another server and execute it without going into BIDS and changing the switch back?
I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.
The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.
I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General: Result Set: None Connection Type: OLE DB SourceType: Direct Input IsQueryStoredProcedure: False (this is greyed out and cannot be changed) Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load] (@system_cd nvarchar(10), @location_type_cd nvarchar(10)) AS BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value system_cd Locations String '03' location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size User::system_cd Input NVARCHAR@system_cd -1 User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009 Source: NewPackage Connection manager "SourceConnectionOLEDB" Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?
At one of my accounts I'm using SQL 2008r2 and SSIS in an DWH solution. Today I wanted to created a dynamic sql statement in one of the packages. The dynamic part is in the where-clause a sql query I use. I want to set the values for this clause dynamically. Therefor I created a user variable (named 'Filter') which should get it´s value at run-time.
/DTS "MSDBProdMy Package" /SERVER "My-Server" /CHECKPOINTING OFF /REPORTING EW /SET "Package.Variables[User::Filter].Properties[Expression]";" where [Starting Date] < '2008-09-01 00:00:00.000' "
However when I execute this package with dtexecui it doesn not use the value specified after the /SET parameter, but instead uses the value specifiied at design time. I get the same when I add the execution of ths pacakage as a sql server job step (type SSIS). I would expect that value specified after the /SET parameter would overwrite the valus specified at design time, but I don't see that happening.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Connect to SSIS Service on machine "DAVINCI" failed: The specified service does not exist as an installed service. ***
I see a ton of people having the same problem, but as for now I still haven't found a solution.
We have SP1 installed and have installed cumulative fix from http://support.microsoft.com/kb/918644, and been by the DCOM settings regarding local/remote access.
The SSIS service is not running. It is not shown in the list of services.
I have a webservice, which I am using in the Web service Task. The XML response, is fed to the XML source and I want to write the data from this XML to flat file. I am using the below attached schema definition for XML (.XSD). When I use this as XSD in the XML source task, in the column definition I see only three columns and they are
ZoneCode ZoneName AggregatedCZ_Id
My question is what is AggregatedCZ_Id and why I do not see the PostCode in the columns? Any help is appreciated.
I've been completely unsuccessful so far in getting the SSIS service to start under normal circumstances.
I've got the SQL 2005 RTM DVD and a clean, brand-new installation of Windows 2000 Server (Service Pack 4). All system requirements have been met according to the SQL installer. I choose to install just the Database Engine, SSIS and the Workstation components, all other installation options are defaults except Services, which are set to use a Domain User account.
The install completes without problems and the SQL Server & SS Agent services start suceessfully, but the SSIS service refuses to start (timeout). If I change the service to use LocalSystem, the same happens. If I make the Domain User (DOMAINMisterSQL) account a member of Local Admins on the server, the service still won't start.
If, however, I use a Domain Admin account, the service does start, so it appears to be a security issue, but how can I configure the service to start with fewer privileges?
This problem has appeared on each machine I've tried this on, four to date, so it's not related to the particular setup of this server. I've searched the web for weeks for a solution with no success; any help would be greatly appreciated.
