September 1, 2005 at 12:27 am
Hi,
I am Vishal Prajapati employed as DBA at Extentia Information Technology Pvt. Ltd. I am new to SQL Server and need your help with respect to DTS Package.
I am using SQL Server 2000. I have created one DTS Package to Export the Data from SQL Server to Micorsoft Access with Data Transformation Services Tools and saved DTS Package as SQL Server.
Now I could find the Package at SQL Server Enterprise Manager in Data Transformation Services Folder into Local Packages . IT works fine for the given SQL Server instance and Path of my Microsoft Access .mdb file when I right click and say excute package.
But now I want to Parameterize this DTS Export Package. I should able to give SQL Server Instance name and Path of my Microsoft Access .mdb file.
Can you please help me.
September 1, 2005 at 9:58 am
Take a look at this article. It will help you achieve exactly what you are after. It refers to a text file, but the principle is the same.
http://www.databasejournal.com/features/mssql/article.php/3073161
September 2, 2005 at 7:04 am
I agree, the dynamic properties task is great for parameterizing a package.
A secondary option would be to use the ActiveX Script task to set properties.
Here is an example of setting the SQL in an Execute SQL Task (Very simple example but gives you the idea):
Dim sSQL ' AS String
sSQL = "SELECT * FROM pubs..authors"
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oSQLTask = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_15").CustomTask
' Assign SQL Statement to Source of DataPump
oSQLTask.SQLStatement = sSQL
' Clean Up
Set oSQLTask = Nothing
Set oPkg = Nothing
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply