Passing Object Variable As Input Parameter To An Execute SQL Task Query

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


ADVERTISEMENT

Web Service Task - Passing Variable As Input

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

Input Parameter In SSIS Execute SQL Task.

Mar 3, 2007

hi

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 1 Replies View Related

Execute SQL Task With An INPUT Parameter Of Type DBTIMESTAMP

Dec 18, 2007

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

Thanks for reading this!

View 12 Replies View Related

Parameter Passing In Execute SQL Task

Mar 20, 2008



Hi All,

I have a Doubt in Parameter Passing in Execute SQL Task.

In Execute SQL Task, I have an Insert query in which I want to pass the Database Name Dynamically i.e. passing it as a parameter.

Eg.
The query is --------- Insert into [?].[dbo].[DimCurrency] values( value1, value2, value3)

I want to pass this Database name using a user variable. But I am not able to do so.

What is the catch in this, Can anybody please help me out.

Thanks & Regards,
kapadia Shalin P.

View 5 Replies View Related

Transact SQL :: Passing Parameter To Execute Task

Aug 6, 2015

In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything

create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)

[code]...

View 6 Replies View Related

Execute SQL Task Fails When Passing A Parameter Using OLE DB

Apr 26, 2008

I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.

Dan

View 6 Replies View Related

Having Issues Passing Parameter Into SQL Execute Task

Oct 12, 2007

Hi, this might be a simple one, but I have been stuck on it for days. I am just getting into SSIS and have been muddling through it for the rest of this package but I am stuck on this. I am using this SQL Execute Task to run some lookup queries and then call the sp_send_dbmail stored procedure. I have this placed in a For Each loop container. I am using a SELECT DISTINCT Branch FROM table1 into an Object parameter, and passing that into the loop container. I am then using a Input Parameter into this SQL Execute Task of type String. I have run a Script task right before this step to ensure that the variable is populated and correct. Any assistance would be greatly appreciated!

Here is the SQL Execute Task :

DECLARE @SQL varchar(2400), @emaillist varchar(200),
@branchMgrEmail varchar(100), @officeMgrEmail varchar(100),
@branchMgrEmpNo varchar(5), @officeMgrEmpNo varchar(5), @subjectline varchar (100),
@Today varchar(10), @BranchNumber varchar(2)
SET @BranchNumber = ?
SET @Today = convert(char(8),getdate(),1)
SET @SQL = 'SELECT rtrim(CONVERT(char(10), PostedDate, 101)) AS Posted_Date,
CAST(Branch AS CHAR(2)) AS Branch,
CAST(Department AS CHAR(2)) AS Department,
CAST(InvoiceNumber AS CHAR(7)) AS Invoice_Number
FROM onbase.dbo.MHC_IncompleteRepairOrders
WHERE Branch = ' + @BranchNumber +
'AND HardCardCount = 0
AND WorkAuthCount = 0
AND QualityControlCount = 0
AND MiscDocsCount = 0'
SET @subjectline = @Today + ' - Repair Order Validation Notification for Branch #' + @BranchNumber
SET @branchMgrEmpNo = (SELECT branchempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @officeMgrEmpNo = (SELECT officeempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber)
SET @branchMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @branchMgrEmpNo)
SET @officeMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @officeMgrEmpNo)
SET @emaillist = @branchMgrEmail + '; ' + @officeMgrEmail
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@email.com',
--@recipients = @emaillist,
--@copy_recipients = 'email@email,
@attach_query_result_as_file = 0,
@subject = @subjectline,
@body = 'This email contains a list of Stuff

',
@query = @SQL,
@query_result_header = 1,
@query_result_separator = '|',
@query_result_width = 150,
@exclude_query_output = 1,
@profile_name = 'MAIL'
END



This is a copy of the ERROR message that I am receiving. Any suggestions?


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: Send email to Branch Mgr and Office Mgr

View 11 Replies View Related

