June 4, 2009 at 9:58 am
I have been charged with setting up an nightly SSIS project, and I have no idea where to begin. I need to take data from an Access database, combine it with data from our SQL Server database, and write the results to a CSV file. Can anyone tell me how to begin? I know that I have to use the Business Intelligence studio, and I can use the Import and Export Wizard to point to the Access db (on a mapped drive), but from there I have no idea how to query the data. Can I write a SP that joins both the Access tables and the SQL Server tables? I don't have to import the Access db into SQL each night do I? There are tables with the same names in both db's, and this sounds like it would be hard on resources. Any help would be appreciated.
June 5, 2009 at 2:14 am
Hi Mark,
To get started open a new project in BIDS.
Add a data flow component to the package.
Double click the component and the data flow page opens.
Add an OLEDB data source and point it at your Access DB.
Configure it to read whichever table you need.
Add an OLEDB destination to the same page.
Drag the green data connection from the OLEDB data source to the destination.
Now edit the destination to be your SQL DB.
That gets the data in.
Go back to control flow and use a SQL component to do whatever merging you need, on the control flow page the arrows are 'control flow' rather than 'data flow'.
After the merge you will need another data flow component similar the the first one but the source will be OLEDB from SQL and the destination will be flat file.
Hope that helps you get started.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply