March 28, 2012 at 1:44 pm
Hi,
I am trying to Create the new MSAccess Database and X, Y, Z tables using SSIS
I thought to create through Script task and write c# but I missed some .dll
Please help me and let me know the suggestions how I can create the Database using the SSIS
March 29, 2012 at 11:44 am
If I understand correctly you are missing the dll to work with Access? Can you clarify where you are running into an issue?
March 29, 2012 at 11:50 am
Yes I am missing some dll ..my issue is how to create MsAccess database and tables using SSIS
March 29, 2012 at 12:13 pm
First off create an empty MSAccess database somewhere. This will be your template database, especially if you are going to do dynamically going forward needing a scratch database. Save a copy and in your package you should be able to do the following.
Add a new OLE connection in your SSIS to the template database using MS Jet 4 Ole Provider.
Next add an Execute SQL Task on the designer and set the connection to your Access database and the SQL statement similar to this (note: the ` are import for Access statements)
CREATE TABLE `Admission_Exam_Test` (
`Student_ID` VarChar(10) NOT NULL,
`Exam_ID` VarChar(10) NOT NULL,
`Admission_Exam_Code` VarChar(15) NOT NULL,
`Admission_Exam` VarChar(25) NOT NULL,
`Admission_Exam_Type` VarChar(15) NOT NULL,
`Admission_Exam_Date` VarChar(10) NOT NULL,
`Admission_Exam_Score` VarChar(6) NOT NULL
)
GO
This will create your table. Attach on success to your data flow.
Now you can create a Script Task to copy your template database to the name you wish to use, then change your package connection for the Access database to the new copy, and finally in your script alter the CREATE statement above to build your table for your workflow.
This should get you going the direction you want and beyond that the rest is up to you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply