SSIS Insert/Update a non OleDB, Excel, Flat File destination

Question

Post reply

SSIS Insert/Update a non OleDB, Excel, Flat File destination

John-79567

SSCommitted

Points: 1892
More actions
March 30, 2007 at 9:43 am

#194817

Apparently SSIS does not support insert/update directly into a non oledb, excel, flat file destination source... I would appreciate suggestions on how i could accomplish this without using sql 2005 linked servers.
Here's my setup...
Source - SQL 2005
Destination - Pervasive 8
Scenario:
I need to insert or update records From SQL Table A to Pervasive Table A based on whether SQL Table A record exists in Pervasive Table A

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason Coleman SSCrazy Points: 2187 More actions · Answer 1

I've imported data from Pervasive to SQL, and can think of the following solution, although I havent tried it...

Create a datareader connection for the Pervasive connection
Configure using an ODBC connection for your Pervasive table
Create an OLE DB connection for your SQL source
Pass the two sources through each of their own sort transformations, and sort on the same indexes/values
Use a Merge Join transformation with a left outer join
Use a conditional split transform to filter out your unique recs
Use a datareader destination with an ODBC connection to update your Pervasive table.

Like I said I havent tested it, but it should work in theory.

John-79567 SSCommitted Points: 1892 More actions · Answer 2

Believe it or not, i did exactly what you suggested prior to posting here. The problem I'm having is the final step. INSERT/Update pervasive via SSIS. The datareader w/ odbc destination is an in memory dataset. You can not INSERT/Update an ODBC DataReader destination using it.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 3

I've not tried this so I don't know if it works... and you may have gone down this road already.

Instead of the ODBC connection, set up an ADO.NET for ODBC connection manager.

Write the results of your dataflow into a recordset.

In your control flow configure a foreach ADO enumerator mapping the columns from your recordset to the appropriate variables. Inside the loop create an execute SQL task and in your update/insert statement for your pervasive table use your newly populated variables to provide you with the input parameters.

It sounds like it might work, but as I say, I've never tried it!!! Also it would probably be very inefficient!

Kindest Regards,

Frank Bazan

Jason Coleman SSCrazy Points: 2187 More actions · Answer 4

I found the following post in the MSDN forums - unless the scope of SSIS has been updated and the above doesn't work, looks like you'll be doing it long hand..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106559&SiteID=1

John-79567 SSCommitted Points: 1892 More actions · Answer 5

Jason, thanks for the link. I actually similar discussions about using "Script Components" as a destination source. It actually worked well. I even went as far as creating script component transformations and a custom destination source for Pervasive. Again, thanks for the suggestions. I know I can always count on the folks at SQLServerCentral.

ramesh-1108129 SSC Journeyman Points: 83 More actions · Answer 6

Hi John,

Can you post a copy of the script that you had developed to insert records into Pervasive within SSIS?

Thanks,

Ramesh