question

don80037 avatar image
don80037 asked

loop ACCESS table

This is my first SSIS project and I would like to be pointed into the right direction. I need to loop through an ACCESS table and pick and choose data to be used to fill an SQL Server 2014 table.![alt text][1] [1]: /storage/temp/3156-question.png
ssistablesql server 2014access
question.png (60.9 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
I'm not sure what you mean by "loop", but this sounds like a simple data flow. The access table would be the source, and the SQL Server table would be your destination. Then just map source columns to destination columns.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Each row in the ACCESS table has the two columns (Keyname and Value). what I want to do is loop through the access table and if the KeyName = "CompanyName" then write the Value for that into the Company table field "Name" and if the KeyName = "CompanyAdr1" then write the value for that KeyName into the the Company table field "Address1" and so on. I hope this helps clarify my question.
0 Likes 0 ·
Gazz avatar image
Gazz answered
I'm not totally sure what you are on about, but I think you want to JOIN the ACCESS table and the GLOBAL table together on either KetName = CompanyName or CompanyAdr1 and then INSERT this value into the COMPANY table. INSERT INTO COMPANY ( NAME ) SELECT A.VALUE FROM ACCESS A JOIN GLOBAL B ON A.Keyname = B.CompanyName INSERT INTO COMPANY ( Address1 ) SELECT A.VALUE FROM ACCESS A JOIN GLOBAL B ON A.Keyname = B.CompanyAdr1
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

i am trying to create one sql server record in the company table using multiple records from the ACCESS table Globals
0 Likes 0 ·
oh so you want to move data from Microsoft Access to SQL Server and edit it both at the same time? Sorry, no idea how to do that as I have never moved data from one to the other. If I was doing it I would just move all the data in Microsoft Access to SQL Server, which (from googling) seems pretty easy. Then do an INSERT statement, which is pretty easy.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.