Create a variable, say RowCount (int32).

Add a Row Count transformation immediately after your source component and map the row count result to the RowCount variable you just created.

Then use precedence...

What you are trying to do is not straightforward, as you have found.

One way is described here, under Self Modifying Packages in SSIS?:

http://www.sqljunkies.com/weblog/knight_reign/

Is this relevant:

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/65d73a61-e720-4568-bbec-81383158292e/

Maybe you are getting confused.

The Copy Column transform creates an in-memory copy of a column in your data source, which you can use later on in your data flow.

It does...

It's really easy.

On your Data Flow pane, add a lookup transformation.

Connect the output of your flat file source to it.

Connect it to the next task on your data flow...

So the package itself cannot be re-opened? That's quite different from the problem with an Excel source being open at run time.

Can you paste the error message that you are...

I agree - Lookup is better - why didn't I think of that?...

Sounds like a MERGE JOIN transformation might give you what you want, taking as inputs the spreadsheet and the parent table and configuring the join as you have detailed ...

Reading between the lines "Close the open file and rerun" would appear to be the resolution in this case.

Try adding a Data Viewer to confirm that the dodgy data is not being returned by the SP.

For some reason, all of your source data is being treated as text. If source data is properly recognised as numeric, the text qualifier will not be applied to it.

To...

OK, good - that makes it clear. I can think of two ways:

1) Import your data to a staging table in SQL Server and then use a UNION query to...

OK, I misunderstood the requirement.

Can you give sample before (source) and after (required format) data please - just one example should be enough for me to picture it - thanks.

Add a derived column transformation after the merge to give you the data as you want.

Derived column works well.

Create a new column with a formula like this:

[SourceTextDateTimeField]

and give it a datatype of DT_DBDATE. This casts the string to a date and strips away the time...