How to schedule package Continuoulsly?

Hi,

I have requirement that i need to Schedule package continuously(every 10 Seconds).

Any solution on this?

it would be great!

Duplicate post:

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

sql

How to schedule package Continuoulsly?

Hi,

I have requirement that i need to Schedule package continuously(every 10 Seconds).

Any solution on this?

it would be great!

H there,

I'm afraid that 2005 doesn't give you the possibility to launch processes on seconds-basis, so you could do a little vb application where inside a loop and using a Timer..:

Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package

Dim EventsSSIS As Eventos

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

while true..

pkg = app.LoadFromSqlServer("msdb\yourpackage", Nothing, Nothing, Nothing)

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

stuff..

Public Class Eventos

Implements IDTSEvents

OnPostValidate()

OnPreExecute()

..

stuff

|||Hi

I have two suggestions:
- you could reorganize your package to use an never-ending for loop, with a ~10 seconds sleep (I would try to do that first, if you have the possibility to modify the package)
- or use a scheduler (sql jobs, or third party software like nncron or cruisecontrol.net to trigger the package execution) - keep in mind that if you have to launch it every 10 seconds, it may end spending more time just starting and stopping compared to the time really used to carry out the transformations...

Thibaut|||

Sivarama wrote:

Hi,

I have requirement that i need to Schedule package continuously(every 10 Seconds).

Any solution on this?

it would be great!

Is SSIS really what you want here?

SSIS is inherently a batch tool. If you want something more real-time then perhaps BizTalk is what you're after.

-Jamie

|||Although you should have an "end process trigger", I would suggest using a for task, place all your stuff in it and just say while @.endtrigger = 0.

I have had a similar issue with trying to run things every 1 min, 10 sec is a little fast I think. On my server it takes an SSIS job 1 min 34 secs just to START the job. This makes it very hard to say, look for a file over and over, until it finds it.

How to Saving Multiple ID in One Column

Dear Sir,
I need one Table Design information.
My requirement is:
I have some products (say 10 products) in my master table. Each product has
Product ID (PK).
ID Code Desc
--
1 A1 AAAA
2 B1 BBBBBB
etc. etc...
I have one interface where the user will select the products that he is
interestered with. Mean One user may select product ID 1, 3, 5 and other may
select 2, 7.
I have to save the product info selected by that user in one Column of the
user table.
(I have to save the selected product in one column only)
So What Type will be for that Column and how do i implement logic to save
all selected product so that it will be easy to save and extract the values
also.
I think I can use some binary kind of valu for each product and save the
selected binary combination or similar logic to store and retrieve.
Please suggest.
Thanks
PrabhatPrabhat
Use RDBMS. Split the User table into produtsSeclected and use usertable for
storing base information only. then you will have three tables
Products where product id is primary
users where users id is primary
and Junction Table : Products selected with foreign keys of productid and
userid. thus participating in many to many relationship.
Regards
R.D
"Prabhat" wrote:

> Dear Sir,
> I need one Table Design information.
> My requirement is:
> I have some products (say 10 products) in my master table. Each product ha
s
> Product ID (PK).
> ID Code Desc
> --
> 1 A1 AAAA
> 2 B1 BBBBBB
> etc. etc...
> I have one interface where the user will select the products that he is
> interestered with. Mean One user may select product ID 1, 3, 5 and other m
ay
> select 2, 7.
> I have to save the product info selected by that user in one Column of the
> user table.
> (I have to save the selected product in one column only)
> So What Type will be for that Column and how do i implement logic to save
> all selected product so that it will be easy to save and extract the value
s
> also.
> I think I can use some binary kind of valu for each product and save the
> selected binary combination or similar logic to store and retrieve.
> Please suggest.
> Thanks
> Prabhat
>
>|||However this is the example of bad DB design where you keep multiple
data in single column, you can make that column of varchar type and
build a string of product ID and use comma (,) separator to saperate
each Product ID.
While fetching you ll only need to split IDs from comma|||What is Bad Design ? Normalization or CSVs for OLTP:
IF you forget about Data intigrity and future intigration problems only
then you can use comma seperated values
this sipmle scenario is not possible with csv.
If you want to write a query to select particular product selected by
multiple users,
is it possible when u use CSV.
Regards
R.D
"Zero.NULL" wrote:

> However this is the example of bad DB design where you keep multiple
> data in single column, you can make that column of varchar type and
> build a string of product ID and use comma (,) separator to saperate
> each Product ID.
> While fetching you ll only need to split IDs from comma
>|||Hi R.D.,
You are right. I know the concept and I know we can use 3 tables for that.
But My question was, Is there a better way of calculation / procedure that
we can store all selected value in one column?
Also the Comma separated method is very critical, as if products will
increase we need to increase the width of the field.
Thanks
Prabhat
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:4A593B0A-B72F-4D7F-9FD4-43E851E2C70B@.microsoft.com...
> Prabhat
> Use RDBMS. Split the User table into produtsSeclected and use usertable
for
> storing base information only. then you will have three tables
> Products where product id is primary
> users where users id is primary
> and Junction Table : Products selected with foreign keys of productid and
> userid. thus participating in many to many relationship.
> Regards
> R.D
>

