Debbie Edwards (2/21/2013)

---

Heres a thought.....

In the Flat file connection managers properties I have

ConnectionString set to \\D-fs07\spar-it\PROJECTS\1 Active Projects\DATA WAREHOUSING PROJECT\Development\Extract_To_CSV\Test.csv

And Expressions Connection String set to @[Extract_To_CSV_Folder]+@[User::DFES_NAME_DATE_File_Name]+".csv"

The fact there...

These are tough problems to troubleshoot. For me, they've always been some small part of a variable or an expression. You've probably looked at the following but just...

Your DDL is great. It would help, though, if you'd provide some sample inserts. The "sample data" you've provided isn't structured to insert and doesn't match the datatypes...

I add to the accolades. Thank you so much for posting the results of your investigations and how the situation was resolved. The error message did not send...

Ok, here's one possibility. I'm still not sure that I understand the requirements but I believe this will get the type of output you're looking for. Note that...

To clarify. You're looking for only a one column output? What you're describing sounds like two separate queries, one for blank AgencyFrom and another for blank AgencyTo.

Yes, you can use reference table to manage the additions of criteria over time. I use reference tables fairly frequently to handle the type of situation you're describing. ...

In terms of finding rows for your cleanup, Dwain's approach and mine will give you the same result so it comes down to speed. How many rows are you...

If I understand your requirements, the approach I suggested will work for any number. I placed the comparison test in a CTE and then queried for the final results....

Here's one approach. You didn't provide ddl for your test data so I made some assumptions about data types. This approach is based on using the first character...

Glad the approach was helpful. I missed the slight variation in the output.

Your desired output seems to suggest that what you're looking for is the minimum GroupID for each item.

Select MIN(GroupId) as GroupId, ItemId, ItemName

From #Data

Group by ItemId, ItemName

Order by ItemId;

Don't have test data from you to check but I believe something like this will work. Approach is to identify all closed accounts by CTE and then exclude them...

If all of the data on all of the tabs in the file are refreshed each time, you might consider dropping and recreating the excel file with each run.

I'm sure there's a more elegant way to do this but here's one possibility. It involves randomly selecting a number between 0 and 9 using 100 of those 10...