SQLTeam.com | Weblogs | Forums

Export data from access to sql using ssis


#1

I have 5 different access databases 2010731 ,2010831
,2010931 ,21001031, 20101131

within each database there are 3 similar tables, employee, customer, sales.
Each table has a reported date column. as you can see each db is named in date format. so for example 2010731
employee table has multiple dates within the reported date column, I only want to get the data for 2010731, so a sql like select * from 2010731
where reporteddate like '7/31/2010%' would work.so 2010831
i only want employees that match 2010831 in the reported date column. this method would apply to customer and sales table.
How can i write a SSIS package that will loop through each Access db located in those 3 tables and only return data for the reported dates that is assigned to the the specific db.


#2

see https://msdn.microsoft.com/en-ca/library/cc280478.aspx?f=255&MSPPError=-2147217396

To get the data from access, write a query to extract the data you want. Alternatively, use a conditional split transformation to send data for the dates you want into the pipeline and discard the rest.

you don't say if you want all data to go to one table in SQL Server or to separate tables. In the first case, build a dataflow with 3 OLEDB sources, one for each Access table, Use a Union all transform to gather the streams before sending to one OLEDB connector for SQL Server. Once you have this working, copy the DFL for each of the other Access databases.

Alternatively, you can use a foreach loop in the control tab and put the access connection strings in there. Then, set your Access database connector to use a variable for the connection string, -- the one in the Foreach loop.

Of course this all assumes you are already fluent in SSIS. If this is your first package, I suggest you run through a few tutorials first and read a few books on the topic. There are many out there.


#3

no this is not my first SSIS package. i already created multiple OLE DB sources and connected to the Access db. and i did write sql within my dataflow task to get the exact date, i just wanted to see if there was a easier way .


#4

the CS Split may be easier since you wouldn't have to write a query. OTOH the package would then read the whole tables from Access, which could be a performance issue.

Using a ForEach loop to run through the Access dbs could be a real time-saver too