CODEWITHSUNDEEP
sql-serverms-accessssisetlssis-2012
Chris
Chris

Reputation: 11

ssis moving data between sql and access databases

In SQL Server Data Tools 2015, I would like to move data from a SQL Server 2012 database into a new access database(2005) and need to create the access table as part of the process. Can this be done all in one Execute SQL process under control flow. This will be part of a loop to run through a list of tables that need to be dynamically created and loaded into an empty access db.

I have created a connection manager and that is in the connection field for the access database and put the code into the SQL statement field under the general tab of the Execute SQL Task component.
Both databases are on my local machine.

"SELECT  a.* into providers from OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  'SELECT * FROM newResults.dbo.providers') as a"

I get the following error:

SSIS package "C:\Users\chris\source\repos\Integration Services Project5\Integration Services Project5\Package1.dtsx" starting. Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "SELECT a.* into providers from OPENQUERYSET('SQLN..." failed with the following error: "Syntax error in FROM clause.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task 2 SSIS package "C:\Users\chris\source\repos\Integration Services Project5\Integration Services Project5\Package1.dtsx" finished: Success.

Upvotes: 1

Views: 398

Answers (1)

John Mo
John Mo

Reputation: 1326

The SQL contained in the Execute SQL Task is executed in the destination's context. The SELECT INTO FROM OPENQUERYSET statement is being passed to Access. Access doesn't have the OPENQUERYSET function and even if it did, your source is SQL Server, which Access doesn't know about unless you have made a connection to SQL Server in Access. Copy your SQL statement into Access and try to execute it and you'll see the same or a similar error. That's what the Execute SQL Task is doing.

Dynamic data is one of the more challenging problems in SSIS. The COZYROC tools include a lot of support for handling dynamic scenarios. Check out the videos for their Data Flow Task Plus for some ideas.

Upvotes: 0

Related Questions