SSIS 2012 Package not reading entire table

Question

I am working on SSIS 2012 and ran into a issue that happened as follows:

Things seemed to be going well until a couple of databases that were a part of SSIS connections managers were set into single user mode by rolling back transactions, since they needed to be renamed.

I am not sure if this could be a direct cause, but after the event, the package seems to be sticking to the row counts for a specific table in the old databases.

For example, the old database DB_old and new DB_new have a table items, which contains 100 and 200 records respectively. After the DB_old has been renamed (to DB_new), the package should be retrieving 200 records, but it only gets the 100 and leaves out the rest. The package executes successfully.

This is happening at the OLEDB data source and not after any lookups.

Can someone please suggest on how can I refresh the package metadata? 

Thanks

Mac

Answer 1

You're sure the SSIS packages still doesn't go the the old table?

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 2

Hi Mac,

I think the metadata is the issue; to refresh it you would need to remove it from the catalog and then open it in BIDS the package metadata should be then automatically refreshed, you may need to right-click on any warning to "fix all references" so it does not have to be for every transformation (a neat SSIS 2012 feature).

Then the package can be put back into the catalog.

---

Arthur My Blog

Answer 3

Sorry. I forgot to mention that the package is being worked on in SSDT (BIDS) and has not yet been deployed into a catalog.

Also, I am pretty sure that it is getting the corect references since the database name is passed through a dynamically created connection string which is updating correctly.

I have also tried reverting back to an older version of the package from source control, but that did not work.

Any suggestions?

Thanks for your help!!

Answer 4

I suspect, if there is a config file, it is not getting applied, or you are wrong about the correctly working expression for the variable. If I am wrongStart removing parts from this package or building a new one until it works. There should be nothing stopping it from getting all the records read.

---

Arthur My Blog