Edit and Continue while Debugging Script Task

Hi all,

I have a simple question: is it possible to use Edit and Continue feature when debugging a script task in VSA?

It seems to me that after breaking the execution of the script task and adding some code in break mode, whole edit disappears after leaving VSA environment.

I have enjoyed the Edit and Continue feature when developing in VBA, so I am wondering if it is available in VSA.

Thank you very much for the answer!

Marek Stefanek

Hi Marek,

Though you can re -debug from your edit point, SSIS is designed not to allow changes to be saved during runtime. So better copy all the changes you do into an editor before exiting from the Debug mode

Thanks

Subhash Subramanyam

|||

Thank you, Subhash.

Although it is not a good news for me, at least it saved me from time consuming searching on the web :-)

Regards,

Marek

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
Bob
Step-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */
|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

Dynamically Creating "Features" Grid

I was given what at first seemed a simple task (and maybe it is, but with everything else on my plate, I can't seem to get my head wrapped around this)...create a grid or table something similar to below:

Plan A Plan B PlanC PlanD

Item1 yes yes no no

Item2 yes yes no no

Item3 yes no yes no

etc....

The "plans" are stored in one db table, the "items" in another. What I can't seem to get straight in my mind is how to relate the yess and nos and then generate a tabular layout similar to the above.

I don't need anyone to do it for me, but a push in the right direction would sure be nice (using SQL 2005) Thanks!

Try googling for SQL Server 2005's PIVOT and UNPIVOT functions.

Here's onearticle I found that should point you in the right direction.|||Yes...a day away and a quick read of the link you provided cleared the cobwebs. Thanks!

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

Dynamically changing task name displayed within ForEach Loop Container

Does anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.

What? Why would you want to do this? The fact that the colors change is just a pretty *debug feature* and means nothing in the execution of the package.|||I know that. I have a loop that executes through a few hundred iterations and runs for 30 minutes or so. I'd like to review how far in the process it is while it runs.|||Can you write a counter to a log file or something?|||

You can create checkpoints or if that does not fit your needs; you could write to a file or table on each iteration an the look at that. if you are interacting with a DB you may use a DB profiler to monitor the activity. Or try to loop only a few times until you get confident with the package result.

Rafael Salas

|||

No, you can't change the task name during execution.

You can log to the log output window though and only log the increment count, that would be the equivalent.

|||Thanks to everyone for the alternative suggestions. Thanks to Kirk for the definitive answer.

Dynamic XSD Schema Location For XML Source

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

It does allow it, but you don't set the expression directly on the component. You have to set it on the data-flow that contains the component.

Switch back to control-flow and look at expressions of the data-flow. Its self-explanatory from there.

-Jamie

|||Beautiful! Thank you.|||Thanks Jamie... just wanted to chime in and say this was really helpful - I would have never thought of looking on the properties of the data flow.

Dynamic Text Parser?

Hi Guys,

I have a script task that is supposed to read and parse a fixed width source file.

Basically, I want to make the FieldWidths dynamic so that I'll be able to reuse this package with different files. So Instead of hardcoding the field widths directly into my script task, I want it to be stored somewhere that the package can get when executions starts. Is there a way of doing this?

The code looks like this:

Using Reader As New TextFieldParser(mTempFilePAth)

Reader.TextFieldType = FieldType.FixedWidth

Reader.SetFieldWidths(1, 8, 8, 8, 4, 8) <-- I want to change this to handle dynamic widths.

Hi,

The best way to make your package re-usable is to use a "Integration Services Variable", see this link for help on variables http://msdn2.microsoft.com/en-us/library/ms141085.aspx and this one for accessing from a script http://msdn2.microsoft.com/en-us/library/aa337079.aspx.

The main gotcha is you need to be aware of is this bit from the second link "You can make existing variables available for read-only or read/write

access by your custom script by entering comma-delimited lists of

variables in the ReadOnlyVariables and ReadWriteVariables fields on the Script page of the Script Transformation Editor."

Also does SetFieldWidths take a variable number of arguments? If so you may need to define a string SSIS variable with the arguments comma seperated, then parse them into the seperate arguments for SetFieldWidths

Dave

Dynamic SQL task

I want to make my SQL task dynamic i.e I want to accept the Connection
String through variable i pass to the package I dont intend on using a
configuration file.How do i go about doing this and what all variables do i pass
to do this.
Thanks
Clayton
P.S How can we assign Connections dynamically to a OLEDB Source by passing values from a list of variables.

Running the package using dtexec you can update the value of the connection string by using the Set command. The Books Online topic dtexec utility contains information about using the set option. You cannot update property expression by using the set option.

Another option is to use the Script task to access the connection strings which are stored outside the package. For example, a database table. The script could retrieve the string and update the connection string.

Marianne
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic SQL in SSIS

Hi All,
I am new to SSIS. I want to be able to write a dynamic SQL statement for a Data Flow task. It would go something like this. I want to schedule an SSIS package to run everynight and extract data from an ODBC source and collect the previous day's sales info based on a date parameter, so the date parameter of the query would have to get a value based on the previous day's date
EX: SELECT * FROM Sales WHERE Invoice_Date = <previous day's date>
So far I am looking at a Script Component to do this and populate the Data Source, but I just wanted to check and make sure there isn't a better or more efficient way.

The previous day could be a simple getdate()-1 in your query however assuming you need something more complex, use the following type of things.
1) Create a variable, i.e. Qtr, String
2) Create a execute sql task or a script task to set the variable value. i.e. for SQL task something like, select cast(c.Current_Qrtr_yyyyqq as nchar(6)) as Qtr from dbo.Constants c with(nolock) and use the result set to pass the value to the variable Result set = Qtr, Variable name = User::Qtr
3) Edit the DataFlow task property, Add an expression for the SQL Command, something like your SQL between double quotes with the variable where you need it, i.e. "SELECT cast(Sum( Case when DECODE ( SIGN( NVL(B.RSD_DT,B.MSD_DT) - (Sysdate-1)), -1, 0,
Decode(B.RSD_YYYYQQ_NUM,0,B.MSD_YYYYQQ_Num, B.RSD_YYYYQQ_Num) ) = " + @.[User::Qtr] +"
then B.NET_AMT else 0 end) as varchar(50)) Current_Qtr_Net_Amt from dataware.mytable B"

Remember that you have to generate all the column mappings before implementing the expression. Also, when you build the package, the original query in the data flow get stripped out of the hard coded values that your expressions replaces, so if you want to change your metadata, you have to manually put back the values in the dataflow source item.

Hope it helps,
Philippe

|||Using dynamic SQL in an OLE DB Source component - http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie