Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

SSIS Web Service Task Input Parameters

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

Kevin Le

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SSIS Web Service Task - SOAP Header Input

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.
Any help highly appreciated.

View Replies !   View Related
Web Service Task Not Showing Input
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
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.

View Replies !   View Related
Null Value For Web Service Task Input
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.

View Replies !   View Related
Web Service Task - Passing Variable As Input
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

Any help would be greatly appreciated.


View Replies !   View Related
Execute SQL Task : Input And Output Parameters In Tsql Stataments With ADO.NET Connection Type
Hi Everyone,

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!!




View Replies !   View Related
Dyanamically Passing Input Parameters To Stored Procedure By Using SSIS

I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert

Uniq_Id,      emp_ass_id,               start_date                                     end_date                    status_id



1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM                  1



1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM                1



1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM                1



1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM                1



1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM                  1

the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.

Thanks in advance.

View Replies !   View Related
Input Parameter In SSIS Execute SQL Task.

I would like to create a SSIS package that is going to be called by store procedures.

What i have done so far.

1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?

2) I save this package as a DTSX file and will called it from a proc.

My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks

View Replies !   View Related
SSIS Web Service Task
I have been struggling with a problem with the Web Service Task.  I have a package that uses this and has to authenticate to the web service.  This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service.  I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is.  If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine.  My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation.  Thanks.



View Replies !   View Related
SSIS - Web Service Task
I just started using the Web Service Task in my package.

I set the HTTP Connection Manager with URL & required credentials and the test connection was successful. And I saved the wsdl locally and selected the wsdl. In the Input I selected the WebService and as soon as I do that I am getting the error:

"Item has been already added.  Key in dictionary: PosDataSet    Key being added: PosDataSet

I am using this web service elsewhere in a console app and it is working fine.

Any idea what could be causing this? Any input will be greatly appreciated

View Replies !   View Related
Web Service Task IN SSIS
hi friends,
regarding the Web Service Task
i was created a with c#   webservices one.asmx  wich contains the 2 methods
 1) no perameters(just like hello world)
 2) with perameters:
        sum(int a , int b)
         return a+b;
until this step fine
in ssis Web Service Task   i am giving the input permeters to sum method
whenever we select the sum() method bellow box displaying the 2 input values
 Name   type Value
 a          int    ??????????????    (IS IT POSSIBLE TO give our local variables    i was given   gives an error @   [  a  ] )
 b          int   ???????????????   (IS IT POSSIBLE TO give our local variables    i was given   gives an error @    [  b  ] )
 we can store the output into our local variables but is it possible to give the input as our loc variables
thanks to all

View Replies !   View Related
Passing Parameters To SSIS Execute Process Task
Can anyone help me in for the following.
i want to execute a exe file with two variable parameters
Executable : C: empMyExe.exe 
Parameter1   : User::Category  type is string  (below example A)
Parameter2  :  User::Amount    type is string  (below example 1)
in dos it looks like this
c: empMyExe A 1
This will executes fine.

View Replies !   View Related
Using Ssis Task To Call A Web Service

I m trying to use the web services task, but when i try to run it i got the following error

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

This is probably because i never worked with this task, even so, does anyone knows what might be wrong?

I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)

I even can download the wsdl file, but the error seems to be in the connection



View Replies !   View Related
Multi-parameters Issue In Data Flow Task SSIS
Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

View Replies !   View Related
Stored Procedures And SSIS Web Service Task
I have created a simple Stored Procedure 'hello_world'. This procedure has one argument and returns a string.

I exposed this Stored Procudure using 'CREATE ENDPOINT' and granted access. All fine. Endpoint was called 'hello_world_endpoint'.

Next step: call the exposed Stored Procedure from the SSIS Web Service Task. At the 'General' tab of the Web Service Task Editor I define the HHTPConnection, configure the WSDL file. This works as well.

Next step is the 'Input' tab. It is weird to see only three lines under the 'Input' on the right (BOL suggests that there are 6 lines). I have 'Service', 'Method' and 'WebMethodDocumentation'. I select the service 'hello_world_endpoint'under 'Service'. Then I select the method (which is the Stored Procedure name). Also okay.. now, only one line left: 'WebMethodDocumentation'. Can't fill in anything here.

Now, if I press 'OK', I the message 'The selected Web method contains unsupported arguments'. What could this be? I even deleted the initial input argument (so the procedure only return a string) and still have the same error. Am I missing the options 'Name', 'Type' and 'Value' that are listed as options in BOL:

Thanks for your time!

Jaap Wagenvoort (The Netherlands)

View Replies !   View Related
SSIS Calling Web Service Task 401 Unauthorized Error

I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.

I have tried the following things:
Setting integrated windows authentication in IIS
Setting the NTFS permissions of those web site directories to EVERYONE
Using a credential / proxy in SSIS and running it from SQL Agent
Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account

I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo

Also in the security event log it shows all authentication as successful.

View Replies !   View Related
Adding A Web Reference (web Service) To An SSIS Script Task?
Is it possible to do this under SSIS 2005? How? I see I can add a reference to but then what?


The web service was developed in 2005 and I have no problem adding and consuming it from a web page developed using 2005 -


Thanks for any help or information.





View Replies !   View Related
SSIS SQL 2005 Script Task For Windows Monitoring Service

Does any body knows how to convert this VBScript Code to VBDotNet (SQL SERVER 2005 INTEGERATION). Below Codes Returns a list of all the services installed on a computer, and indicates their current status (typically, running or not running).Its in VBScript Format.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\" & strComputer & "

Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colRunningServices
    Wscript.Echo objService.DisplayName  & VbTab & objService.State

Deepu M.I


View Replies !   View Related
SSIS SQL 2005 Script Task For Display Windows Service

The Below code which i have used for getting the status for a windows service .
The Code display the status of Telnet service either Running or Stopped.
I would like to check if its service is stopped or starting i would able to notify.
For example My SSIS Script(SQL SERVER 2005) application is running
Manually i am changing the status of Telnet through (Control pannel / Services) like start and stop.
If the service is stopped i should able to see the status.

I have tried to use timer control to execute the scripts for simultaneosuly...
But timer event is not firing in the SSIS script componet page....

Please help ....


Imports System
Imports System.Management
Imports System.Data
Imports System.Math
Imports System.Web
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

  Public Sub Main()
        Dts.TaskResult = Dts.Results.Success
        Dim str As String
            Dim searcher As New ManagementObjectSearcher("rootCIMV2", "Select * from Win32_Service where Name='TlntSvr'")

           For Each queryObj As ManagementObject In searcher.Get()
                If queryObj("State").ToString = "Running" Then
                    'str = str & queryObj("DisplayName").ToString & " --- Status " & queryObj("State").ToString & vbCrLf
                    str = "Service is Running"
                    str = "Service is Stopped"
                End If


        Catch err As ManagementException
            MessageBox.Show("An error occurred while querying for WMI data: " & err.Message)
            MessageBox.Show("An error occurred while querying for WMI data: " & err.StackTrace)
       End Try

    End Sub

 End Class

Regards Deepu M.I



View Replies !   View Related
Problems With Connections And Analysis Service Processing Task In SSIS
Hi everybody,

I'm fairly new to the SSAS/SSIS world (though not new on databases, etc.) and I'm having some problems with the SSIS packages in our Cube environment.

Currently in our SSAS/SSIS project, we have two major connection managers, one to the database we use for loading the Cube, and the other connector for the cube itself. To load the data from the database to the cube, we wrote some SSIS packages and used the Analysis Service Processing tasks to process all the dimensions and measures. This works pretty good, so no problems here.

The real problem starts, when I try to change the connection parameters, e.g. because the server changed, or the database has been renamed.
As soon as the connection managers points to another (existing) cube, regardless if the structure is exactly the same as the one of the old cube, the tasks lose all the assigned objects from their lists. It is really annoying to add all these exactly same objects to the task again. I tried experimenting with the DelayValidation attribute so the Development Studio doesn't destroy my work every time, but when I deploy the package the Cube breaks. Obviously some kind of deeper connection is destroyed when I change the connection string.

Is there a way to prevent the package from breaking/losing objects, without me having to sacrifice 15 minutes every time I change the connection parameters?


View Replies !   View Related
How To Specify Input Parameters?
I'm very new to SQL Server so please forgive me if this question is ridiculously simple.  I have to upgrade the report engine from one that was used in a legacy VB6 app to a app.  In doing so, I'm looking at assorted reports that came out of the old app.  Here is the SQL code for one of them:

SELECT (LTRIM(STR(From_To,10,0)) + '-' + LTRIM(STR(From_To + 49,10,0))) AS Bonus_Earned, COUNT (Empl_ID) AS Men, round(SUM (SumDollars),2) AS Group_Earn, round((SUM (SumDollars) / COUNT (Empl_ID)),2) AS Calc0 FROM (SELECT CONVERT (int, round (SumDollars / 50, 2)) * 50 AS From_To, SumDollars, Empl_ID FROM (SELECT round(SUM (Actual_Hours_Dollars.Incentivedollars * Contract_History.Bonus_Pct / 100), 2) AS SumDollars, employees.Empl_ID FROM ((Employees INNER JOIN Actual_hours_dollars ON Employees.Empl_ID = Actual_Hours_Dollars.Empl_ID_R) INNER JOIN Contracts ON Actual_Hours_Dollars.Contract_ID = Contracts.Contract_No) INNER JOIN Contract_History ON Contracts.Contract_Idx = Contract_History.Contract_Idx_R where employees.empl_idx = (SELECT max(empl_idx) FROM Employees AS OuterEmployees WHERE OuterEmployees.empl_id = employees.empl_id AND outeremployees.Datex = (SELECT max(datex) FROM Employees AS InnerEmployees WHERE InnerEmployees.empl_id = employees.empl_id AND Inneremployees.disabled = 0 AND Inneremployees.Datex < '~EndDate~')) AND Contracts.Datex = (SELECT max(datex) FROM Contracts AS InnerContracts WHERE InnerContracts.contract_no = Contracts.contract_no AND InnerContracts.disabled = 0 and InnerContracts.deleted = 0 AND InnerContracts.Datex < '~EndDate~') AND Actual_hours_dollars.Datex >= '~StartDate~' AND Actual_Hours_Dollars.Datex < '~EndDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) >= '~StartDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) < '~EndDate~' and Actual_Hours_Dollars.IncentiveHours <> 0 GROUP BY employees.empl_ID) AS InnerRS1 GROUP BY ROUND (SumDollars * 2, -2) /2, SumDollars, Empl_ID) AS InnerRS2 GROUP BY From_To

I'm only including it for completeness.  The key thing I'd like to draw your attention to are two variables that are clearly input parameters:  ~StartDate~ and ~EndDate~.

My question is this: If I want to copy this code into SQL Query Analyzer and run it to see what kind of results I get back, what's the simplest way to define these two input parameters?  I'm hoping you could just show me the syntax to define them above the SELECT statement.

Robert Werner
Vancouver, BC

View Replies !   View Related
Retrieve Date Using Input Parameters W/o GUI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance

View Replies !   View Related
Parameters Input Field Size


is it possible to change the appearence of input fields for parameters on the report server? My parameter is Multi-value with quite large amount of available values. On report server, user can (without scrolling) see only the first value. Parameter values are quite long, so user has to move alternally with both vertical and horizontal scrollbars to find the right value.




View Replies !   View Related
Insert Syntax When Passing Input Parameters
I'm trying something like this:

CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
VALUES (@Dist)
select @CheckNo=@@IDENTITY

If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?

PS I'm using Microsoft SQL 7.0

View Replies !   View Related
SqlCommand Return And Output Parameters Not Working, But Input Does?
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?

System::Boolean rs::sp(System::String ^ ssp){

System::String ^ k0s0; bool bOK;

System::Data::SqlClient::SqlParameter ^ parami0;

System::Data::SqlClient::SqlParameter ^ parami1;

System::Data::SqlClient::SqlParameter ^ parami2;

System::Data::SqlClient::SqlParameter ^ paramz0;

System::Data::SqlClient::SqlParameter ^ paramz1;

System::Int32 pz0=0;System::Int32 pz1=0;

k0s = ssp;



paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);




















ndx = -1;


