Not sure if this is a "user training" issue or a bug, I seem to have difficulty dragging and dropping variable(s) to the watch window. Is there a specific sequence I need to follow to enable this feature in BIDS? Appreciate any feedback.
View 5 Replieshi frnds,
i want to view the runtime variable value in the watch window while the package is running,
how i will get that watch window. i searched all the menus but i didnt get it.
im using Microsoft Visual Studio 2005 for SSIS package.
I honestly don't see where to view the watch window. Can someone please advise.
Thanks
hi frnds,
i have one SSIS package with single SQL Execute task.
select empname from emp
This is the query i had mentioned in that SQL task.
i had created the varibale User:rs as object and i mapped that to the resutset and i selected fullresultset in general tab.
In the debugging mode im getting the com object as the result of that variable.
This is the o/p which i got in the debugging mode.
- User::rs {System.__ComObject} __ComObject
Type __ComObject
Value System.__ComObject
but i requires the empname to be displayed in that mode.
how to get that?
pls tell me .
between my wrox book, google and looking everywhere in BIDS, I couldn't figure out how to watch the variables being set in an Exec SQL Task control flow item. My wrox book said something about right clicking the var in the script (I wasnt in a script), or using the debug menu or pressing ctrl-alt-q. None of this worked. Is it possible that at installation a certain choice wasnt made, or maybe watch windows only work in the context of something other than the control flow?
View 9 Replies View RelatedHello All,
I am wondering if someone can shed some light on how to display "Variables" window in Integration Services. I am trying to add a new variable by clicking SSIS menu, and choosing Variables. However, the Variables window never showed up. Is there some property settings that I need to set before I could view the Variables window?
Your help is much appreciated!
-Lawrence
I'm not sure if this is the right forum, but i'm having an issue where SSIS Variables window will not come up. The most i get is a gray bar that looks like it is supposed to hold the variables.
I've tried getting it to show by:
1) Menu --> SSIS --> Variables
2) Right Click --> Variables
3) Menu --> View --> Other Windows --> Variables
The same thing seems to happen for "log events" in Menu --> View --> Other Windows --> Log Events.
Does anyone know how to fix this? Is anyone else having this problem?!?!?!
-Thanks Thames
I saved a stored procedure (see below) and I'm not seeing listed within the 'Databases' / 'Remote_Serials' (DB name) / 'Programmability' / 'Stored Procedures' folder within the 'Object Explorer' window.
I'm trying to perform a simple add-info-from-form-into-DB process.
Is it because of the way I put together the procedure? I have a feeling that it is (was going off a video demo, which created it within VB.NET 2008 Express, but I'm not able to connect to the DB from there; it says the file is open, when I completely closed out of SQL Management Studio Express).
Code Snippet
ALTER PROCEDURE dbo.spInsertSerialInfo
@EmployeeID as nchar(10),
@First_Name as nvarchar(50),
@Last_Name as nvarchar(50),
@HAddress as nvarchar(50),
@City as nvarchar(30),
@HState as nvarchar(2),
@Zip as nvarchar(10),
@Phone_Home as nchar(15),
@Phone_Cell as nchar(15),
@Monitor1 as nvarchar(50),
@Monitor2 as nvarchar(50),
@PIX_ASA_Box as nvarchar(50),
@System_Case as nvarchar(50),
@Batt_APC as nvarchar(50),
@current_count as int OUTPUT
AS
INSERT RemSerials (EmployeeID,
First_Name,
Last_Name,
HAddress,
City,
HState,
Zip,
Phone_Home,
Phone_Cell,
Monitor1,
Monitor2,
PIX_ASA_Box,
System_Case,
Batt_APC)
VALUES (@EmployeeID,
@First_Name,
@Last_Name,
@HAddress,
@City,
@HState,
@Zip,
@Phone_Home,
@Phone_Cell,
@Monitor1,
@Monitor2,
@PIX_ASA_Box,
@System_Case,
@Batt_APC)
SELECT @current_count = Count(*) From RemSerials
When trying to access the stored procedure from VB.NET 2008 Express, it says, "Could not find stored procedure 'spInsertSerialInfo'."
I'm listing the code as below.
Code Snippet
cmd.CommandText = "spInsertSerialInfo"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.Add("@EmployeeID", SqlDbType.NChar, 10).Value = txtEmployeeID.Text
If I change the 'CommandType.StoreProcedure' to 'CommandType.Text' it seems to find it, but then I get another error saying that the @EmployeeID variable has already been delcared and that I need to have a unique variable.
Any help would be greatly apreciated
I'm writing my first SSIS custom task. I have added Public properties, which appear in the standard task properties window, and to one of them I have added an EditorAttribute as follows:
<Category("General"), _
Browsable(True), _
EditorAttribute(GetType(UIFileNameEditor), _
GetType(System.Drawing.Design.UITypeEditor))> _
Public Property FilesToArchive() As String
[Code] ....
When I select the FilesToArchive property in the Properties window, I get an ellipsis that appears in the property value. When I click on the ellipsis, it brings up the FolderBrowserDialog, which is defined in the UIFileNameEditor class. This all works fine.
What I want to do is to bring up the EditorUI, the one you get when you double click on the task, and also populate that with properties which can be edited. I have a class which inherits DTSBaseTaskUI, which is displayed when I double click on the task.
I can also get properties to be displayed in that UI but I cannot get them to be editable in that UI, using the same technique as in the standard Properties window, as described above.
I am trying to install the MS SQL Server 2012 Failover Cluster on Windows Server 2012 . I successfully Installed the Failover cluster instance on my primary node.
But when I am running the installation process on passive node to add node in the failover cluster I am stuck with very unique kind of issue.
I am following the Standard process of Installation and I am getting the same windows for each next process, but after License Agreement window when I get the Cluster Node Configuration window, then in "SQL Server Instance Name" drop down box I am not getting the Name of Instance which is already installed on the primary node. But this Instance complete information is appearing in below given box.
Only issue is Instance name is not appearing in the drop down list, that's why I am not able to select and when I click on next it trough error and do not proceed.
@counteft = (@countone + @counttwo)
Does this work in sql?
The Yak Village Idiot
I am trying to wrtite a qurey that will return multiple rows as one varchar(8000). But I am howing issues when trying to add two varchar(8000) variables together. During the loop through the cursor it will just stop adding the two varchar(8000) variables together. Depending on which record that I run the query against it will stop at different point. I have run sql traces and can see that it does finish looping through the cursor completely.
I am currently just testing this and it will be placed into a stored procedure to be call from with an application later.
Any help is appreciated.
declare @ticketnum as int, @mystring as varchar(8000), @myreturn as varchar(8000)
set @ticketnum = 2
set @myreturn = ''
declare details cursor for
select convert(char(25), [Date]) +
convert(varchar(255), [Description]) +
char(9) +
[Login ID] +
isnull(char(10) +
char(9) +
replace(convert(varchar(500), [Note]), char(10), char(10) + char(9)), '') +
char(10)
from [_SMDBA_].[Incident Details]
where
[Incident #] = @ticketnum
order by [Sequence] asc
open details
FETCH NEXT FROM details
INTO @mystring
WHILE @@FETCH_STATUS = 0
BEGIN
set @myreturn = @myreturn + @mystring
FETCH NEXT FROM details
INTO @mystring
end
CLOSE details
DEALLOCATE details
select @myreturn
Example of what the query returns.
Jul 16 2003 1:54PM Opened CallMAGIC
I want to
see multiple lines
Jul 16 2003 1:54PM Call Taken By MAGICMAGIC
Jul 16 2003 1:54PM The Clock has been started MAGIC
Jan 15 2004 2:00PM User Defined Statu
What it should have returned.
Jul 16 2003 1:54PM Opened CallMAGIC
I want to
see multiple lines
Jul 16 2003 1:54PM Call Taken By MAGICMAGIC
Jul 16 2003 1:54PM The Clock has been started MAGIC
Jul 16 2003 1:54PM The Clock has been started MAGIC
Jan 15 2004 2:00PM User Defined Status Changed To CLOSEDMAGIC
Jan 15 2004 2:00PM Close Call # 1MAGIC
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
I am using Visual Studio 2005 to create a report from a OLAP cube.
I am building a drop down list for user to select the desired branch. My mdx query as follow:
with
member [Measures].[ParameterCaption] AS '[Company].[Branch].Currentmember.Member_Caption'
member [Measures].[ParameterValue] AS '[Company].[Branch].Currentmember.Uniquename'
select
{[Measures].[ParameterCaption], [Measures].[ParameterValue]} on columns,
{[Company].[Branch].ALLMEMBERS} on rows
from [Profit And Loss]
I would like to add a blank row in the result set, such that I can consider that as selecting ALL Branch
Any suggestions on what should I do?
Hi...
Am working with asp.net with vb for a MusicProject..
In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button....
Here He can insert those songs into a new playlist or update the earlier..
the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing
***************************************************************************************
CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON
DECLARE @IDENT INT
IF @PLAYLISTTYPE = 'N'
BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID )
SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E'
BEGIN
SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME
END
DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item)
INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END
GO
***************************************************************************************
Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist..
Please help me out
Thanks in Advance,
Madhavi
I was trying to write an expression someting like this.
(CASE WHEN (GroupVar2 IN('CBank','DTC', 'EDirect')) THEN GroupVar2 ELSE 'InstLend' END) AS COALESCE(GroupVar2,'Total') AS GroupVar2
In GroupVar2 column, following values are available;
CBank
DTC
EDirect
InstLend
Inst-Load
I use this for a parameter in my report. I want to consider inst-Load as the same as InstLend. In drop down menu , I should see only InstLend. When I select it, I should get summation of InstLend and Inst-Load.
Also I should see 'Total' as one of the available value. So when I select total it should give me summation of all of above.
Can anyone help me to write this corretly?
Thanks
I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
[Code] ....
I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
UserIds
----------
UserId - int, primary key
Username etc
Contacts
-------------
UserId - int, the UserId of the user who has the contact
ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
Nate Hekman
We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.
Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?
I am writing my first custom SSIS task and I can see that, if I put a public property into the task, I see that property in the standard Properties window. If I add a property of type String, I can put a value in that property, in the task code, and when I instantiate the task in a package, I can see the value I entered.
To try and get a drop down list property in the Properties window, I declared a property of type Combobox, and indeed a drop down list appears in that property.
My problem is trying to get values in that property. I have used the test items:
Property.Items.add("Fred")
Property.Items.Add("Jim")
But I do not see the values in the drop down list. All I do see is one item with a value of "(none)".
All,
I cannot use the watch window while debugging. I want to use it to look at the runtime variable values. The watch window is blank when I click on it, could someone please tell me where and how to configure it?
Many thanks
I need an example of a WQL query to use in the WMI Event Watcher task to watch for the presence of a file. The task has to succeed for either:
1) The file already exists when the task is run
2) The file shows up while the task is running (waiting)
Also the query has to reference a UNC path and file name. The file I'm looking for is just a flag file telling me a table is refreshed in our Data Warehouse.
I also need pointers on how to set the WMI connection manager. For the server, do I use the \servername of the fileserver? Do I need to include the folder the files are in (eg. \servernameflags)?
Thanks!
Kory
Is there a way i can see the value of the variables during debugging ?
View 10 Replies View RelatedI have a folder that gets 2 to 3 files dropped everyday. I want to create a package that loads only the new files into the database. The old files (Processed files) remain in the same folder and cannot be moved. How do i accomplish this in ssis?
I found something about the file watcher task with the for loop, but just can't put it all together.
Any help would be appreciated.
Thanks
Dear members,
In MSDN, it says that it is recommended to use windows authentication to connect to SQL Server rather than use mixed authentication.
I create user deltasqluser on windows OS, and I specify in my webform ASP.NET script below :
protected System.Web.UI.WebControls.Label Label1;
private string _connString = @"data source=deltasql2000;initial catalog=northwind;integrated security=false;user id=deltasqluser";
/*
comment : I login to my windows as deltakoronx, and I want to every user (including me), connected to sql server through IIS, will be identified as deltasqluser not as user's login (impersonate)
*/
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection(_connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select suser_sname()";
conn.Open();
string userName = cmd.ExecuteScalar() as string;
conn.Close();
conn.Close();
Label1.Text = userName;
}
at web.config, I add :
<identity impersonate="false" userName="deltasqluser" password="" />
at IIS webApplication1's properties, tab "Directory Security", at "Authentication and access control" section, I checked "enable anonymous access" with user : DELTAIUSR_DELTA and checked "Integrated Windows Authentication",
at query analyzer, I login as "sa" and execute script below :
exec sp_grantdbaccess 'deltasqluser','northwind'
when I run the ASP.NET script, error at conn.Open(); with error message :
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
What should I do so that IIS login to SQL Server as user deltasqluser not as "NT AUTHORITYNETWORK SERVICE" ?
Regards,
Koronx
Hi Guys,
I've got an order generation query that will _sometimes_ run long. By that I mean every few days a query used to estimate the required stock quantities for an order will time-out because it has taken an extended time to run. This only occurs at one particular site. This site shares a common schema with others who do not experience this issue, although site is a particularly busy one.
This query (a stored proc call) needs to aggregate sales data from ~200k rows and usually takes 10-15 seconds. On the days when it 'runs long' it will hit the 5 minute time-out period and be failed by the application.
The problem I have with this process is that if I have the application dump out the query it's using to generate the order (this query will vary as the stored proc is passed the name of a temp table to use for this order) so I can run it in Query Analyzer to get an execution plan and maybe some clue as to why it takes so long; the issue will vanish. After I've tried to investigate in this way an order generated from either Query Analyzer or the application will work in the normal time. When the query is running long any processes which attempts to write to the sales data tables will get blocked until the order generation has finished.
The application is accessing the database via ADO 2.6. The database itself is running on SQL Server 2000 sp3 under Win2k.
I believe that the SQL Profiler is the tool best suited to investigate this but I'm hoping for some advice on which of the myriad of watchable metrics I should be checking here.
Any profiler advice or estimates as to what the issue may be here would be great!
-Karl.
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View 11 Replies View RelatedCan't an SSIS package run "in the background", so to speak, without having either the cmd.exe or dtexecui windows open while executing? I'd obviously rather not have to have a window open when the thing is running right?
View 2 Replies View Related
Hi,
I am using SSRS Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00. I want to open linked report in new window.
I tried whats mentioned in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240172&SiteID=1 but i get an error on Window.Open method.
How do I solve the problem?
Thanks
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies View RelatedSo at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID