Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts

Wednesday, March 21, 2012

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying value of a variable during runtime

Greetings all,

Apologies if this question has been asked in the past but how I display the valuw of a variable during runtime?

Thanks for your help in advance.

Running in debugmode; you can set a break point and when the excution breaks; you can go to the locals window (Ctrl + Alt + V, L)to see the value of the variables at that point|||

Dear Rafael, I am fallin in love with you LOL

|||

dreameR.78 wrote:

Dear Rafael, I am fallin in love with you LOL

Glad you got it!

Friday, February 24, 2012

Display TABLE_NAME from database

Hi,

I would like to display the TABLE_NAME variable where the table.ID =@.ID

So far I got here:

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE (TABLE_TYPE ='BASE TABLE')AND (TABLE_NAME.ID = @.ID)

but the last part AND (TABLE_NAME.ID = @.ID) does not work.

Thanks,

dg

Where do you get the ID from? There is no TABLE_NAME.ID column in the view.

You could do

SELECT [Name]

FROM sysobjects

WHERE Id = @.id

|||Every table has an ID column so, I would like to display only the names of those tables where column ID is @.ID|||

I understand. For the proc to run and return the table name, you are passing the value from your application right? How are you getting the ID of the object to the application?

And there is only 1 table/object for a given ID. So if you can explain more as to what information you have and what you are trying to retrieve we can help you better.

|||This is what I have


Table1Table2Table3Table4IDEntryIDEntryIDEntryIDEntry11
1

the result would be the name of those tables where IDEntry=@.ID where in this case @.ID = 1

Table1

Table2

Table4

|||I am sorry I still did not understand your table structure. So you have 3 tables all with ID = 1? this is not possible. ID is unique across all objects in a DB. Unless you have your own Id's for each table in your user tables?|||

I have seven tables, I want to know the name of each table where in thecolumn IDEntry I have the number 22(every table has this column)...this numberis unique and can be present in every table or only in some.

I know that I can do seven selects and see if this number exists in thespecified column. If it exists I can hard code the name of that table but thisisn't a nice way of doing. I thought that perhaps there is another way.

|||

If the tables are not related, you have to do separate SELECT's. If you use a stored proc you wouldnt have to worry about round trips. If you just want to check if the value exists you can do

IF EXISTS( SELECT * From Table1 WHERE IDEntry IS NOT NULL)

BEGIN

-- do something

END

I am still not sure if I understand your question. I am trying.

|||

He wants a query that looks inside a specific database, and returns the names of all the tables that have a column named "IDEntity" in which the value "22" exists in a row in that column. I'm just too lazy to write it for him.

Best answer I have would be to run a query similiar to what he has in his first post to get a list of table names (limited by tables that also have a column named IDEntity), then build a dynamic query to check each table and generate a string in the form:

SELECT @.tablename

FROM @.tablename

WHERE IDEntity=22

UNION

...

repeat for each value returned by the original query, replacing @.tablename with a hardcoded string prior to trying to execute it.

|||

DECLARE

@.varas varchar(8000)

DECLARE

@.colas varchar(20)

SET

@.col='IDEntity'

DECLARE

@.valas varchar(10)

SET

@.val='22'

SELECT

@.var=CASEWHEN @.varISNULLTHEN'SELECT '''+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+''' FROM '+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+' WHERE '+@.col+'='''+@.val+''''ELSE @.var+' UNION SELECT '''+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+''' FROM '+c.TABLE_CATALOG+'.'+c.TABLE_SCHEMA+'.'+c.TABLE_NAME+' WHERE '+@.col+'='''+@.val+''''END

FROM

INFORMATION_SCHEMA.COLUMNS c

JOIN

INFORMATION_SCHEMA.TABLES tON c.TABLE_CATALOG=t.TABLE_CATALOGAND c.TABLE_SCHEMA=t.TABLE_SCHEMAAND c.TABLE_NAME=t.TABLE_NAME

WHERE

COLUMN_NAME=@.colAND TABLE_TYPE='BASE TABLE'

EXEC

(@.var)

That should be good for about 20 or so tables. If you are running on SQL Server 2005 or above you can change the definiation of @.var to varchar(max) and then it should work for any number of tables.

|||

Hi Motley,

This is what I was looking for.

Thank you.

Display Result in Page Header

Hi!

I've got following problem: I got a formula evaluated whileprintingrecords in page footer. It writes in a global variable the highest number on the page. When I display this variable in page footer it works fine. But I want it to appear on the page header, too! When I display it in page header, it is the old value. Of course, because the variable is written on the end of page.

Is there a possibility to say the page header should be written AFTER the Page Footer? Or that the variable shall be displayed AFTER the page footer?
I tried the EvaluateAfter() function but it didn't work. It just writes the previous value on the top of following page.

I would be glad about some hints.

Greeting and thanks,
MarkusCongratulations, you've run into one of the most unpleasant "features" of Crystal Reports - inability to display running totals in group or page headers. In fact, there are workarounds for squeezing group-level running totals into group headers (by using external views or crosstabs), but for page-level totals, I think you are out of luck.

The reason is, as you correctly mentioned, that pages are formed and all running totals are calculated AND PRINTED during pass 2, but to put the running totals into headers, pass 3 would be required. There is, in fact, pass 3, but it will not calculate anything except page count.|||Thank you for your answer. Although it's not the one I wanted to hear... :-(

Perhaps there is a possibility to locate the field at an absolute position? So I can say: "Okay, you're evaluated in page footer, but displayed 2cm away from top of page"... But I think, this is not possible, too. I've seen that the position relates on section, therefore is no absolute one...

Sad, that CR is far behind freeware tools in this topic... Had one where you could set the evaluation and print time for each field...