I am writing my first SSIS task. I have an MS Access database and I'm moving the contents of each table into a like-named SQL Server database. The task is working properly except for one step.
The source Access database may or may not have one of the tables in it. It will always have TableA, TableB, and TableC, but may or may not have TableD. I need to write my SSIS package in such a way that it will detect the presence/absence of TableD and either run or skip the import step for this table accordingly.
Can someone assist with the step that detects the table presence/absence in the MSAccess database?
thanks,
matt tag
easy.I never work with Access/Excel, but can you run an Execute SQL task against it and populate an Integer variable with the result set?
This is the query. You'll need to map a parameter, or simply hard code the table name in place of the "?" below. If the output is greater than zero (which you can test in a precedence constraint) then the table exists, if not then the table does not exist:
SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = ?|||
Matt,
The post of the Phil is excellent, but you must do it inside the control flow. Although you have a control error for the OLE DB Source that you can redirect to do something in the case of the source does not exist...
Regards,
Pedro
