Using Variables As Input Params In WebService Task
Jul 24, 2007
Hi,
I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx
which says :
"
Variable
Select the check boxes to use variables to provide inputs. "
but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work
i tried the following strings in the value column:
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 :-)
I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?
Dim strWhere strWhere = " WHERE dbo.""User"".UID IS NOT NULL "
If Not request.querystring("EmployerID") = "" Then strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'" End If
If Not request.querystring("AccountNumber") = "" Then strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'" End If
If Not request.querystring("LastName") = "" Then strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'" End If
If Not request.querystring("FirstName") = "" Then strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'" End If
DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr")) DBCommand = New OleDbDataAdapter _ ("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
I have designed a package that reads in rows from an Excel file into a recordset then loops through the recordset sending two parameters to a webservice task. This works fine however I now need to output the results of the webservice task to a file or recordset - I have tried outputting to a file however it only stores the last result as the file is overwritten each time - I am new to SSIS and am sure there must be a really simple way to do this but cannot find an help on how to do it !
I am searching for a solution for Calling or consume a web service in SSIS through Script task. I have gone through so many links but i am able to find the exact solution. I am getting so many references, though i am unable to crack it.
My requirement is i need to call a web service URL through script task which is having a client certificate. When we are trying to connect to the URL it will ask for the certificate authentication. After calling this URL we will get a WSDL file from the web service, We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables.
How can we call that web service URL( With certificate) through script task and how can we read the WSDL file and How we can load the data into DB table.
Excuse me for the 'noobish' question but it seems in my mining models, i am 'loosing' input variables. I am using the Microsoft Decision Tree algorithm and eventhough i have set 4 variables as 'input' and all 4 of them are in my mining structure, the model is using only 3. That 4th variable is also missing from the dependency network graph. Can anyone help me solve this problem?
Can anybody tell me the perfomance difference for 2 sql statements below:
select count(*) from products where prodID between 20987 and 21003
go
declare @ProdID1 int declare @prodID2 int set @ProdID1 = 20987 set @prodID2 = 21003 select count(*) from products where prodID between @ProdID1 and @prodID2
First statement takes 1 second to execute and second statement takes more than one minute. I don't understand what is the problem with using variables.
I want to convert a SQL query as shown below into a stored procedure:
select name from namelist where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
I'm attempting to use the Web Service task to call a method provided by a vendor. The inputs are all simple types so that helps a lot, but for one input it's a string array. If I enter a single string value for a given id using the array dialog, the method works fine.
However if I try to change the input to use a variable to provide the value it fails. I'm assuming that you can't create a variable to return a string array. Please let me know if this is the case or if I'm missing something obvious ;-)
In addition, is there a way to specify a null value for an input? Currently there doesn't appear to be a way to do this in SP2. I tried leaving the string empty which didn't work and I tried using a variable with expression "NULL(DT_WSTR, 1252)" but that failed too.
For the life of me I can't figure this one out. I'm trying to call a web service using the SSIS "Web Service Task". I give the Connection Manager a valid URL, download the WSDL, and go to the input pane of the "Web Service Task" options. No problems. I select my web service and pick a web method from the methods drop-down box. No problems. But where it should pop up with an input dialog box that allows you set your inputs for the web service call, nothing shows up. No errors are reported; the input pane just remains completely blank. I can inspect the WSDL with other tools (like Altova's XMLSpy) and plainly see the inputs it takes. I haven't seen a similar issue posted on this forum so I don't know if I'm going nuts or my version of SSIS is evil or what. I applied SQL Server SP1 and SP2 but it didn't change anything. I then tried to connect to one of Amazon's public web services to see if it was a problem related to our in-house WSDL formatting, and I discovered the same problem. Take for instance http://webservices.amazon.com/AWSECommerceService/AWSECommerceService.wsdl? I create a Web Service Task for this service and when I go to select the "ListLookup" method from the drop-down box on the input page no input parameters appear. The WSDL clearly defines this method to take a bunch of inputs. If anybody has any ideas about what's going on here I would greatly appreciate the help. Thanks.
I'm using a Row Count Task to count the number of records passing thru a particular path in my data flow. I created a package variable and referenced this variable in the Components Properties tab in the Row Count Task. I believe this is the minimum I need to do to get the row count.
However, as I explore the other tabs in the editor, I see there is something called Input Columns tab. What is this for? I didn't select anything in there and things are working fine. At first I thought that I had to choose the columns that I want to have available to me for further processing after the Row Count Task, but this isn't the case. I am able to see all my columns coming out of the Row Count Task even tho I didn't do anything in the Input Columns tab.
Microsoft says it is possible but I just do not see how. Here is the link to the help file where it said that variables could be pass as input to web methods...I do not see the check box they mention on my IDE.
There is not a way to pass parameters to input of Web Service tasks. I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.
If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation. I hope I'm wrong, so could someone please give a pointer. Thanks
I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.)
The command should be something like this:
Code Block
EXEC dbo.UpdateSynchTimestamp ? I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error:
[Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is strange for a number of reasons:
1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue. 2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything.
If I change the code to include a value hard-coded it works:
Code Block
EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD It is only when a variable is involved that it breaks.
Finally, here's the Stored Procedure itself:
Code Block
CREATE PROCEDURE [dbo].[UpdateSynchTimestamp] @NewValue TIMESTAMP AS BEGIN SET NOCOUNT ON;
UPDATE ServerSettings SET [Value] = @NewValue WHERE [Key] = 'SynchTimestamp' END Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported.
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used, it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:
1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123
b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE FROM @NewParameterName"
d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."
2. a) see 1.a)
b) see 1.b)
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE FROM ?"
d) Running this results in following error: "Syntaxfehler, Berechtigungsverstoß oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."
I am trying to get the max of a fields in to a user variable
SELECT User::maxnafseq = MAX(nafseq) FROM naf
The error I get is
[Execute SQL Task] Error: Executing the query "SELECT User::maxnafseq = MAX(nafseq) FROM naf" failed with the following error: "SQL: Column 'MAXNAFSEQ' is not found.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
I'm trying to run a statement against all of my servers - I've got about 10 statements working, but am having trouble with this one. It's in a for loop with a connection that is changed by a recordset which is a list of my servers. Here is the code - In the step before, I've created the tempdb.dbo.DBROLES table.
Code Block declare @dbname as varchar(200) declare @mSql1 as varchar(8000) DECLARE DBName_Cursor CURSOR FOR select '[' + name + ']' from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN Set @mSQL1 = 'Insert into tempdb.dbo.DBROLES ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '''+ @dbName +''' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @dbNAme+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+ @dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @mSql1 Execute (@mSql1) FETCH NEXT FROM DBName_Cursor INTO @dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor;
failed with the following error: "Must declare the variable '@dbName'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Put dbroles into table
The program '[10320] DBA_Repository_Load (1).dtsx: DTS' has exited with code 0 (0x0).
I'm going to try creating the procedure on each server - that will probably work - I just don't get why it's not recognizing that variable. The line it is referring to is FETCH NEXT FROM DBName_Cursor INTO @dbName. Thanks for any info on this!
I use the variables "CustId,FileName,FileCreateDate" in the ReadWriteVariables collection. Any ideas would be appreciated as to how to get around this error.
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.
I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!
Hey all, I have a stored procedure, which need one variable as parameter. I am trying to call this stored procedure from my DTS Task and my parameter is defined as the Global Variable in DTS. here is the SP call within my DTS Task
it gives me an error that DTSGlobalVariables function not defined. In this case how can i pass the value of Client Id which is my global variable to my SP.
