Dynamic columns for Flat File destination?

I have a database app, and we're implementing various data export features using SSIS.

Basically, it's a couple of straight extracts of various recordsets/views, etc. to CSV (flat files) from our SQL Server 2005 database, so I'm creating an SSIS package for each of these datasets.

So far, so good, but my problem comes here: My requirements call for users to select from a list of available columns the fields that they want to include in their exported file. Then, the package should run, but only output the columns specified by the user.

Does anyone have any idea as to the best way to accomplish this? To recap, at design time, I know which columns the users will have to choose from, but at run time, they will specify the columns to export to the flat file.

Any help or guidance here is greatly appreciated

You will need to create the package programatically in order to acheive this.

http://msdn2.microsoft.com/en-us/library/ms345167.aspx is a good place to start.

Donald

|||

Thanks Donald... I have one more thing to ask. Is it possible to create and execute a package from scratch within a Script Task?

I really want to avoid scripting this stuff outside of the SSIS package (ie, in a VB.NET assembly), because my solution architecture depends on encapsulating all of the logic for SSIS stuff within the package.

If it's not possible from within a script task, could there possibly be another way to achieve this while preserving encapsulation of the logic within the SSIS package?

|||

Yes it is possible to do it in a script task. the code is exactly the same as if you were doing it outside of SSIS (as long as you were using VB.Net of course :)

The best example I have seen so far is here: http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3 Click on "Create Packages Dynamically" in the bottom right hand corner.

-Jamie

|||

Hi Jamie,

again, thanks for the reply... While that code is indeed a fine example of creating packages programmatically, there's a bit of a problem with trying to replicate it inside of SSIS.

That code sample (as well as pretty much all others I've found for building packages programatically) imports the Microsoft.SqlServer.Dts.Runtime.Wrapper and the rosoft.SqlServer.Dts.Pipeline.Wrapper namespaces. Apparently neither of these namespaces are available from within a SSIS script task. The VSA IDE won't let me add references to the assemblies containing these namespaces.

Is there something else I'm missing?

|||

It is not possible to create packages or new objects within packages using SSIS.

You may be able to build the functionality into an assembly which could be called from a script - but you'll need to deploy the assembly separately.

Donald

|||

Oops, I didn't know that! Sorry J Nail!

If I were you I would build it innto a custom task. Its not that difficult - not that much harder then doing it in the script task.

-Jamie