SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - OLE DB Source task Error


SSIS - OLE DB Source task Error

Author
Message
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 1399
I am using SSIS package to read the data from Access file and import them to SQL Server table. I have total 10 tables that I need to migrate / import to SQL Server. I am using OLE DB Source & OLE DB Destination tasks for this requirement. SSIS Package is working fine and perform the required tasks.

I am using OLE DB Source task to read the data by providing following:
OLE DB Connection Manager: AccessDB Connection
Data access mode: Table or View
Name of the table or the view: tableName

Now, I need to add one more table but it may not present in all the accessdb (mdb) files. Because of table is not present, OLE DB source task throws an error but I would like to ignore that error, insert default data to SQL Server table and go ahead with other tasks.

How can I achieve this functionality in SSIS package?

Thanks
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18155 Visits: 20390
Hardy21 (9/27/2012)
I am using SSIS package to read the data from Access file and import them to SQL Server table. I have total 10 tables that I need to migrate / import to SQL Server. I am using OLE DB Source & OLE DB Destination tasks for this requirement. SSIS Package is working fine and perform the required tasks.

I am using OLE DB Source task to read the data by providing following:
OLE DB Connection Manager: AccessDB Connection
Data access mode: Table or View
Name of the table or the view: tableName

Now, I need to add one more table but it may not present in all the accessdb (mdb) files. Because of table is not present, OLE DB source task throws an error but I would like to ignore that error, insert default data to SQL Server table and go ahead with other tasks.

How can I achieve this functionality in SSIS package?


I can suggest a way in outline, at least.

1) Create a package-scoped variable (TableExists, or similar) - make it a byte or small integer.
2) Create an ExecuteSQL task which 'points' to the Access database.
a) (I'm a SQL Server guy, so this is the bit you'll need to expand on) Write a query to return 1 if the table exists, 0 otherwise.
b) Return the results of the query to the variable created in (1).
3) After your ExecuteSQL task, create a precedence constraint to the data flow task for the table which may or may not exist.
a) Change the Evaluation operation to Expression and Constraint.
b) Set the Expression to something like
@[User::TableExists]==1
c) Create another precedence constraint from the ExecuteSQL task which skips the data flow task. Its Expression will be something like
@[User::TableExists]!=1


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 1399
Phil Parkin (9/27/2012)
Hardy21 (9/27/2012)
I am using SSIS package to read the data from Access file and import them to SQL Server table. I have total 10 tables that I need to migrate / import to SQL Server. I am using OLE DB Source & OLE DB Destination tasks for this requirement. SSIS Package is working fine and perform the required tasks.

I am using OLE DB Source task to read the data by providing following:
OLE DB Connection Manager: AccessDB Connection
Data access mode: Table or View
Name of the table or the view: tableName

Now, I need to add one more table but it may not present in all the accessdb (mdb) files. Because of table is not present, OLE DB source task throws an error but I would like to ignore that error, insert default data to SQL Server table and go ahead with other tasks.

How can I achieve this functionality in SSIS package?


I can suggest a way in outline, at least.

1) Create a package-scoped variable (TableExists, or similar) - make it a byte or small integer.
2) Create an ExecuteSQL task which 'points' to the Access database.
a) (I'm a SQL Server guy, so this is the bit you'll need to expand on) Write a query to return 1 if the table exists, 0 otherwise.
b) Return the results of the query to the variable created in (1).
3) After your ExecuteSQL task, create a precedence constraint to the data flow task for the table which may or may not exist.
a) Change the Evaluation operation to Expression and Constraint.
b) Set the Expression to something like
@[User::TableExists]==1
c) Create another precedence constraint from the ExecuteSQL task which skips the data flow task. Its Expression will be something like
@[User::TableExists]!=1

I have a query that check the table exists in Access database as below:
SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = "tblName"
But, when I use this in SQL Task, SSIS throws an error:
"
[Execute SQL Task] Error: Executing the query "SELECT count(*) as TableExists
FROM MSysObjects
WH..." failed with the following error: "Record(s) cannot be read; no read permission on 'MSysObjects'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
"
I have checked result set, connection are all proper but still SSIS is throwing an error.

Any idea?

Thanks
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18155 Visits: 20390
Is the ResultSet property of the ExecuteSQL task set to SingleRow?

Have you mapped 'TableExists' to your package variable on the Result Set property page?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 1399
Phil Parkin (9/27/2012)
Is the ResultSet property of the ExecuteSQL task set to SingleRow?

Have you mapped 'TableExists' to your package variable on the Result Set property page?

Yes Phil. It is SingleRow and result set mapped to package variable.
Result set page setting:
Result name: 0
Variable Name: User::AccessTable

Thanks
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18155 Visits: 20390
Hardy21 (9/27/2012)
Phil Parkin (9/27/2012)
Is the ResultSet property of the ExecuteSQL task set to SingleRow?

Have you mapped 'TableExists' to your package variable on the Result Set property page?

Yes Phil. It is SingleRow and result set mapped to package variable.
Result set page setting:
Result name: 0
Variable Name: User::AccessTable


Try changing ResultName to TableExists (to match the column name in your query).


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 1399
Phil Parkin (9/27/2012)
Hardy21 (9/27/2012)
Phil Parkin (9/27/2012)
Is the ResultSet property of the ExecuteSQL task set to SingleRow?

Have you mapped 'TableExists' to your package variable on the Result Set property page?

Yes Phil. It is SingleRow and result set mapped to package variable.
Result set page setting:
Result name: 0
Variable Name: User::AccessTable


Try changing ResultName to TableExists (to match the column name in your query).

Phil, Checked with that option. No luck yet.
SSIS throws same error.

I dont have Access installed in my box. Is it the issue?

Thanks
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18155 Visits: 20390
Hmm - it seems that I have had a go at helping someone solve this issue in the past - check here.

It would seem to be a problem specifically related to permissions on system tables.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search