Getting a reference to a Script task in VB.net

I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

Imports Microsoft.SqlServer.Dts.Runtime

But I get errors saying

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?

What am doing wrong?

Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ? = ctype(t.InnerObject,?)

End If

Next

You cannot manipulate properties of objects in the package directly from script within the package. Period.

What you CAN do is put expressions on properties which affect them at execution-time.

-Jamie

|||

I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.

I have figured it out.

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

Module SSISPackage

Public Sub main()

Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"

Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)

Dim cp As ScriptTaskCodeProvider = sct.CodeProvider

Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"

Dim code As String = cp.GetSourceCode(Moniker)

End If

Next

But still does not seem to show the user text added by the developer? Strange.

|||

I had to add a reference to:

Microsoft.SqlServer.ScriptTask

Microsoft.SqlServer.VSAHosting

to my project and then this would work:

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

|||

Ah OK. Sorry, my bad!

Glad you got it working.

-Jamie

sql

Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function

Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

GetBlobData method fails

Hi,

I have s Script Component, that retrieves data from NTEXT column using this code:

Dim b As Byte()

If (Row.OutputXML.Length > 0) And (Not (Row.OutputXML_IsNull)) Then

b = Row.OutputXML.GetBlobData(0, CInt(Row.OutputXML.Length))

End If

I′m getting this error:

[Script Component 1 [838]] Error: System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

OutputXML column is filled by Ole Db Command from an output parameter (nvarchar(max)) of a stored procedure. In management studio sp works fine, even Execute SQL Task returns correct (but truncated) data.

Please, help!

what is the SSIS data type (not the original SQL data type) assigned in OleDB Command to OutputXML column?|||data type is DT_NTEXT

Get table warnings?

Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
MikeThough SQL Server lets you create a table with row size > 8060, you are
still limited by the size 8060. So, your table is just like any other table,
and SQL Server will not let you have a data row that exceeds that limit.
The DBCC commands you are talking about are mostly for checking database
consistency.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uHy50X25DHA.1368@.TK2MSFTNGP10.phx.gbl...
Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
Mike

