Help Passing Complex Variable Types To A Web Service Task
Feb 23, 2007
I need a little help here and appreciate any insight into this issue.
I am building an SSIS package that retrieves data from a database to use in a web service task. So let me give you a little more broad overview of the package so you can understand how this is supposed to roll. A database is queried and those values are dumped into a recordset. A foreach loop uses each row of variables to call the web service and dump the returned values to another database. The first database holds a bunch of fields, but the four fields of interest are: a StartDate (DateTime), a StartFormat (single char), an EndDate (DateTime) and an EndFormat (single char). The output from the query of the first database is the input in the signature of a web service's .Load method. Sounds easy, right? Sure, why not?
Well I dragged the Web Service Task on to the pane and took a look inside. Lo and behold, I can hardcode the variables in for the web service or I can assign the inputs to package variables. How fancy, thanks Microsoft!
But that's where the difficulty begins. The method call for the web service looks like this: service.Load(string, int, GTTimestamp1, GTTimestamp2). The web service is expecting a string, an int, and two objects of the type GTTimestamp, which is a very simple class defined as this:
<System.Xml.Serialization.SoapTypeAttribute("GTTimestamp", "http://util.gtdw.pci.com")> _
Public Class GTTimestamp
Public calendar As Date
Public displayFormat As String
End Class
In the input pane for the Web Service, when I click in the value of the two GTTimestamps like I'm going to hardcode them in, another window pops up saying "Enter the values for complex type -in4" and the pane looks exactly like the previous pane with one very important exception: There is not place to check to assign the value from a package variable!!! Dang you Microsoft!!! I don't really understand why they would leave us out to dry on this... Oh, well, maybe they'll take care of it later...Time to work around it.
SSIS does allow you to create a variable of type System.Object, so after playing with the Web Service for a few minutes and giving up on that, I decided to create a script task that is supposed to create two GTTimestamp objects and assign them to two object variables in the package for passing to the WebService Task. The first challenge was to get the web service to play nice with the script. For those who have never done this, use a command prompt and the wsdl.exe to generate a .vb or .cs file to add to your script file using the right click, add existing item...
Once the file was accessable to my scripts, I created two GTTimestamp objects and assigned them to the Package variables of type System.Object. Running the package, I got this error:
"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Type 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp' in assembly 'VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' is not marked as serializable.
Not marked as serializable, huh. Okay, so I made the GTTimestamp serializable by adding the <Serializable()> before the class declaration. Then the error changed to:
The error is: Type is not resolved for member 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp,VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'.
And here I am, at a loss for what to do from this point. I'm a little lost, so I thought I'd stop and ask for directions. I'm sure I'm not the first to want to use complex variable types.
Only two options at this point. One is to try and store the GTTimestamp in the database and see if SSIS can deal with that. I'm not sure how to store objects in a database or if that is even an option for me, but it came to mind so it made it into this post. The other is to get this to work through the script above that I have run into a wall.
Again, any help is appreciated. Thanks for your time.
View 11 Replies
ADVERTISEMENT
Oct 5, 2011
When you pass a complex type (the one represented by class) to a web service the BIDS UI allows you to enter values for every field of that type as constants. But what if you want to pass a variable? Once again the UI allows you to specify a variable for that complex type parameter. But how to make this variable in SSIS?I understand it should have the type of Object. But how to specify what the runtime type of this object is? And how to assign all fields to that object?
View 6 Replies
View Related
Nov 24, 2006
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.
Any help would be greatly appreciated.
Thanks,
Catherine
View 4 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
Jul 23, 2007
Hi all,
I'm working with Execute SQL task. Connection type: OLE DB. With the following settings, the task works fine:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::InputFile Input NVARCHAR 0
User::DesiredOutput Input NVARCHAR 1
SQLStatement:
Code Snippet
exec [spu_CreateOutput] ?, ?
However, I want to put in some conditions so I modified the task as:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::OutputFile Input NVARCHAR 0
User::InputFile Input NVARCHAR 1
User::DesiredOutput Input NVARCHAR 2
SQLStatement:
Code Snippet
if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
But the modification doesn't work. The following message was thrown:
Error: 0xC002F210 at CREATE OUTPUT, Execute SQL Task: Executing the query "if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: CREATE OUTPUT
Can anyone tell me what I have been wrong with it? It seems that parameter mapping can only apply for a single select statement/function/procedure call :-?
Thank you,
GiaHi
View 5 Replies
View Related
Sep 25, 2006
I am trying to exectue SQL task as below by passing a parameter
If I try....
@v1 datetime
set @v1 = convert(datetime, ? ,103)
it fails with below error
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
however the below code works well
delete from t1 where last_update = convert(datetime, ? ,103)
What could be the problem?
View 4 Replies
View Related
Jun 11, 2008
Hi,
I am creating SSIS package which using Execute SQL Task to which I am supplying one .sql file code is
delete from Test where ID = @ID
I defined @ID variable to SSIS and also path where .sql file placed but i am not able to execute this package i am getting error like can not find C:@Path file......
As i got information that passing such variable to .sql you need ADO.net connection so I changed SQLSERVER Database connection string to ADO.net .... after that when i set hard core value for these variable i.e for @ID and @Path then it runs sccessfully but by setting parameter i am getting above error
Any suggestion that will be gr8 help for me
T.I.A
View 2 Replies
View Related
Mar 29, 2007
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 6 Replies
View Related
May 26, 2005
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
View 1 Replies
View Related
Jul 19, 2007
Hi friends,
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Apr 19, 2004
Hello -
I've used SQLXML to generate WSDL from a sample stored procedure that returns customerID and contactName from the 'northwind' database based on a runtime-supplied parameter.
However, the generated WSDL doesn't explicitly name the two columns (customerID and contactName) that the procedure returns. I've tried all of the possible combinations of 'row formatting; and 'output as' when configuring the virtual name. Instead, I get the following:
- <xsd:element name="TestResponse">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name="RobTestResult" type="sqlresultstream:SqlResultStream" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
To parse the results, I would have to cast the information correctly, which requires some hard-coding. Does anyone have any suggestions on how to force the WSDL file to correctly list out the individual elements in the result set?
Many thanks!
Robert
View 1 Replies
View Related
Feb 15, 2008
Hi All!
I have a parent package that contains two children... The second child depends on the succes of the first child.
THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...
Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...
I tried to execute coth children as Out Of Proc as well as In Proc...The same
Would anybody have an idea how to resolve this problem?
Thanks in advance for any help!
Vladimir
View 5 Replies
View Related
May 21, 2007
Hi,
I am trying to make a call to a third-party web service in my SSIS package. The request has custom complex data type as the parameter. As has been pointed out in this forum before, the Web Service Task only lets you assign the outside parameter from a variable, not the internal parameters needed to create the complex data type.
To be more specific, the web service input wants a 'ContactSearchRequest' parameter. I can assign this from a variable. If I click on the 'value' field under the 'Input' section for the web service task, it shows me that the 'ContactSearchRequest' data type is made up of the following:
contactId - long
numResults - int
offset - int
passKey - string
searchParam - string
sortType - int
Unfortunately, I can't assign these internal parameters from a variable, at least not through the web service task interface.
My next thought was to create a variable of type 'object' and then set it in a script task prior to calling the web service task. However, I'm not sure exactly how to do this. How will my script know about the class definition of 'ContactSearchRequest'? Do I just create a class called 'ContactSearchRequest'?
I've used this same web service in a .NET C# project and after I imported the web service, visual studio knew all about the custom data types. How do I do something similar in SSIS?
Of course, the easiest solution would for Integration Service to allow me to set those internal parameters via variables, but we're apparently not there yet.
Any suggestions?
Thanks,
Trey
View 8 Replies
View Related
Feb 8, 2007
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
<soap:Body>
<GetXml xmlns="myreports.ws">
<root >
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
</root>
</GetXml>
</soap:Body>
My question is: how do I pass the xml to the web service from reporting services.
I have seen examples with simple types. They do it with this query:
<Query>
<SoapAction>myreports.ws/GetXml</SoapAction>
<Method Namespace="myreports.ws" Name="GetXml"></Method>
<Parameters>
<Parameter Name="Parameter1">
<DefaultValue>some value</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="True">
GetXmlResponse{}/GetXmlResult{}/diffgram{}/NewDataSet/Element{@Attribute1,@Attribute2}
</ElementPath>
</Query>
But there are no example on using xml directly instead of parameters. Can anyone help me? Where do I put my xml?
View 3 Replies
View Related
Oct 25, 2006
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View 1 Replies
View Related
Oct 9, 2007
Hi,
Need a guidance on consuming the WCF service with complex type in the SSIS package.
I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee
{
FName String;
LName String;
Age int
}
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee
{
FName String;
LName String;
Age String
Type EmployeeType;
}
EmployeeType
{
type string;
}
Now I get the error Web Method has unsupported arguments.
Can this be done in SSIS?
Virendra
View 3 Replies
View Related
Oct 30, 2003
This is part of the codes of a stored procedure:
-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)
However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.
This user-defined data type is created in a user database only.
I tried to created the user-defined type into BOTH tempdb and master but same error.
How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?
In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)
and cerate udt_PRNum in tempdb only could solve it.
But now i want to use table variable.
It seems NOT work for my situation.
Any idea?
View 1 Replies
View Related
May 18, 2006
Greetings once again SQL friends,
Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!
I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.
The syntax I am using is as follows :
(dt_str) @[User::MAX_OFFER_PRICE_ID]
My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.
What am I doing wrong?!
Thanks for your help in advance.
View 7 Replies
View Related
Mar 28, 2007
Hi,
I am using SQL Server 2005 Reporting Services. I want to make it secure. I am also using WCF services and made them secure using Claim based System.Identity Model.
I want to apply same claim based model to Reporting Services.
How can I do that?
Amit
View 2 Replies
View Related
May 4, 2006
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()".
or
[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 6 Replies
View Related
Aug 9, 2007
Hi,
This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10.
Please help.
Thanks
View 13 Replies
View Related
May 18, 2007
Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);";Doesn't seem to work though. CODE: [WebMethod] public string addCol(string pColName) { SqlConnection cnn = new SqlConnection(connString); try { cnn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnn; cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);"; SqlParameter rColName = new SqlParameter("@rColName", pColName); cmd.Parameters.Add(rColName); int i = cmd.ExecuteNonQuery(); cnn.Close(); return "Insert Successful"; } catch { return "Insert Unsuccessful"; } }
View 3 Replies
View Related
Jun 14, 2007
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
View 4 Replies
View Related
Jan 18, 2008
Hello,
I have a SP that recevies a date value for a users date of birth called "dob".
However when passing it into the class which contains the Stored procedure it gives an error.
Below is my code
please advice
Thanks
Ehi
1
2 command.Parameters.Add(new SqlParameter("@usernames", SqlDbType.Int, 0, "RegionID"));
3 command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20, "username"));
4 command.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.VarChar, 50, "First_Name"));
5 command.Parameters.Add(new SqlParameter("@Last_Name", SqlDbType.VarChar, 50, "Last_Name"));
6 command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 50, "dob"));
7
8 command.Parameters[0].Value = 4;
9 command.Parameters[1].Value = "username";
10 command.Parameters[2].Value = "First_Name";
11 command.Parameters[3].Value = "Last_Name";
12 command.Parameters[4].Value = DateTime.Parse(dob.Text);
HERE IS THE ERROR MESSAGE
Compiler Error Message: CS0103: The name 'dob' does not exist in the current context
Source Error:
Line 40: command.Parameters[2].Value = "First_Name";
Line 41: command.Parameters[3].Value = "Last_Name";
Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text);
Line 43:
Line 44: int i = command.ExecuteNonQuery();
Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 42
View 7 Replies
View Related
Apr 25, 2008
I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
View 2 Replies
View Related
Apr 28, 2008
could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help
View 8 Replies
View Related
May 11, 2008
hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great ! tnx
View 2 Replies
View Related
Aug 9, 2007
What is the easiest way to pass a value into a variable...
TSQL...
declare @@test int
--select count(*) from authors
select count(*) into @@test from authors
View 1 Replies
View Related
May 20, 2007
hi guys what is the syntax for using the passing variable part into the name of a table in a store procedure. in particular: (assume already declared the variable periodseq.
select *
into Temp_Usage_@periodseq
from Master_usage
where Master_usage.PERIODSEQ = @periodseq
in particular the Temp_Usage_@periodseq line of code, how do i "add" the periodseq (which is a number) to the end of the name of Temp_Usage, i.e: Temp_Usage_112
is the syntax an & like Temp_Usage_&@periodseq?
Cheers
Champinco
View 1 Replies
View Related
Mar 10, 2008
I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used.
I've read BOL and bought a book. Can't say either one has really helped.
I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.
What I'm trying to do now is parameterize a SQL statement.
I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.
Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.
I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?
I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.
Is my approach fundamentally flawed in some way?
View 25 Replies
View Related
Oct 24, 2007
Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.
View 11 Replies
View Related
Jan 28, 2008
I am running a query in with my daily import job that lets me know if a duplicate record was imported into the system. If so I want to setup my email table to mail me the following information. My code works fine except the value of the variable @ID is only giving me the first record in the set. How do I pass it on to the next record? I get the right number of rows inserted into the email table but the @ID variable is the same for all 3.
Drop Table #SER1
Select id#,
Colx=Count(*)
Into #SER1
From Business
Group By id#
Having Count(id#) > 1
Declare @ID Varchar (4)
Select @ID = ID# from #SER1
INSERT INTO Email
( [To],
[Subject],
[message],
[Table],
TableUKey,
TableSource
)
SELECT
'Firstname.Lastname@Company.com',
'Duplicate ID# Imported',
'ID# ' + @ID + ' has been imported multiple times ' + ' on ' + cast( getdate() AS varchar(30)) + '. You need to remove the record and rerun the import.',
'Daily Import',
@ID, 'Daily Import'
FROM #SER1
View 1 Replies
View Related
Jun 1, 2007
Hello,
I can't figure out how to pass values from report parameters to a variable in custom code.
I have a Report Parameter called Parm1 of type string. It contains the following:
Label: Value:
Labell1 Value1
Label2 Value2
In custom code I have declared a variable thus:
Public Dim ParmValue As string = Parameters!Parm1.Value
What I want is this: When a user select a report, they will have to choose a parameter from a dropdown-list (created automatically when creating a paramete). The chosen parameter will be passed to the variable ParmValue which I use as one of the parametes in a text-box like this:
=code.InstanceName.MethodName(ParmValue)
InstanceName is the instance of a class from a dll I reference.
It works fine if I hard-code the paramter like this:
Public Dim ParmValue As string = "SomeString" instead of:
Public Dim ParmValue As string = Parameters!Parm1.Value
The error I get is this:
[rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
I don't know if I'm at all on the right track here.
Can someone tell me what I'm doing wrong?
Thanks
/Peter
View 1 Replies
View Related