OLE DB Source & Excel Destination

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq

If the metadata of the sources changes then you cannot use the same data-flow task. Its as simple (or as difficult) as that.

-Jamie

|||

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Or is there any thing which can refresh the meta data during runtime?

Thanks

Shafiq

|||

shafiqm wrote:

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Yes, except they're not called conditional splitters. The correct nomenclature is conditional precedence constraints. Loads of good info here: http://www.sqlis.com/default.aspx?306

shafiqm wrote:

Or is there any thing which can refresh the meta data during runtime?

No! Well, actually there is a horrible workaround which involves editing a .dtsx package from another .dtsx package. I have never done it and I certainly never intend to - steer well clear of it.

Using precedence constraints to decide which data-flow to execute is absolutely the right way to go.

HTH

-Jamie

|||

I am going to use the conditional precedence constraints. The next question is do I have to use different OLE DB source / Excel File connection Manager for each data-flow task or they can be changed dynamically.

I was trying to only use one OLE DB and I got error message VS_NEEDSNEWMETADATA

Thanks

|||

You can use the same connection manager across different data-flows and change it dynamically.

You can not use an OLE DB Source component in different data-flows.

-Jamie

|||

It looks like I can't use same Excel File connection Manager as during the design time, If I change the file the mapping of data-flow task then previously defined Data-flow tasks goes wrong and I get the message

Excel desitnation needs VS_NEEDSNEWMETADATA

Note: Each excel file will have different columns depending upon the report

I think same excel file connection manager only work if all the files have the same number of columns

It looks like I am doing an automatic job manually.

Thanks

|||

OK, here's the deal. Once you change the connection manager to point to a file with differrent metadata then of course the data-flow tasks will fail to validate because they are expecting one thing and seeing another (that's what's causing the message you are getting).

Get around this by setting DelayValidation=TRUE on all the data-flows. THis means that they won't get validated until they are executed by which time your connection manager connection string should be set up correctly.

-Jamie

|||

Thanks very much for your prompt response and it help me a lot. One last thing. As I am deleting the excel file and re-creating every time the package runs, Is there way to format the excel file using SSIS

e.g.

Format a column to show 2 decimal places or format as currency

Do any subtotal or run a macro?

I know I did this using ActiveXScript task in SQL 2000, Is there any other way to do this?

Thanks

|||

If Excel has an API (which I assume it does) then I assume you can manipulate it via that API. You would need to ask someone that knows about Excel.

-Jamie