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>
...
We have a large number of SSISDB packages running happily, connecting to our SQL Servers using ADO.Net or Sql Native Client, making their connection using NTLM. (We don't have our SQL Server SPNs correctly configured to support Kerberos).
The SSISDB packages are hosted on and run on a dedicated SQL server, different to the SQL Servers they are connecting to.
Very occasionally, the connection attempt is made using Kerberos instead of NTLM, and the connection attempt to sql server fails. (This is going by the Windows Security event log, which reveals a Kerberos login - a successful one at the Windows level - at the precise time that the calling agent job is informed of a connection timeout and fails, approx 23 seconds after the job starts).
The correct configuration of our SPNs is something we may wish to look into for security best practice, and would of course fix this. However, that may not be my decision to make.
I have a question, but first I need to give you some background:
My network works with Active Directory on Windows 2000, and I have web servers running on windows 2003 and SQL Servers 2000 running on Windows 2003.
I wanted to enable account delegation and I found a bunch of information.
Everything seemed "easy", but I tried to test it first on my test servers anyways and this is what happened:
We created the SPN for the SQL Server Account is trusted for delegation check box was selected for the service account of SQL Server. Account is sensitive and cannot be delegated check box was not selected for the user requesting delegation. But when we checked the box Computer is trusted for delegation (and only this box !!) in the server running an instance of SQL Server 2000, the role of this server changed magically (just like this guys, it was magic) from "server" to "Domain Controller".
We were intrigued about this change, but we "trusted" the white paper that we had in front of us.
http://support.microsoft.com/kb/319723
After some hours, the production web servers (of the whole network) and many workstations stopped working:
The IIS on this web servers will show an empty list of websites The network and dial-up connections were missing on the web servers and also on the workstations. The web servers and the workstations affected were "isolated" from the network, the command ping was not finding any of this computers.
Anyway, it was a nightmare, it took a while to fix the mess, we reverted the changes in Active Directory, and this makes me thing that the magical "promotion" of the SQL server to Domain Controller had to do with all this.
the questions is:
Do you have an idea about what could have caused all this? I mean, I still need to enable this account delegation thing. But I would like to know first if someone has done it before in a similar environment or if someone has run into one of the problems described before.
I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax
SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.
The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' reported an error. Authentication failed. [OLE/DB provider returned message: Communication link failure] [OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe. ] [OLE/DB provider returned message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.] OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
If I use the Sql Server 2000 OLEDB provider when creating the link I get this error
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The same link using the SQLNCLI provider in Sql Server 2005
works and I am able to use double hop authentication.
My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?
I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here: http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server. If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work. Have i overlooked something? What could be causing the login prompt?
Hi, We are using SQLServer 2005 SP2. I successfully registered SPN and TCP is enabled and order of protocol are Shared Memory 1 TCP 2 Names Pipes 3
when I am running select auth_scheme from sys.dm_exec_connections where session_id=@@spid
still getting NTLM. I disabled all protocol in local client except TCP with no avail. Interstingly when I am using SQLServer 2000 client where TCP is enabled and first in order in Clinet netwrok it is working OK and I am getting KERBEROS.
i have a cluster running win2k and SQL server2k, the app on the server uses kerberos authentication. all works fine until we need to flip the cluster over - then the registration of the SPN fails - this means we need to keep registering the SPN manually - a bit of a pain and sometimes people foget to register it causing us lots of grief.
does anyone know of a way we can get the SPN to register automatically?
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
I've just installed SQL 2005 SP2 Rollup 3 Package (Build 3186) on a 2 node X64 W2K3 Cluster. Everything went fine, although after the install, the SQLAgent Services of my instances started to complain about delegation not enabled for the domain account used for the SQLAgent Service.
SPN's were already registered, so I've enabled unconstrained delegation & no errors anymore..
Apparently we're obliged to enable delegation as soon as this hotfix is installed
(maybe due to fix 938086 included in it ?)
To make this setup more secure, we would like to enable constrained delegation. This does not seem to work, as soon as we choose constrained delegation by adding the SPN of the clustername to the domain user account we're running with & restart the sql agent, it fails with the same error as when no delegation was configured:
! [298] SQLServer Error: 22022, CryptUnprotectData() returned error -2146892987, 'The requested operation cannot be completed. The computer must be trusted for delegation and the current user account must be configured to allow delegation.' [SQLSTATE 42000] ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
Summary: With Unconstrained delegation enabled for computer account & user account, everything goes fine. As soon as constrained delegation is chosen, by adding the SPN's to the domain user account of SQL Agent, SQL agent fails to start.
We use a domain account for the SQL Agent. This account has an 2 SPN registered on it
I am trying to create an asp.net website with integrated windows authentication to access SQL databases. IIS resides on WinXP and SQL Server on Win2000 SRV. Both are in the same NT Domain. IIS and SQL Server cannot reside on the same machine and a stand alone web server is ideal as the website needs to access multiple SQL Servers. IIS is set to Integrated Windows Authentication. The machine running IIS & the SQL Server are set to be "trusted for delegation" in active directory. The domain user accounts that will be accessing the databases are not marked as "Account is sensitive and cannot be delegated".
The connection string that the web app uses to connect to SQL database is:
with which the user credentials should be flown to the SQL database. But instead the delegation fails and results in the following ANONYMOUS authentication failure error:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) _______________ Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 ---------------------
For last 2 days, I'm struggling to integrate WSS 3.0 with SP1 with SQL Server 2005 Reporting Services with SP2 with Kerberose authentication.
And finally I'm stuck
At the moment I've got 2 issues, one is when "Set defaults" on Central Administration site, second is when I'm trying to browse the reporting server for report €“ using Report Viewer webpart configuration (when selecting "Report").
Before I will go futher with errors message, here is my configuration:
SQL Server 2005 with SP2, Reporting Services with SP2, WSS 3.0 with SP1 Front End: Computer: SharePointDB | SharePointDB.led.local Front End Portal url: http://sharepointdb | http://sharepointdb.led.local URL to reporting services: http://sharepointdb/SPSReportServer | http://sharepointdb.led.local/SPSReportServer Front End App Pool: LEDSPContentPool Reporting Services App Pool: LEDSPConfigAcct Report Server Service Account: LEDSPConfigAcct SQL Server Account: LEDSPConfigAcct
I know I should have separate account.
Service Principals (SPContentPool): Registered ServicePrincipalNames for CN=SPContentPool,CN=Users,DC=LED,DC=LOCAL: HTTP/sharepoint02 HTTP/sharepoint02.led.local
Service Principals (SPConfigAcct): Registered ServicePrincipalNames for CN=SPConfigAcct,CN=Users,DC=LED,DC=LOCAL: HTTP/sharepointdb HTTP/sharepointdb.led.local MSSQLSrv/sharepointdb.led.local:1433 HTTP/sharepointadmin02.led.local HTTP/sharepointadmin02
Reporting add-in is activated, I'm able to specify the report server (http://sharepointdb.led.local/SPSReportServer) and to grant permission.
1) FIRST ISSUE However when I'm trying to set the defaults for Reporting Services from Central administration I'm getting following error:
The target location you specified is not supported by the report server. A report definition (.rdl), report model (.smdl), resource, or shared data source (.rsds) file must be located within a library or a folder within it. ---> The target location you specified is not supported by the report server. A report definition (.rdl), report model (.smdl), resource, or shared data source (.rsds) file must be located within a library or a folder within it.
Reporting Server error message is: w3wp!library!1!03/12/2008-12:15:23:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ContainerTypeNotSupportedException: The target location you specified is not supported by the report server. A report definition (.rdl), report model (.smdl), resource, or shared data source (.rsds) file must be located within a library or a folder within it., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ContainerTypeNotSupportedException: The target location you specified is not supported by the report server. A report definition (.rdl), report model (.smdl), resource, or shared data source (.rsds) file must be located within a library or a folder within it. w3wp!library!1!03/12/2008-12:15:39:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!5!03/12/2008-12:15:49:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!1!03/12/2008-12:15:52:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!1!03/12/2008-12:15:55:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!1!03/12/2008-12:16:07:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!1!03/12/2008-12:16:59:: Call to GetDataSourceContentsAction(http://sharepoint02.led.local/lrs/Reports/SHAREPOINTDB.rsds). w3wp!library!1!03/12/2008-12:17:11:: Call to GetPermissionsAction(http://sharepoint02.led.local/lrs/Reports/TestSharepoint.rdl).
This error message then repeats few times, usually always after: w3wp!library!5!03/12/2008-11:18:16:: Call to GetSystemPropertiesAction().
2) SECOND ISSUE
When the I'm trying to add Report Viewer (I'm logged as Portal administrator) and then select the report from web part settings, I'm getting: Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.
When I'm looking at the Event log in SharePointDB I see Anonymous login:
I've noticed that, using Windows authentication, if I connect to the server using its server name, the DB Engine uses Kerberos authentication scheme (as it is supposed to do) but if I use Kerberos authentication, I see that the DB Engine uses the NTLM authentication
scheme select client_net_address,auth_scheme from sys.dm_exec_connectionsÂ
I need to use DNS alias to connect to my server and I want to use Kerberso auth scheme.
Like many others, I am have trouble getting this to work, and none of the solutions I have found on the inter-tubes seems to work for me:
"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> The request failed with HTTP status 401: Unauthorized."
The Setup:
MOSS/SSRS (Integration Mode) running on a server farm on a single server: myserver.mydomain.org
Service Account for all Services: mydomainmyaccount (trusted for delegation, member of IIS_WPG) myserver trusted for delegation SSAS running under Local System on ssas.mydomain.org.
SETSPN -L mydomainmyaccount results:
HTTP/myserver.mydomain.org
HTTP/myserver
MOSS Authentication Settings
Authentication Type = Windows Default Authentication Provider = Negotiate (Kerberos) Anonymous access not enabled
IIS Settings
SSRS on Default Web Site: Port 8080
Application Pool Identity mydomainmyaccount
NTAuthenticationProviders="Negotiate,NTLM"
Security: Windows Authentication
MOSS on Sharepoint-80 Site: Port 80
Application Pool Identity mydomainmyaccount NTAuthenticationProviders="Negotiate,NTLM" Security: Basic Authentication except _vti_bin/ReportServer is Windows Authentication The idea is to use kerberos to pass credentials from SSRS reports running on myserver.mydomain.org to SSAS on ssas.mydomain.org.
I have two MS Windows 2003 Server Ent Ed. SP2 in cluster. They have MS SQL Server 2005 in cluster.
I have created and endpoint and when I try to access I get the attached error in client machine. This problem only occurs in cluster configuration, because the same installation in an SQL (no cluster) works fine.
EventID: 4 Source: Kerberos The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/fra-lille-hel03.ea.holcim.net. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named machine accounts in the target realm (EA.HOLCIM.NET), and the client realm. Please contact your system administrator.
I ran into a Kerberos authentication issue because of a missing AOAG SPN. Some of the tickets that granted me access to the nodes of the AOAG cluster were using the encryption type that I would expect. However, the MSSQLSvc SPNs were not using what I would expect!
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 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.
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.
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.
