Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.
Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.
MA
SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.
Import data from Access can be done in a data-flow
There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.
-Jamie
|||Jamie:
Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?
MA
|||Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx
-Jamie
|||Hi Jamie:
I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.
MA
sql
No comments:
Post a Comment