Problem While Setting Global Params Using DTEXec Utiltiy
Jul 31, 2007
Hi pals,
I am new to SSIS.I am getting a problem while setting the global parameters.
I created a package with a single task i.e an Execute SQL task.The associated sql is
"delete from src_emp where deptno=?" and i added a new user defined variable as "dno" i.e "user:no"
which of String type with scope=<package name>.The name of my package is "test.dtsx" which is a Structured File
save in my d: directory. I harded code the value in SSIS package desiner i.e in BIDS and tested the functionality,
it is working fine. But when i remove the hard coded value and when i tried to call the dts package using the command line
utility DTExec, i am getting. Below are the different ways i tried to set the global parameters.
trial 1:
---------
D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].Properties[value]";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:06:51 AM
Warning: 2007-07-31 11:06:53.00
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].Properties[value]".
This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].Properties[value] value to 20.
Started: 11:06:51 AM
Finished: 11:06:53 AM
Elapsed: 1.094 seconds
trial 2:
---------
D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:08:04 AM
Warning: 2007-07-31 11:08:05.99
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].value". This occurs
when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].value value to 20.
Started: 11:08:04 AM
Finished: 11:08:05 AM
Elapsed: 1.078 seconds
trial 3:
---------
D:>dtexec /f test.dtsx /SET "PackageExecute SQLTask.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:09:56 AM
Warning: 2007-07-31 11:09:57.58
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "PackageExecute SQLTask.Variables[User:no].val
ue". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set PackageExecute SQLTask.Variables[User:no].value value to 20.
Started: 11:09:56 AM
Finished: 11:09:57 AM
Elapsed: 1.062 seconds
Any suggestions would be appreciated.
Thanks and Regards,
Manu
I am attempting to execute a pckg using DTEXEC. I need to set a package variable to the text value "SQLAgent". This variable, 'Caller', is scoped for the package itself. This is the command stmt:
I am receiving the following message when I execute this from cmdshell. What am I doing wrong when trying to set the variable??
Message Executed as user: SERVERNAMESYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:54 PM Warning: 2006-06-16 12:53:55.04 Code: 0x80012017 Source: PCKGNAME Description: The package path referenced an object that cannot be found: "Package.Variables[User::Caller].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set Package.Variables[User::Caller].Properties[Value] value to SQLAgent. Started: 12:53:54 PM Finished: 12:53:55 PM Elapsed: 0.125 seconds. Process Exit Code 1. The step failed.
How can I set a global var in another DTS package from inside the one I'm excuting. An example would be that I have in package #1 an ID that gets assigned, and it executes package #2 which needs the ID from the previous package.
By default the BufferTempStoragePath is mapped to the user running the package's Documents and Settings folder. This is problematic when numerous packages are running simultaneously and using this disk location (i.e. sorts), and you don't have a large disk for your C: drive.
The property of course can be changed. However the property is specific to a data flow task, so this would require developers to change the property is every data flow task of every package. Is there a global setting to change the default location that SSIS will use?
An alternative is to use configurations, however a configuration will be required for every data flow, as it is specific to the data flow task (and name of that data flow task)
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....
I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...
I keep getting the following error: "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."
Here is the script:
Public Sub Main() Dim vars As Variables ' Lock for Read/Write the variables we are going to use Dts.VariableDispenser.LockForRead("User::varFTPDestPath") Dts.VariableDispenser.LockForRead("User::varFTPFileName") Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName") Dts.VariableDispenser.GetVariables(vars)
' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE vars("User::varSourcePathFileName").Value = _ Dts.Variables("User::varFTPDestPath").Value.ToString + _ Dts.Variables("User::varFTPFileName").Value.ToString
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.
Somthing like
For each ??? in vars msgbox(???.Value) Next
One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?
I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
Here are the details:
Environment:
Windows Server 2003 64-bit (Build 3790: Service Pack 2) SSIS 32-bit & 64-bit installed SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
SSIS Package details (compiled in 64 bit)
Script tasks only Microsoft Visual Basic .NET (using TRY...CATCH block) PreCompileScriptIntoBinaryCode = TRUE Run64BitRunTime = TRUE
Execution
Batch file that uses DTExec to execute the Package.
SCENARIO I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
Wrong connection String using 32-bit Execution
While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
Wrong connection String using 64-bit Execution
While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?
P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
Sorry for the lengthy one and Thanks very much for you help in advance .
HeyLet's say I have some stored procedure that takes as a param userId and it's simple SELECT statement. I want to assign this param and value in my code how to do it ???Jarod
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Hello, Basically I am trying to do an order by statement using variables.
I have the SQL:
IF @SortOrder = 'ASC'
BEGIN
SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',
S.SpendAmount as 'T3', E.SpendAmount as 'T4',
E.SpendAmount - S.SpendAmount as 'T5',
Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100
When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100
When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
END as 'T6'
FROM DivisionData D,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E
WHERE D.Division = @Division
AND S.Code = D.Code
AND E.Code = D.Code
END
I have the params @Crit1 @Crit2 .... to @Crit6. These parameters come from a web form and can have the values 'T1' 'T2'... to 'T6' They correspond to the field names I have put in my select statement. What I want to do is use these params in an Order By statement, enabling the user to select how the fields are ordered. I have tried using something along the lines of:
ORDER BY
CASE WHEN @Crit1 = 'T1'
THEN 'T1' END
This works as long as I dont use the word DISTINCT in my select statement, otherwise I get the error:
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I do need the distinct... I was wondering if there is a simple way to get round this, or if anyone can point me in the right direction or a article/example of this?
I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?
Dim strWhere strWhere = " WHERE dbo.""User"".UID IS NOT NULL "
If Not request.querystring("EmployerID") = "" Then strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'" End If
If Not request.querystring("AccountNumber") = "" Then strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'" End If
If Not request.querystring("LastName") = "" Then strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'" End If
If Not request.querystring("FirstName") = "" Then strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'" End If
DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr")) DBCommand = New OleDbDataAdapter _ ("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)
Hi all, To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @@IDENTITY) as input for some of the other procedures.
I am getting errors when I compile the proc: Line 10: Incorrect syntax near the keyword "EXEC" Same error on Line 11...
CREATE PROCEDURE addTemplateDetail @TemplateID int, @GroupNameID int, @SubGroupNameID int=null, @MethodID int, @AnalyteID int AS DECLARE @TemplateGroupNameID int DECLARE @TemplateMethodID int SET @TemplateGroupNameID=(EXEC addTemplateGroupName @TemplateID, @GroupNameID) SET @TemplateMethodID=(EXEC addTemplateMethod @TemplateGroupNameID, @SubGroupNameID, @MethodID) EXEC addTemplateAnalyte(@TemplateMethodID,@AnalyteID)
I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors... Can somebody see what I am doing wrong? TIA.
I have a long running trigger that makes calls to several tables in order to get values for a list of parameters before doing my final INSERT statement into a different table.
One of my parameters is for a local language translation of a particular word which is stored in a table. The problem is - I do not know the name of the table until runtime when I dynamically build the name as follows:
DECLARE @SQL nVarChar(200) SET @SQL = 'SELECT @Translation = nvcLocalEventDescription FROM Monitoring.dbo.tblSignalTemplate' + CAST(@MonitoringCentreID AS nVarChar) + ' WHERE nvcEventDescription = "' + @EventDescription + '"'
If there is a MonitoringCentreID of 1234, then there will be a table named tblSignalTemplate1234 - which will contain a nvcLocalEventDescription field containing the value that I am after. Here is the code for the stored procedure...
CREATE PROCEDURE [dbo].[usp_parmsel_LocalEventDescription] @strSQL nVarchar(150), @Translation nVarChar(100) OUTPUT AS EXECUTE sp_executesql @strSQL GO
The error I get is "Must declare the variable '@Translation'" - which has thrown me a little as it declared on the 3rd line of the stored proc.
Anyone got any ideas where I am going wrong, or as usual, is there a simpler way ?
I am trying to execute a stored procedure from another stored procedure by passing parameters. any idea please. I have tried with EXECUTE PROC dbo.dxGetMemberActivityData (@author) RETURNING_VALUES RESULT
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct? Is there any other way to approach the problem?
I've got the following C# function to add a customer record to the database. The record gets added without any problems but the OUTPUT PARAMETER (Parameter[10]) is always NULL and I can't see why. I'm also using the Microsoft Data Application Block.
Here's the C# function:
public void SaveCustomer(int customerId,string customerName,string address1, string address2, string town, string county, string postcode, string webSiteAddress, string mainTelNo, string mainFaxNo)
I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages. Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app. Here is a function that I use in one of my classes: <code> Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _ ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader Dim dr As SqlClient.SqlDataReader SqlConnection1.ConnectionString = strConn prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection) Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value If Me.Rows / intPerPage > Me.Pages Then Me.Pages = Me.Pages + 1End If Return dr prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose() End Function </code> It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values. Anyone know how to do this? Thanks.
I am getting an insert error with the following SP. I don't have to pass the CampID because it is an IDENTITY field. The error says "number of supplied values does not match table definition."
Do I pass in the CampID to the SP and allow nulls? Thanks in advance
Hi all!Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.As one might guess I would like the result to be 1. What is wrong? I.e, whywont the result of the SP come back to the caller?CREATE PROCEDURE test@val INTEGER OUTASSELECT @val = 1GODECLARE @val INTEGEREXEC test @valSELECT @val
Hi all,I'd like to put together a SQL statement that will take the name of astored procedure as a param, and return that SP's parameters.I'm writing a test application, and I'd like to wrte a generator tosave myself some time, but I can't seem to figure out how to get theparams from a SP. Any help would be appreciated.Thanks in advance,Craig
I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx
which says :
" Variable
Select the check boxes to use variables to provide inputs. "
but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work
i tried the following strings in the value column:
hi HHello!, the following 3 webmethods still work after i switched them to WCF but now it never accepts my parameters. i didn't change anything in reporting services, but i have a feeling it's not sending the params to the service anymore.
is there an operation contract attribute change i need to make to require the datetime be provided by RS? here's the schema provided by my serviec wsdl (the relevant portion)
I have reports delivered by email subscription. My "date from - to" parameters are set to "default" as "=Today()" and "=Now()". Report is delivered fine but I have problems with "link" to that report (attached to email). RS don't add my default parameter values to this link so every time user click this link, report is generated with new "date from" and "date to" and I want to generate exact the same report (same parameters) as in the email.
Is there a way to send default parameter values as static values in email delivered report link ?
queryText = SELECT agvb_id as ID ,agvb_cat_id as Category ,agv_brn_id as Brand ,agvb_brn_name as BrandName ,agv_brn_area as Area ,agv_brn_sku_qty as SkuQuantity ,agv_brn_qty as Quantity ,agv_orderliness as Orderliness ,agvb_importance as Importance FROM AGENTS_VISITS_BRANDS WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id) AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id) ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.
In the data tab I'm currently using "text" for command type and :
exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?
We run std 2008 r2. I've never been able to get to my ssrs server thru ssms as u can see as part of the question atÂ
[URL] ..... but I've found workarounds to this point by knowing how to look at the rs db itself and by having access to the server itself thru rdp with an admin acct.
I'm looking at an error from a user that reminds me of what we used to call session timeouts. They would occur when the user has walked away from his desk long enough for ssrs to delete refs to the report he just ran.
I logged into the server itself hoping to find both exec and session timeout params via the rs config tool but did not see them there. What the config file is called and where i'd likely find it? Or perhaps a query I could use to see what the current values are?