Problem When Passing Parameter To Execute SQL Task

Oct 18, 2006

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?



Thank you!

View 10 Replies View Related

Passing Parameter To SQL Task Variable

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

Passing Parameter To Kill Staement In Execute SQL Task

Oct 18, 2007



Hi,

I have an Execute SQL Task that contains the following line:

kill ?

I have a variable (Spid) that I would like to pass as a parameter.

The connection is OLEDB.

I have tried naming the parameter 0, 1 but to no avail

I have also tried:

kill @spid

and named the parameter @Spid. This also does not work.

The variable contains a value obtained from a query that returns a spid for a locked table and is type INT32.

Can I even use the kill statement in such a context?

View 4 Replies View Related

Integration Services :: How To Access Object Type Variable In Execute Task

Nov 18, 2015

In my ssis 2012 package, I have a 'object' type variable with some table like records. I want to do some SQL operations like insert/update on the records in another table based on this 'Object' type variable records. Basically I want to use a MERGE statement with another physical table with the records in the 'Object' type variable.how to map/use the Object type variable in Execute sql task.I am not good in script task. How to utilize this Object variable in a Execute sql task?  

View 9 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

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

Passing A Variable Value (Package Scope) To A DTS Package Embedded Into Execute DTS 2000 Task

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

Passing Path As Input Parameter

Jan 24, 2007

Hi

I have created one SSIS package which extract data from database and put that into verious text files like Emp.txt,Add.txt like that.
Also I set one globle veriable (CityID) that help in extracting data as citywise.
When I ran it through command prompt by passing globle veriable to it like
C:setup pcaSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Data>DTExec /FILE Package.dtsx /SET Package.Variables[CityID].Value;100

it gives me data whose CityID is 100 and format it into text files.
but when i want data for another CityID (101) it overwrites my all previous text files where i kept that files.

Now my requirment is that i want to set another globle veriable that take PATH as input parameter and place these files in that path location.(How i set this path in command promt and also in SSIS)

Please guide me

Thanks

View 1 Replies View Related

Getting Object Reference Not Set To An Instance Of An Object In Execute SQL Task

Jun 6, 2007



When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:



"Object reference not set to an instance of an object"



Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:



1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:



create proc usp_testsp

as

begin

select 'whatever' ;

end



2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None



When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:



Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.



And here's what I get with ResultSet set to Single row:



Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".



I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.



Thanks,

Michael





View 3 Replies View Related

EXECUTE SQL TASK --&&> Object Reference Not Set To An Instance Of An Object

Jun 27, 2007






Hi all,

Does anyone see the error below before?
I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.
It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY
€œObject Reference Not Set to An Instance of an Object€? when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.
There are other stored procedures of different kinds and they all worked.
But all of them give this error when I click on PARSE QUERY.




Code Snippet
DECLARE @TodayDate datetime
SET @TodayDate = GETDATE()

Exec dbo.updDimBatch
@BatchKey = @BatchKey,
@ParentBatchKey = @ParentBatchKey,
@BatchName = 'Load Customer Increment',
@BatchStartDate = NULL,
@BatchEndDate = @TodayDate,
@StatusKey = NULL,
@RowsInserted = @Count_Insert,
@RowsUpdated = @Count_Update,
@RowsException = NULL,
@RowsError = NULL,
@UpdatedDate = @TodayDate,
@BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''






I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.









View 2 Replies View Related

Passing Table Object Into A Variable

Jul 5, 2007

Good Day guys, sorry i'm just new in SQL Server.

My problem is like this:

I want to pass Table Object to a declared variable and make View.

Usually we make like this:

"Select * from Table1"

I want like this:

"Select * from @Table1"

I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.

Please help me guys, i really appriciated everything.

Thanks,

Ero-Sennin26

View 1 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

How-to Execute SSIS Package With Input Parameter?

May 19, 2006

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,

Vitaliy

View 3 Replies View Related

Passing SqlDataSource Object An Array As A Parameter

Feb 22, 2007

Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
 
 
        DataTable dt = new DataTable();        DataTable dt2 = new DataTable();        DataView dv = new DataView();                        
       foreach(ListItem liOptions in ListBox1.Items)       {             if(liOptions.Selected)             {                                      SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);                   dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);                   dt2 = dv.Table;                   dt.Merge(dt2);                   dt2.Dispose();                   SqlDataSource1.SelectParameters.Clear();             }       }
        if (dt.Rows.Count > 0)        {           Graph(dt);                             //Pass original datatable (dt) to Graph();           dt.Columns.RemoveAt(2);      //Reformat datatable (dt) and remove last column before binding to Gridview1
           GridView1.DataSource = dt;           GridView1.DataBind();        } else {
    errorMessage.Text = "No data was returned!"; }

View 3 Replies View Related

How To Use A Variable From The For Loop Container As A Input Parameter To A SP

Nov 6, 2006

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

 

MA

View 1 Replies View Related

Execute SQL Task - Passing Variables

Aug 28, 2007

how can you pass variables from one 'Execute SQL Task' to another?

View 9 Replies View Related

Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.

Dec 2, 2005

I have a SQL Task that calls a stored procedure and returns an output parameter.  The task fails with error "Value does not fall within the expected range."   The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms]             @InParm INT ,             @OutParm INT OUTPUT as Set @OutParm  = @InParm + 5   The task uses an OLEDB connection and has a source type of Direct Input.  The SQL Statement is Exec TestOutputParms 7, ? output    The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm  

View 7 Replies View Related

SqlDataSource.Update With Session Variable As Input Parameter

May 25, 2007

I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID".  Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
 
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>

View 1 Replies View Related

Passing Parameter Value To Variable

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

Execute SQL Task : Input And Output Parameters In Tsql Stataments With ADO.NET Connection Type

Jan 2, 2007

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

Thanks



 

 

View 11 Replies View Related

Passing Global Variables From A Execute Package Task

Apr 28, 2004

I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?

View 6 Replies View Related

Passing Parameters To SSIS Execute Process Task

Feb 20, 2008

Hi

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.

Thanks,
Madhu

View 1 Replies View Related

Passing Variables As Arguments To Execute Process Task?

Nov 9, 2007

I am running my "execute process task" in a foreach loop that uses two variables (file paths) from the recordset that feeds into it.

My executable line looks like this: C:Program FilesWinZipWZZIP.EXE ,
which runs the command line Winzip executable and my argument line looks like this: -rp @ZipArch @ZipTxt.

When I replace the variables with literal strings, the process works (only once, of course).

My Argument won't parse in the expression editor, nor does the process run when embedded in the foreach loop. What am I doing wrong? Thanks for your help.


View 12 Replies View Related

Execute SQL Task Passing Parameters To A Restore Command

Mar 22, 2007

Hi,

I'm very new to SSIS and I€™m trying to do the following in a SQL task

RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10

I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?

Many thanks

Martin



View 4 Replies View Related

Passing A Parameter To A SQL Task

Nov 5, 2007



Hi


The genereal Properities of my SQL Task are

ResultSet : None
Conection Type : OLEDB
SQLSourceType : Direct Input

SQL Statement :



Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?

BypassPrepare : True

Parameter Mapping
Variable Name Direction Data Type Parameter Name
User::Variable2 Input LONG 0

When executing the SSIS Package I get the Following Error

SSIS package "Test.dtsx" starting.

Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query
"Update NewFile

Set CompanyID = 'S',

CompanyName = 'SA',

CustomerName = 'SA TEST',

CustomerCode = ?

" 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.

Task failed: Update Company ID and Name

SSIS package "Test.dtsx" finished: Success.

Please Help

Many Thanks In Advance
Que



View 6 Replies View Related

Not Executing SSIS SQL Task By Passing Variable

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







Copyrights 2005-15 www.BigResource.com, All rights reserved