Will your output file always have the same number of columns? To the best of my knowledge SSIS can not handle a dynamic number of columns.

I am not sure if...

>>Would a single shared database server at one of the sites be better/fast enough?

Definitely would be simpler and for me that often means better.

Is it really acceptable for one...

>>if there are lots of files in this format in a specific folder. I need to extract these files and load it into a db where orderid is a primary...

From my license agreement.

Subscriber agrees that it shall not reverse engineer, disassemble, decompile or otherwise attempt to derive the source code of the software provided hereunder.

I think I would rather...

The vendor proc is encrypted. I know there are ways around that but I am somewhat reluctant.

The proc also accepts a date range ( I was simplifying in my example)...

I'm happy with my EAV setup.

My analysts create analytics and since there are a myriad way to analyze something its not hard to come up with new analytics. It happens...

>>Who is your users?

Business users making a decision on if they want to set MyFlag = 1

>>Can they write SQL?

Not at all. They need a user interface that will allow...

Thank you. My database structure has been fixed and the additional code you provided is working. The non-normalized structure I need to output to is necessary to satisfy the requirement...

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/67871/

My schema design is not vendor-provided, legacy, already-in-production, it simply is ...bad design.

Joe: thank you for pointing this out. I am now fixing the design.

Read this http://msdn.microsoft.com/en-us/library/ms162810.aspx

Can you use xp_cmdshell?

Where is you package saved as a file or on the server?

What is your variable in the package called?

Whats the datatype of...

Here you pass a parameter to a stored procedure. The procedure then executes the package using the current value.

The variable in the package is called ID.

CREATE PROCEDURE [dbo].[uspExport]

ID decimal (10,0)

AS

DECLARE...

The third step is not applicable to using DTEXEC. The links were not intended to be a step- by step solution, just some building blocks you can use as you...

Some examples below.

First create the variable in your package and have your select statement reference the variable. then look at using DTEXEC to set the variable at runtime.

http://weblogs.asp.net/rrobbins/archive/2009/01/15/ssis-package-user-variables.aspx

How are you calling the package?

You could call it with DTEXEC and use the SET option to dynamically set a variable within your package.