if (ndx == -1){





for (iG1_20=0;iG1_20<r0nf;iG1_20++){





if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}



else {



for (iG1_20=0;iG1_20<r0nf;iG1_20++)

{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}











return true;


View Replies !   View Related
Webservice Task Input
I want to dynamically pass values to the input value of a Web method to return values to a webservice.

View Replies !   View Related
Stored Procedures Management - Keeping Input Parameters Updated
Hi everyone

I have just starting creating some stored procedures for our system and have a question related to management of these.

When using input parameters using the following syntax:

CREATE PROCEDURE sp_someInputProcedure
@Username as varchar(16)
@Password as varchar(12)
@Name as varchar(50)
@Address as varchar(60)
@Zip as int
@City as varchar(30)

This is all well and good, but what if I make a change in the datamodel - for instance changing a datatype or the length of a varchar - do I need to remember to manually update all stored procedures that uses these columns/variables?

Seems like a bit of a hazzle. Is there an easier way to do this?

Many thanks,

Stian Danielsen

View Replies !   View Related
1 SP With Dynamic Input Parameters And Multiple Rows As The Source Of The Query
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you

View Replies !   View Related
Sniffing StoredProc Input Parameters For General Error Handling

id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used  in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.

What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined  input parameters of the SP and  generates the logging statement  accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.

So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....

Any tipps would be appreciated...


View Replies !   View Related
Using Variables As Input Params In WebService Task


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:


which says :



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:







any ideas?


thanks for your help



View Replies !   View Related
Execute SQL Task With An INPUT Parameter Of Type DBTIMESTAMP
Hi Everyone,

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

    UPDATE ServerSettings
    SET [Value] = @NewValue
    WHERE [Key] = 'SynchTimestamp'
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.

Thanks for reading this!

View Replies !   View Related
Amazon Web Service Call With Web Service Task
With the new improvments to the web service task, we can now use variables as arguments in web service calls.  I am trying to setup a call to the amazon web service ECS.  I am trying to do a simple sellerlookup.  I have played with the settings and gotten nowhere.  I get one of two error when I try to execute.  I can always use a scripting task or write my own task, but I would like to use the built in task if it is possible.  Has anyone used AWS with SSIS?


[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".


[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Method 'ProxyNamespace.AWSECommerceService.SellerLookup' not found..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

View Replies !   View Related
Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'


This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
     USE AP
      --Code that passes the parameters by position
      DECLARE @MyInvTotal money
      EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
      PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
     USE AP
     DECLARE @InvTotal as money
     EXEC spInvTotal3
              @InvTotal = @InvTotal OUTPUT,
              @DateVar = '2006-06-01',
              @VendorVar = '%'
      SELECT @InvTotal
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)



Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output



'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception





End Try

End Sub

End Class
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question:  I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". 
                        How can I do it?
#2 Question: Did I set the CommandType property of the command object to
                        CommandType.StoredProcedure correctly?
#3 Question:  How can I define the 1 output parameter (@InvTotal) and
                        2 input parameters (@DateVar and @VendorVar), add them to
                        the Parameters Collection of the command object, and set their values
                        before I execute the command?
#4 Question:  If I miss anything in print out the result for this project, what do I miss? 
Please help and advise.
Thanks in advance,
Scott Chang


View Replies !   View Related
Passing Object Variable As Input Parameter To An Execute SQL Task Query
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).


Please give me solutions for the above two..



View Replies !   View Related
Map Resultset From Executing A Stored Proc Into Input Columns Of A Data Flow Task

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:
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 ]
thanks in advance!

View Replies !   View Related
SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View Replies !   View Related
SSIS Many Input Files To One Destination
I've been experimenting with SSIS (SQL Server Integration Services) and I have got some data imported. However I have a couple of questions.(1) I would like to know how I would go about importing data from multiple files in one operation. I have many files with the same format to be imported to one table. I'm looking for a way to have it iterate over all files in a directory, or a list I specify. It would performing essentially the same data flow operation but with a different input file. (2) I also have a variable that would need to be updated on each iteration as one item of data is missing from each file because it would be the same for all rows in the file. I currently set a variable before running the package that provides the missing value and I use the derived column transformation to inject it into the data flow.Any help would be greatly appreciated - especially pointers to relevant documentation as I just can't seem to figure out the correct key words to stick into MSN Search.

View Replies !   View Related
New 2005 SSIS Task: File Properties Task
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.

View Replies !   View Related
SSIS : Flat File Input And XML Output

 Hi All,

       I want to know is it possible to have source as Flat File and destination as XML

Thanks in advance,









View Replies !   View Related
SSIS Doesn't Read All Input Rows
Hi *,

I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?


View Replies !   View Related
How-to Execute SSIS Package With Input Parameter?
Hi all.

we have SSIS package which runs under SQL Job.

Now we need to modify this package in order to have  input parameter 

which we want to setup in the "Set Value"  Tab of SQL Server 2005 SQL Job |Edit Step |Option window.

Could anybody give me in details  how-to setup Global Variable (or something else)  inside SSIS Package in order to create input parameter of the package?

I also appreciate an advice how exactly we should assign value to input parameter of SSIS Package inside SQL Job Set Value Tab (example?).

Thank you,


View Replies !   View Related
SSIS Script Component Accessing Input ?? Please Help!
I have a script component task that uses a column (which contains filenames) as input,

my problem is i cant access this in the script ? the column name is "Document"

and i have tried .
Test_var = Me.ProcessInput(186, "fulldoc").Value

View Replies !   View Related
Execute &&"Select&&" Depending On The Input Parameters
Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.

Something like this:

create storeproc
Param1, param2

if param1 <> null

Select *
from table
where id = param1


Select *
from table
where id = param2

end if

Thanks in advance....





View Replies !   View Related
Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates
SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View Replies !   View Related
Accessing Input On SSIS Script Component Tasks
I have a script component task that uses a column (which contains filenames) as input,

my problem is i cant access this in the script ? the column name is "Document"

and i've tried Me.ProcessInput("Document")

View Replies !   View Related
SSIS Script Component - Iterating Through Input Columns

I'm trying write a reusable script component that takes data from rows that were rejected from a SQL Destination operation and put them into a common SQL error table.
This script would basically function to take the input columns selected in the script, and build a delimited string, (similar to the 'Flat File Source Error Output' that is contains redirected rows from reading a flat file) and insert this string into a SQL table called 'SourceData' to store errors.
I'm trying to script the component to iterate through all input columns (as selected in the input columns screen) and build a simple string.


Code Block
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Use the incoming error number as a parameter to GetErrorDescription
Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

Row.ErrorColumnName = ComponentMetaData.InputCollection(0).InputColumnCollection(Row.ErrorColumn).Name
Catch ex As Exception

Row.ErrorColumnName = String.Concat("Column Name retrieval failure. Details", ex.Message)
End Try
'Build input data
Dim inData As String

For Each inputCol As IDTSInputColumn90 In ComponentMetaData.InputCollection(0).InputColumnCollection
inData = String.Concat(inData, "~", inputCol.Name) 'I don't want the name, but the value.
Row.SourceData = inData
End Sub
I've only got as far as iterating the names of columns in the input buffer, but how do i get the values?
The result i'm trying to achieve is :
Selected columns in 'Input Column' screen : Name, Address, Phone
OutPut column 'SourceData' value : Harry~Melbourne~None
I don't want to write the code as:

Code Block
inData = Row.Name
indata = String.Concat(inData,"~",Row.Address)
indata = string.concat(inData,"~",Row.Phone)
as this make my code not very reusable. I've got some tables which are 100+ columns long and I don't wish modify the code too much 
I have also tried overriding the ProcessInput() function of the script component to iterate through the buffer columns :

Code Block
Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

MyBase.ProcessInput(InputID, Buffer)
Dim iCnt As Integer = 0
Dim inData As String

If Buffer.ColumnCount > 0 Then

For iCnt = 0 To Buffer.ColumnCount - 1

inData = String.Concat(Buffer.Item(iCnt)) 'Error thrown here: PipelineBuffer has encountered an invalid row index value.
End If
End Sub

but i get an error when i run it.
Please help.

View Replies !   View Related
How Do You Get A Reference To The Task Host In An SSIS Task
I've created my own posting for this.  The original post was here, I apologize:
According to the poster it's not possible.  But there has to be some way to do it?  Reflection (don't know how)?

I need to get a reference to the task host in an SSIS Task component.
Basically the scenario is this:
I have a custom task I have created.  However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task.  I know how to verify its a string variable.  But I can't figure out how to read what the user selected (such as User::Myvariable).  The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.
What I did is this:
I've implemented IDtsTaskUI and during the initialize section I wrote:
  Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
    ' Store the TaskHost of the task.
    Me.taskHostValue = taskHost
    Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
    myTask.myTaskHost = taskHost
  End Sub
My Task is named: CustomTask.  I have a public variable in my task as follows:
  Public NotInheritable Class CustomTask
  Inherits Task
  Implements IDTSComponentPersist
  Public myTaskHost As TaskHost = Nothing
Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.
Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.
I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI
How do I solve this? 

View Replies !   View Related
Flat File Name As SSIS Data Source Input Parameter
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?

View Replies !   View Related

Copyright © 2005-08, All rights reserved