Greetings,

Environment: Sql Server 2005 June CTP / Win2003 Server
Disclaimer: I'm VERY BEGINNER sql server & totally uninitiated /
unwashed when it comes to DTS / SSIS

Challenge: I have 18 delimited flat files that I want to import into 18
matching Sql Server 2005 tables on a nightly basis. I'd like to use SSIS to
automate the process.

Progess thus far ...

(01) I've successfully run the Db Import / Export wizard on 2 of the tables
and successfully imported the flat file into the database table. I saved
the resulting projects as .dtsx files.

(02) Successfully added the package to an empty BI Designer project by
"dragging & dropping" the .dtsx project file into an empty BI Designer
project. (Was surprised when I browsed to the .dtsx file with the BI
Designer Tool and "file open" wouldn' recognize the .dtsx "package file",
is this a June CTP bug??) Successfully ran the flat file import package
using the BI Designer debugger ... worked great.

(03) Now I have 2 packages in the BI Designer and I can create 16 more that
run successfully as >> individual packages <<.


What I don't know how to do ...

Is how to automate a sequential run of these separate packages with SSIS to
run consecutively and notify me via e-mail if the process fails. It would
be nice to be able to correct failure problems and re-start the import
process at the point of failure.

I've searched thru the SqlIS.com site, the microsoft.public.sqlserver.dts
news group and googled 'automate a series of tasks with SSIS and DTS' but I
haven't had much luck (although I did get sidelined by the Space Station
Information System site I'm currently drowning in the MS BOL SSIS
documentation.

Any help would be greatly appreciated ...

Thanks in advance,

Barry
in Oregon

Hello frostbb,

OK So say you now have 16 package that do the imports. You want to call
them on a schedule.

You can use a Controller DTS package and use the ExecutePackage task to call
the other packages and use workflow between them.

You can use DTExecUI to get the cmdline syntax to execute each package and

Put that into a SQL Server Agent job
Use another DTS package to call the cmdline syntax.


You could of course deploy these packages to a SQL Server and do very similar
things.

Why would 16 Data Flow Tasks joined by workflow in 1 package not work for
you?

You can use the error workflow to handle what happens in the event of a failure.

Restartability can be handled by checkpoints and keep your eyes peeled on
www.SQLIS.com for an article on them in the near future.


Allan

Barry,

Focus some of your BOL reading on the Send Mail Task, Scheduling
Package Execution and Checkpoints.

Use the Search button and filter on Integration Services.

That should get you up to speed on some of what you need.

Andrew Watt
MVP - InfoPath

On Thu, 20 Oct 2005 10:02:56 -0700, "frostbb"
<barry.b.frost (AT) remove (DOT) this.wrd.state.or.us> wrote:

Allan,

Thanks for the quick reply. Its very much appreciated.

Actually, "16 Data Flow Tasks joined by workflow in 1 package" is exactly
what I would like to do in SSIS. Its just that I have multiple separate
packages now with separate control flows, data flows & connection managers.

Is it a good idea / legal to drag and drop all the separate control flows,
data flows and connection managers into a single package and then wire them
together with the green line thingys ??

Please have patience. I'm coming from years in a Unix DB environment where
everything is .ksh / sql scripted ... it might sound wierd but I'm finding
this "click & point" / "drag & drop" stuff a little strange. Intresting
but strange. Kind of feels like the occasional interactions I've had with
the MacIntosh OS

Barry
in Oregon



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks Andrew,

Thanks for the prompt reply. Its very much appreciated.

I'm finally starting to figure out how to productively sort thru the huge
amount of stuff in the BOL. What you suggest is basically where I've been
for the last few hours ... I'm finding it slow going. Never happy being at
the bottom of a learning curve slope. Find myself always hoping for an
example that'll paint a nice easy to understand picture. Never hurts to
hope!

Thanks again.

Barry
in Oregon

"Andrew Watt [MVP - InfoPath]" <SVGDeveloper (AT) aol (DOT) com> wrote

Hello frostbb,

You only need two connection managers (they are in the tray at the bottom)
as each pipeline will instantiate its own version of it (By default)
Yes 16 data flow tasks in 1 package is valid as is one data flow task and
16 pipelines within.


The green thingys are the workflow constraints.

BTW there is a September CTP out there now for you.



Allan

Allan,

Again thank you. I wasn't getting far enough - fast enough with the BOL
pipeline ... so for now I'm scripting the loads the old fashion way ... Bulk
Insert ... .cmd files ... I'll be returning to SSIS when I'm not so "under
the gun".

I'm much farther ahead conecptually with SSIS after this conversation than I
was after 2.5 days with the BOL ... you're help is very much appreciated.

Thanks for your help.

Barry
in Oregon

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Barry,

SSIS is huge and incredibly flexible.

Sometime, set aside a day to go to http://www.microsoft.com/events and
search the On-demand webcasts for SQL Server 2005. There are several
SSIS webcasts to suit all levels. They cover a lot of ground.

When you have watched those for a few hours you will have those magic
"Oh I see now" moments. But also those "Wow, it's huge! How am I ever
going to learn all this?" moments.

Andrew Watt
MVP - InfoPath

On Thu, 20 Oct 2005 14:24:37 -0700, "frostbb"
<barry.b.frost (AT) remove (DOT) this.wrd.state.or.us> wrote:

Good suggestion,

Will hopefully have time sometime next spring after our suicidal migration
schedule lets up a little. In the interrim I've resorted to writing
everything as DLT / .cmd scripts. I know how to make that work. Wasn't
getting far enough, fast enough to justify using SSIS for import / export
tasks.

Regardless, I really appreciate the help. Definitely want to leverage
SSIS in the future.

Best wishs.

Barry
in Oregon

"Andrew Watt [MVP - InfoPath]" <SVGDeveloper (AT) aol (DOT) com> wrote