Hi,

I've created an SSIS package by using the Import Wizard and saving the
package to the file system (since Integration Services will not install
on my server).

I have noticed the package creates several connections to temp files and
stores the SQL it needs to run in these files.

eg in the DTSX files you have lines like:

<DTS:ConnectionManager>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">CompensatingSql</DTS:Property>
<DTS:Property
DTS:Name="DTSID">{EE2A770D-C880-4BD3-9597-894197E388A9}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">FILE</DTS:Property>
<DTS:ObjectData><DTS:ConnectionManager>
<DTS:Property DTS:Name="FileUsageType">0</DTS:Property>
<DTS:Property DTS:Name="ConnectionString">C:\Documents and
Settings\Administrator\Local Settings\Temp\1\tmp25.tmp</DTS:Property>
</DTS:ConnectionManager>
</DTS:ObjectData>
</DTS:ConnectionManager>

The temp file in the connection string above is created on the machine
on which the package was created (my workstation) rather than the
server. This is annoying in it's own right as to schedule the package, I
need to create it on the server on which it will run.

What's even more annoying is that when you run the package, it seems to
delete all these temp files if it runs successfully - despite the fact
that they contain all the SQL that does the work. Ie, the package
destroys itself each time it is run!!!

Is there a way to tell it to store the SQL in the DTSX file if the
package is generated by the wizard? There don't seem to be any options
for saving the package. Otherwise, how can I make this work without
having to create the entire package by hand which will day several days
of work?

Why on EARTH doesn't it just put the SQL in the DTSX file???

Nick...

Hello Nick,

What do these files contain? TSQL Statements? You can execute TSQL statements
that are stored inside files from the ExecuteSQL task now which is kind of
cool. What is it you are trying to do and why not try and build the package
in the designer instead of letting the wizard do it for you?




Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Because I was under the delusion that the Wizard would actually produce
a sensible package that is suitable for production use. Perhaps I was
wrong since the wizard generated implementation seems to be a totally
unmaintainable mess. I mean who on earth would store the SQL in the TEMP
folder which gets erased each time you run the disk cleanup wizard?!

I'm only a newbie at SSIS and I don't really have time to work out how
to do this all by hand at the moment - it looks complicated and more
than an afternoon's work to do what I want.

Nick...

When you ran the wizard, you likely checked the "Optimize for multiple
tables" check box. That will cause this "unusual" package design :-)

Re-run the wozard and make sure that check box is NOT checked and you will
get a normal package with a dataflow task and multiple flows.

"Nick Gilbert" <nickg (AT) newsgroup (DOT) nospam> wrote

Dave Frommer wrote:
Well you need to check this box in order to check the box which says
"run as a transaction". And I *need* to run it as a transaction as if
something goes wrong, a live website will end up in an unusable state.

Nick...

Todd C wrote:
You don't need Integration Services installed to run the packages for
some reason. You can run them, but you cannot load in packages saved to
the server - which is why I'm saving it to the filesystem.

I can't find any steps that would delete these files! I really don't
know why it happening. It doesn't even seem to happen every time - only
every other time or so.

Nick...