You should try the XML source adaptor in SSIS and see whether that can do it for you (it does not work for all XML variants).

If you are looking for...

- Create an int variable, scoped at package level, to hold the rowcount results.

- Use an Execute SQL task to run the count query and assign the result to rowcount

-...

Your sample data contains no commas, so each row will be interpreted as a single field.

Maybe you could write to a single variable within the loop - just keep on appending messages to it as you iterate round - and then use the contents of...

Sure. Make sure that the variables you are using are scoped at the package level and then add your tasks to create the file and send the e-mail after the...

That's a real pain!

I think that you might have to import every row as a single text string and then use code (ie a Script component) to split the items...

There are loads of examples on the Internet. This one is nice and detailed and will help you get started:

http://www.codebeach.com/tutorials/sql-server-integration-services-your-first-script-component.asp

This one generates multiple output rows from a single input:

http://blogs.conchango.com/jamiethomson/archive/2006/12/13/SSIS-Nugget_3A00_-Unpivoting-using-the-script-component.aspx

Phil

Maybe this article gives you what you need:

http://www.sqlservercentral.com/articles/Integration+Services/61542/

Wow! Can anyone explain that?

steveb (5/5/2009)

---

I would load the data from the files into a table, then use a sql task to create some indexes on the tables, and then join them using...

Your requirement is still puzzling me. You want to create a single XML file and store that in a single BLOB field?

What is the purpose of using XML here? Are you planning to insert the data, including the XML tags, into the table? Please explain a little more what the requirement...

So the regions on your spreadsheet may or may not match existing regions in your region table.

If they do, pick up the region ID

If they don't, create the region and...

It seems that you need to modify the script, which looks curiously like a VBScript DTS script.

Try replacing this line

DTSDestination("CreateDate") = CDate(DTSSource("Col095"))

With this

If IsDate(DTSSource("Col095")) Then

DTSDestination("CreateDate") = CDate(DTSSource("Col095"))

End If

That sounds a bit nasty - I think you might end up having to do that work in a Script Component, where you can certainly generate multiple output rows for...