I tried

SELECT * FROM [SHEET NAME$]

I received the following error. Is my syntax incorrect?

TITLE: Microsoft Visual Studio

------------------------------

Error at XXXX_YYY_WEEKLY [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR....

In the Flat File Connection Manager, I set the text qualifier to double quotes (""). It had no affect. Am I doing something wrong?

You're right, a pipe character would be a superior delimiter and I wish I could use it. Unfortunately, I have NO control over the format of the source file.

I kept the lookup based on the SELECT statement I mentioned earlier. SALE_DATE (the only column in the lookup) became the Lookup Column. The Lookup Operation was <add as...

Wait, I think I figured it out with the Lookup.

Okay, this makes sense. I now have the flat file source bringing in all the rows. I tried to use a lookup based on SELECT DISTINCT SALE_DATE FROM...

Could the Slowly Changing Dimension transformation help in this situation?

Thanks for all of the help. I ended up doing something like this:

If File.Exists(lstrFileName) Then

...

Thanks for the great advice! Does anyone know what affect the new DATE datatype has on Cognos? Has anyone experienced the new data type hurting Cognos reports?

Regarding the Script Component, how would I set the DTS.Variables("").Value to the value from the spreadsheet?

Yes, one SharePoint list is an existing dimension and a different SharePoint list is the destination. I need to ask the boss why it is being done this way.

I need to make a correction. The exisiting dimension is a SharePoint list. The source system is a SQL Server table. The output is a different SharePoint...

Please pardon the delay. This is definitely the Kimball Method Slowly Changing Dimension from Codeplex instead of the standard SSIS SCD. Here is how we have it set...

This is excellent information, thanks!

Can I use the SELECT COUNT(1) to populate a variable from within the Data Flow task?