ETL from multiple Access databases -SSIS?

Howdy Folks,

I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?

Thanks for your reply,

Chris

Yes, you can do this.

Use the ForEach File Enumerator to enumerate your list of files. you can use wildcards (e.g. *.mdb)

Store the file path of each file in a variable. You can then use the contents of that variable to dynamically build your connection string using an expression.

Lots of information via Google about this stuff if you want to go hunting or ask here with any pertinent questions.

Regards

-Jamie

|||

Thanks Jamie (alot ) for your suggestion. I tried using the Foreach File Enumerator with the root path for the Access files and a *.mdb wildcard. Then I created a mapped string variable varFileNameto hold the file path. Inside the loop i have my Data Flow Task. In the data source for the task, I was using the variable in an expression as the ConnectionSting property which didn't work. So I added an OLE DB connection string in the expression like "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::varFileName] but i don't think that's the answer.

I've been googling on this but can't find solutions for multiple .mdb's, most of waht i find is dealing with flat files. Great blogs btw.

|||

Should be able to find all you need here: http://www.connectionstrings.com/

-Jamie

|||

Yep, i went there. Thanks Jamie. My string was ok. Actually what got me running was to set the Foreach Loop Container>Properties>Execution>DelayValidation = True. Works great running the SSIS package on the box with SQL server (as opposed to OLE DB) destinations.

Error1Dimension '...' : The following attribute(s) form loop(s):

Hello!

I have an error on a dimension, saying
"Error 1 Dimension 'Car Park Type' : The following attribute(s) form loop(s): [Car Park Type], [Car Park Key]. 0 0 "
but I do not have much idea of what it could mean.

This dimension is based on this request :
SELECT 'Inside' AS CarParkType, InsideCount AS CarParkCount, BuildingKey, OfferKey, TransactionKey, CarParkKey
FROM dimCarPark
WHERE (InsideCount > 0)
UNION
SELECT 'Outside' AS CarParkType, OutsideCount AS CarParkCount, BuildingKey, OfferKey, TransactionKey, CarParkKey + 100000 AS Expr1
FROM dimCarPark AS dimCarPark_3
WHERE (OutsideCount > 0)
UNION
SELECT 'Proximity' AS CarParkType, ProximityCount AS CarParkCount, BuildingKey, OfferKey, TransactionKey, CarParkKey + 200000 AS Expr1
FROM dimCarPark AS dimCarPark_2
WHERE (ProximityCount > 0)
UNION
SELECT 'Double' AS CarParkType, DoubleCount AS CarParkCount, BuildingKey, OfferKey, TransactionKey, CarParkKey + 300000 AS Expr1
FROM dimCarPark AS dimCarPark_1
WHERE (DoubleCount > 0)

Does anyone have any idea?

Thanks anyway.I have found. There was an error in the attributes of the dimension.

Bye!

Error: There is already an open DataReader associated with this Command

Hi

I'm trying to loop through all the records in a recordset and perform a database update within the loop. The problem is that you can't have more than one datareader open at the same time. How should I be doing this?

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & dtrPhoto("AuthorityID"), conWhitt)

cmdUpdate.ExecuteNonQuery()

EndIf

EndWhile

Thanks

What you could do is create a update sub that you just passed the ID of the photo to, which then set the NoPhoto to 1

i.e.

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

SetPhoto(dtrPhoto("AuthorityID"))

cmdUpdate.ExecuteNonQuery()

Then create a sub

Sub SetPhoto(Byval id as Integer)

' Your database stuff

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & id, conWhitt)

End Sub

Hope that helps

|||

Thanks, I thought that would work but I get the same error

PrivateSub Page_Load(ByVal senderAs System.Object, _

ByVal eAs System.EventArgs)HandlesMyBase.Load

Dim cmdPhotoAs SqlCommand

Dim dtrPhotoAs SqlDataReader

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") &".jpg")And dtrPhoto("Pref") <>""Then