How to save "New" / "Updated" records only in to destination Tables

Hi

I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.

So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.

How can i do that in SSIS 2005 using different data flow tasks ?

Thanks

Kumaran

Answers are right here: http://www.sqlis.com/default.aspx?311

-Jamie

|||

Hi Jamie

As per the URL you mentioned, i tried for the results.

I don't have any problem on identifying new records and inseting in to my database table using the "Conditional Split transformation".

But How to handle my few UPDATED records ? hoc can i identify that and update only those records in to my data base Table.

Thanks

Kumaran

|||

You could use another lookup on the records that find a match which basically does the same - but looks up against ALL columns in the target. If there are any rows that don't find a match then you know something has been changed.

-Jamie

How to run profiler from cmd

Hi,

We have a requirement to run profiler continuously to find out the
misbehaving applications/SQL in the dataserver. And we are not
interested in running it from an individuals local machine. Also we
want the profiler to start automatically whenever there are SQL Server/
Machine restarts.

Is there a way to invoke the profiler instance from cmd prompt or is
there a way to script the profiler?

Thanks in advance,
Thyagu.Here are the steps that can help you accomplish what you need:

1. Define your SQL Profiler Trace and script it. A good idea to place
restrictive filters to minimize the load on the production system. Make sure
the trace target is a file, not a table as it will have significant
performance impact. To script the trace use:
- SQL Server 2000 -File -Script Trace
- SQL Server 2005 -File -Export -Script Trace Definition

2. Modify the trace script and wrap it in a stored procedure. Add a couple
parameters to pass the target trace file, source database. etc. One of the
first statements in the generated trace script is calling sp_trace_create,
where you need to replace the target trace file as a parameter. And you can
add a call to sp_trace_setfilter (for example, EXEC sp_trace_setfilter
@.TraceId, 3, 0, 0, @.DbId) to set a filter for a specific database based on
database id that you can pass to the SP (or do not set that filter if you
want to trace for all databases). Also, the database can be one of the
preset filters in step 1 above.

3. Inside the stored procedure you can record the trace id to a table, so
later on if needed you can use the SP sp_trace_setstatus to stop the trace.

4. Create a job to schedule the stored procedure. In the schedule specify to
run automatically when SQL Server Agent starts.

BTW, this is the recommended way to run traces on production systems. If you
run a trace via the GUI you are actually running two traces: one that send
the trace to the target file, and another that sends the trace data to the
client running Profiler.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

how to return more than two values from a stored procedure..

Hi,

I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.

How can I do that. I'm able to get only 1 value if i use the return key word.

Eg:

create proc test1 as

Begin

Declare scount int

Declare scount2 int

-- statements in stored procedure

return scount

return scount2

End

create proc test2

Declare variables...

Exec Test1

// here i want the values (scount and scount2 ), processed in stored procedure Test1 .

How can i get this.. please let me know..

Thanks,

srikanth

I would suggest using a function rather than a stored procedure; however, it is also possible to do what you want using a stored procedure, but you will not be able to use the RETURN verb to return the values. Give a look to CREATE PROCEDURE in books online.

An inline function to return 2 values would look something like:

Code Snippet

create function dbo.test1 ()
returns table
as
return
( select 1 as scount, -- or whatever values
3 as scount2
)

The function would be invoked with something like:

Code Snippet

select scount, scount2

from test1()

Give a look to the CREATE function article in books online.

To doctor up your procedure, try something like:

Code Snippet

create proc test1
@.scount integer output,
@.scount2 integer output
as

-- statements in stored procedure

set @.scount = 14 -- or the appropriate value

set @.scount2 = 21 -- or the appropriate value

return

To invoke the stored procedure, try something like:

Code Snippet

declare @.out1 integer, out2 integer

exec @.test1 @.out1 = @.scount output,
@.out2 = @.sctount2 output

select @.out1 = [@.out1],
@.out2 = [@.out2]

|||

during the process i have to modify data in database.so i cant use functions as they cant update the data.

hence i hav chosen stored procedure.

let me know if there is a way in stored procedure itself..

thanks for ur reply..

|||

Which stored procedure is updating the data -- the stored procedure that invokes the other stored procedure or the stored procedure that gets invoked?

|||

You can use OUTPUT parameters.

In SQL Server 2005 perhaps you may use OUTPUT CLAUSE http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1642175&SiteID=1 for your purpose.

|||

hi Kent,

thanks for your information in your previous reply.

Its working the way as you said.

Thanks,

srikanth