Get table warnings?

Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
MikeThough SQL Server lets you create a table with row size > 8060, you are
still limited by the size 8060. So, your table is just like any other table,
and SQL Server will not let you have a data row that exceeds that limit.
The DBCC commands you are talking about are mostly for checking database
consistency.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uHy50X25DHA.1368@.TK2MSFTNGP10.phx.gbl...
Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
Mike

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanks
Hi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>
|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.
|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>
|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,
|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:
> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:
> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>> Hi,
>> SQL Server 2000. How do I get the filename of the script currently being
>> executed from within the .SQL script itself?
>> Is there a special variable containing the filename of the .SQL script,
>> or
>> another method of obtaining it?
>> thanks
>>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:
> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
> > osql -U sa -i test.sql
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > Hi
> >
> > I am not quite sure what you are wanting, but the filename is really only
> > known by the application and AFAIK will not get passed to the server. If
> > you
> > are running the command from a command line or batch file you could use
> > command prompt variables to substitute into a script something that is
> > sent
> > to the server.
> >
> > If you expand and post some example of what you are trying to do it may
> > help!
> >
> > John
> >
> > "JJ Williams" wrote:
> >
> >> Hi,
> >>
> >> SQL Server 2000. How do I get the filename of the script currently being
> >> executed from within the .SQL script itself?
> >>
> >> Is there a special variable containing the filename of the .SQL script,
> >> or
> >> another method of obtaining it?
> >>
> >> thanks
> >>
> >>
> >>
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
> > Hi, thanks for your reply. Here's a simple example. test.sql contains:
> >
> > print 'Script: test.sql'
> > select @.@.version
> >
> >
> > Execute it using osql:
> >
> > > osql -U sa -i test.sql
> >
> >
> > Instead of hardcoding the script filename within the script, I want to get
> > the script filename programmatically within the script itself, e.g. via SQL
> > statement/command or otherwise. I've got dozens of .sql scripts and want a
> > general method to output the filename of the script (it doesn't matter if
> > the filename printed includes the whole folder path or not) and would rather
> > not hardcode the print statement in each script.
> >
> > Hope that clarifies what I want to do.
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > > Hi
> > >
> > > I am not quite sure what you are wanting, but the filename is really only
> > > known by the application and AFAIK will not get passed to the server. If
> > > you
> > > are running the command from a command line or batch file you could use
> > > command prompt variables to substitute into a script something that is
> > > sent
> > > to the server.
> > >
> > > If you expand and post some example of what you are trying to do it may
> > > help!
> > >
> > > John
> > >
> > > "JJ Williams" wrote:
> > >
> > >> Hi,
> > >>
> > >> SQL Server 2000. How do I get the filename of the script currently being
> > >> executed from within the .SQL script itself?
> > >>
> > >> Is there a special variable containing the filename of the .SQL script,
> > >> or
> > >> another method of obtaining it?
> > >>
> > >> thanks
> > >>
> > >>
> > >>
> >
> >
> >|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.
> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>> Hi
>> If you had two files script.sql and sript1.sql you could do something
>> like:
>> script.sql
>> PRINT 'Script Script1.sql'
>> :r Script1.sql
>> script1.sql
>> SELECT @.@.VERSION
>> Then run script.sql
>> osql -E -S (local) -d master -n -i script.sql -o script.out
>> If you want to do this for multiple files you could create the file and
>> then
>> run it something like:
>> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>> >>
>> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d
>> Master -n -i
>> script.sqf > script.out
>> If you have a large number of scripts it may be useful to limit how many
>> are
>> run at any one given time.
>> John
>>
>> "JJ Williams" wrote:
>> > Hi, thanks for your reply. Here's a simple example. test.sql
>> > contains:
>> >
>> > print 'Script: test.sql'
>> > select @.@.version
>> >
>> >
>> > Execute it using osql:
>> >
>> > > osql -U sa -i test.sql
>> >
>> >
>> > Instead of hardcoding the script filename within the script, I want to
>> > get
>> > the script filename programmatically within the script itself, e.g. via
>> > SQL
>> > statement/command or otherwise. I've got dozens of .sql scripts and
>> > want a
>> > general method to output the filename of the script (it doesn't matter
>> > if
>> > the filename printed includes the whole folder path or not) and would
>> > rather
>> > not hardcode the print statement in each script.
>> >
>> > Hope that clarifies what I want to do.
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>> > > Hi
>> > >
>> > > I am not quite sure what you are wanting, but the filename is really
>> > > only
>> > > known by the application and AFAIK will not get passed to the server.
>> > > If
>> > > you
>> > > are running the command from a command line or batch file you could
>> > > use
>> > > command prompt variables to substitute into a script something that
>> > > is
>> > > sent
>> > > to the server.
>> > >
>> > > If you expand and post some example of what you are trying to do it
>> > > may
>> > > help!
>> > >
>> > > John
>> > >
>> > > "JJ Williams" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> SQL Server 2000. How do I get the filename of the script currently
>> > >> being
>> > >> executed from within the .SQL script itself?
>> > >>
>> > >> Is there a special variable containing the filename of the .SQL
>> > >> script,
>> > >> or
>> > >> another method of obtaining it?
>> > >>
>> > >> thanks
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help
!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQ
L
> statement/command or otherwise. I've got dozens of .sql scripts and want
a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rath
er
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like
:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and th
en
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n
-i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many a
re
> run at any one given time.
> John
>
> "JJ Williams" wrote:
>|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>

Get Previous step result in an SSIS Script Task

Hello,

I am using SQL Server 2005 Integration Services to create new values for my tables. One step I must do is execute a query and the script task that receive its constraint (it is set to completion) must do different things depending on the query result.

My question is: how can I know the result of the precedence constraint?

Thank you,

Pablo Orte

If a task executes then it matched your precedence constraint. If you say "Complete" and "VarA == 20", and it executes then you can be assured that was the result. Are you wanting to do branching, or have multiple precedent constraints?|||

The key point to what I think Sean is suggesting, is that if you can get a variable to hold the result, you could use an expression to influence the constraint, whether it is satisfied or not. Expression support on a constraint is very useful for influencing workflow.

I am not sure that this is useful to you, since you have to populate that variable somehow. One way would be to use an on error event handler on the task. Default the variable to true, and change it to false in the event handler.

I don't know why or exactly what you are trying to do, but my first reaction would be this sounds like a bad idea. I think there should be a better, more SSIS way of trying to achieve this. Forgive me, but your method reminds me of the hacks we used in DTS. To conditionally do something why not use two tasks, or why not use event handlers to do an out of band type operation when something fails.

|||

This thread might have somthing for you...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1

Rafael Salas

|||

Hi and thanks for your replies,

The problem I have is that I have to do this in a script task situated after an "Execute SQL". This cannot return any parameter and I need to have something in the code like this

IF objDTSPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionResult = 0 THEN

This code worked in SQL Server 2000, but not in SQL Server 2005.

Thanks,

Pablo Orte