NoPhotoUpdate(dtrPhoto("Pref"))

EndIf

EndWhile

conWhitt.Close()

dtrPhoto.Close()

EndSub

Sub NoPhotoUpdate(ByVal PrefAsInteger)

Dim cmdUpdateAs SqlCommand

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref =" & Pref, conWhitt)

cmdUpdate.ExecuteNonQuery()

EndSub

|||

Hi there

Sorry i didnt reply sooner, i was moving house!

The problem your having is that the NoPhotoUpdate sub cannot access the datareader of the page_load sub.

(its a bit of a git to get your head around at first, then when you do you will wonder how you ever managed without it!)

You might want to get a book on OOP for asp.net - The dummies book one is quite good (and written by a comedian!), but covers only vb1.1 (AFAIK).

Anyways that said..

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load
SelectPhoto()

End Sub

Sub SelectPhoto()
Dim cmdPhoto As SqlCommand
Dim dtrPhoto As SqlDataReader

cmdPhoto = New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()
dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

If Not File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") & ".jpg") And dtrPhoto("Pref") <> "" Then

NoPhotoUpdate(dtrPhoto("Pref"))

End If

End While

conWhitt.Close()

dtrPhoto.Close()

End Sub

Sub NoPhotoUpdate(ByVal Pref As Integer)

Dim strConnectionString As String
strConnectionString = ' Your connection String

Dim DBConn As New SqlConnection(strConnectionString)
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
DBConn.Open()

DBCmd = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref = @.Pref", DBConn)
DBCmd.Parameters.Add("@.Pref", SqlDbType.NVarChar).Value = Pref
DBCmd.ExecuteNonQuery()
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing

End Sub

The whole @. thing is a safer way to pass paramaters to your SQL

http://weblogs.asp.net/scottgu/archive/2006/09/30/Tip_2F00_Trick_3A00_-Guard-Against-SQL-Injection-Attacks.aspx for a explanation

Notice how each sub is a seperate little program, with one activating the other whilst passing data.

I hope ive been of some help

|||

Thanks that works. Thanks also for the heads up about sql injection attacks. I do normally pass my parameters the safe way but was not aware of the dangers of not doing so.

Enjoy your new house

|||

I'm having the same problem and i have to believe there is a better solution then creating a sep sub and then creating another connection. Based on your solution you should problably just create a second connection in the first sub and just use that, no need for the whole extra nophotoupdate sub.

Having said that there needs to be a solution for activly using the same connection at the same time. In good old ADO days we were able to open many recordsets and execute sql commands all at the same time with one DB connection. There is a way to do this here.

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

Dim strConnectionString As String
strConnectionString = ' Your connection String

Dim DBConn As New SqlConnection(strConnectionString)
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
DBConn.Open()

DBCmd = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref = @.Pref", DBConn)
DBCmd.Parameters.Add("@.Pref", SqlDbType.NVarChar).Value = Pref
DBCmd.ExecuteNonQuery()
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing

End Sub

|||

I'm having the same problem and i have to believe there is a better solution then creating a sep sub and then creating another connection. Based on your solution you should problably just create a second connection in the first sub and just use that, no need for the whole extra nophotoupdate sub.

Having said that there needs to be a solution for activly using the same connection at the same time. In good old ADO days we were able to open many recordsets and execute sql commands all at the same time with one DB connection. There is a way to do this here.

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

Dim strConnectionString As String
strConnectionString = ' Your connection String

Dim DBConn As New SqlConnection(strConnectionString)
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
DBConn.Open()

DBCmd = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref = @.Pref", DBConn)
DBCmd.Parameters.Add("@.Pref", SqlDbType.NVarChar).Value = Pref
DBCmd.ExecuteNonQuery()
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing

End Sub

|||

I found the solution on the msdn forums. Seems to only work for SQL 2005

"This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string)."

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=123691&SiteID=1