Showing posts with label package. Show all posts
Showing posts with label package. Show all posts

Tuesday, March 27, 2012

Delete an excel worksheet only if it exists, in a dts package

Hi :

1. I import data into sqlserver, from an excel file, from a worksheet
within it, named 'input_data'
2. then i scrub the data in sqlserver
3. then i export some data into the same excelfile but into another
worksheet named 'output_data'

I have got it working and this is how i am doing it right now. I am
having a problem in the few steps identified below :

1. I delete the worksheet 'output_data' using an execsql task
DROP TABLE `output_data'

2. Then i have another execsql task to create it
CREATE TABLE `output_data`

3. Then i have datapump task to put data into worksheet.

The work fine, but i am running into a problem, when the output_data
worksheet doesnt exist.
So i need to write a query telling
IF `output_data` doesnt exist, then DROP IT

How do i do this
I am open to doing this in ActiveX Script(vbscript) and ExecSQL task

Also i do not have excel dll or runtime in my dbserver.

Please let me know
thanks
RSapologize for typo
I do have a problem, when 'output_data' worksheet does exist
and hence need a query telling,

if output_data worksheet exists, then drop it

thank you
RS

Thursday, March 22, 2012

Delaying the Execution of a component

Hi All,

Is there a way wherein if I have three components in my package, A,B,C and I want the DFT C to be executed only after A and B is executed ?

I have now connected A and B to C and made the two arrows from A to B to blue, but i guess that is not good enough. Will setting the DelayValidation property to true help here?

Because that the DFT C is totally dependent on A and B I should somehow ensure that there is a constraint set.

Can someone guide me here ?

Thanks in advance

Mithun

Use the precendence constraints (arrow in the control flow) from A to C and from B to C ensure the task C is executed after both A and B have finished. Additionally, if you set DelayValidation property on task C to true, the task C is validated just before execution, i.e. after both A and B have finished.|||

That is gr8.. Thx for such a quick reply..

delay when running with DTexec.exe

Hi,
when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage.
the subpackage has been setup so executionoutofprocess pramater has been set to true.
any idea what migth be the problem.
I have to metion even when I run this with in the visual studio still I have a big delay.

cheersWhat is a "big" delay? SSIS has to validate the package and its connections/metadata before executing.|||it takes normally between 30 sec to one min. for it to start the sub-package. is there any way to reduce this delay by pre-validation
Thanks|||Try setting DelayValidation = True on your connection managers, sources and destinations.|||

From another thread on this forum:

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

Usually, executing using DTEXEC /F File.DTSX is the fastest way, but gives the mininum logging. Varios logging options for DTEXEC add functionality but may affect performance, although I've never seen 10x times slowdown because of it.

I have tested myself and I can tell the speed improves but just too litle.

|||Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers|||

Kolf wrote:

Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers

Validation HAS to happen. And unless you set DelayValidation=TRUE a task will get validated when the package spins up and immediately pror to the task executing. There isn't alot of point in caching the results of the first Validation because that would negate the point of doing the second validation.

Does that answer the question?

-Jamie

Wednesday, March 21, 2012

delay in running the sub-packages

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

Kolf wrote:

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)

the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers


I have to add that the subpackage has got some variables which is specified in package configurations
|||

Have you tried executeoutofprocess = false? Do you have the same delay?

|||And delayvalidation = true?

Delay in package starting when running from SQL Agent

Hi,

I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.

The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.

When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.

I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.

Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.

Thanks,

Sam

That sounds very strange. Although I'd guess its a SQL Server Agent problem rather than SSIS.

Can you replace the steps with something else - some simple command-line calls for example, and see if the same thing happens?

Do the log fiels for SQL Server Agent and SSIS tie up? i.e. The package may start 30 minutes late but did the job step start 30 minutes late (there's an important distinction here)?

-Jamie

|||

hi sam, I can think over that problem is that your sql agent is very busy attending other jobs ?

|||

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

|||

Enric,

Thanks for the reply, but this is the only job on the server at the moment, so that shouldn't be causing a problem.

Sam

|||

sam2005 wrote:

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

If that is the case then I would suggest that the delay is caused by the package going through validation. Set DelayValidation=TRUE on the package to see if this removes the delay. If it doesn't, set DelayValidation=TRUE on all your containers and tasks and see if this removes the delay.

If this solves the problem then you know that it is the validation step that is causing the delay. Try doing what i suggested above and then reply here and we'll take it from there!

-Jamie

|||

this is probably a longshot...

do you see this problem when you run package in bi studio?

is it possible that the service startup is slow?

there is a kb article that talks about problem in sp1

http://support.microsoft.com/?kbid=918644

|||

The DelayValidation at the package level, as suggested by Jamie, seems to have done the trick. I also found that there was a msmsgs.exe process running which was constantly using half the processor - killing this has sped things up even more.

Would the DelayValidation setting have any other impact on the package?

Sam

sql

Friday, February 17, 2012

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess is
that it is located in a default location. Where is that?
Regards,
Jamie
Well, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created an
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
Regards,
Jamie
"thejamie" wrote:

> After creating a complex SSIS package, I am unable to locate it. My guess is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie
|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess is
that it is located in a default location. Where is that?
--
Regards,
JamieWell, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created an
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
--
Regards,
Jamie
"thejamie" wrote:
> After creating a complex SSIS package, I am unable to locate it. My guess is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess i
s
that it is located in a default location. Where is that?
--
Regards,
JamieWell, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created a
n
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
--
Regards,
Jamie
"thejamie" wrote:

> After creating a complex SSIS package, I am unable to locate it. My guess
is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .