Post reply

Access table and SQL table inside SSIS package

  • February 2, 2009 at 11:55 pm

    #130655

    Hi,

    I have a table in Access database(table name: tblA)

    I have another table in SQL (table name: tblS)

    Given that the colum names are same in both the tables,

    I need to compare these two tables through an SSIS package.

    Is it possible to do?

    I have a stored proccedure usp_CompareTables:

    My thinking is like creating a script task

    1. Get the access table name

    2. Get the sql table name

    3. pass these two table names to the stored procedure

    4. get the output with the differences in table(if any)

    (How to display this output?)

    Will this work?

    I know nothing about scripts.

    Please give me some suggestions for this.

    Or any links to articles which teaches scripts from very basic.

    I don't think the above works.

    sending only the table names to the stored procedure!!!

    I think we have to

    1. Query the Access table "select * from tblA" and store the result somewhere(I do not know where)

    2. Query the SQL table "select * from tblS" and store the result somewhere(I do not know where)

    3. Execute the stored proc on these two results

    Any help/suggestions will help me greatly...

    Thanks

    Thanks,
    Santhosh

    Human Knowledge Belongs To The World !!

  • February 3, 2009 at 5:33 am

    #937037

    This is how I would do it - no scripts required:

    1) Create a new SQL Server table to hold the Access data temporarily (tblAS).

    2) Write a stored proc on SQL Server to do all the comparisons / updates etc you need.

    3) Create an SSIS package that does the following - in broad terms:

    a) Truncate tblAS

    b) Import from Access to tblAS

    c) Run the stored proc

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 2 posts - 1 through 1 (of 1 total)

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