Mass updates to dts Packages
-
I have 128 dts packages and I am migrating my sql server from a development domain to a production domain. I need to go into all of these dts packages and update the server connections to reference the new server. Any Suggestions?
-
Can't help much on the mass update, but I would suggest doing a search on this site for articles about portable DTS packages. In developing a prototype data warehouse, I used dynamic settings reading source and destionation information from a database. Where to look for that was set using an INI file. It made writing the packages on my desktop easier and moving them to the server was simple, no updates to the DTS packages themselves.
-
This tool works very well for transferring DTS packages from one server to another.
http://www.sqldts.com/default.aspx?242"
The site also has many helpful suggestions for working with multiple DTS packages.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
If you Save As your package to a Visual Basic file (in SQL 2000, DTS Designer, select the Package.Save As menu item), you can then modify the result and reload it (by commenting/uncommenting the appropriate line in the "Save or execute package" section of the .bas file). The trick is to automate this process by using DMO or some other means to backup all the packages. I don't know how to do that, but at least I helped you along a little. If you find out how to automate the process so that we can save all the packages with a single script please post it here so that all of us can benefit. Thanks.
-
Don't change them. Change the server's name. Sort-of.
Migrate the packages to the new server. Using the client config tool on the new server, define an alias that uses the name of the old server, but make it refer to the new server.
Server aliases are a great way of enhancing portability. So if you change the packages by hand or by DMO scripts use an alias.
-
Hi I have problem here. I am trying to copy the data from 1 table to another but only want to copy the unique records. I wrote dts package that do that but it inserts duplicates. Pleas ehelp I am providing my code .
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
Dim strConn, strSQL,rs,intSort
Dim cn2'* as New ADODB Connection
intID=DTSLookups("Instructor").Execute("instrIDNum")
msgBox intID
if DTSDestination("intFacId")=DTSSource("InstrIdNum") Then
Main=DTSTransformStat_SkipRow
'if not isNull (intID) then
'Main=DTSTransformStat_SkipRow
else
Set cn2 = CreateObject("ADODB.Connection")
intSort = DTSGlobalVariables("SortOrder").Value+1
' Open a connection to the database
' Connection to SQL Server without using ODBC data source
strConn = "Driver={SQL Server};Server=" & DTSGlobalVariables("gvSServer").Value & ";Uid=bonnieDBO;Pwd=" & DTSGlobalVariables("gvSPwd").Value & ";Database=" & DTSGlobalVariables("gvSDB").Value
cn2.Open strConn
Set rs = nothing
' Insert a new record into the table
strSQL = "SET NOCOUNT ON;" &_
"INSERT INTO tblItem (intWebId, intDataType, intNavID, intSubID, intTabID, blnFeatured, blnDeleted, blnApproved,intApproveUserID, intOrder, blnStagingNew, blnStagingChange, intCategoryID, blnNeedsTranslation, intLastLevel) VALUES (687,1,526,687,1,0,0,1,2,"&intSort&",1,1,688,0,0);" &_
"SELECT @@IDENTITY AS NewID;"
' Execute the SQL statement
set rs = CreateObject("ADODB.RecordSet")
Set rs = cn2.Execute(strSQL)
' Get the inserted ID
If rs.BOF OR rs.EOF then
msgbox "bof"
Else
intID = rs.Fields("NewID").value
set rs = nothing
DTSDestination("txtTitle") = DTSSource("txtTitle")
DTSDestination("intCategoryId") = 688
DTSDestination("intItemID") = intID
DTSDestination("txtAbstract") = "0"
DTSDestination("blnNotification") = 0
DTSDestination("intFacID") = DTSSource("InstrIDNum")
DTSDestination("blnPrivate") = 0
DTSDestination("blnLive") = 1
DTSDestination("intResourceID") = 0
' Close the connection
cn2.Close
Set cn2 = Nothing
DTSGlobalVariables("SortOrder").Value = DTSGlobalVariables("SortOrder").Value+1
'Main = DTSTransformstat_InsertQuery
End If
Main=DTSTransformStat_InsertQuery
end if
End Function
Kindest Regards,Web programmer
-
Erik,
I have not tried to understand the Activex script that you posted, because I do not have the time right now and because I think that you are using an approach that is more difficult than it has to be.
Your approach, although creative, does not seem appropriate, at least I would not do it that way. A data pump has three major components, the Source, Destination and Transformation. The Source is generally a table but can be a query. What I would do is to use Query Analyzer to write several queries designed to obtain the non-duplicates that you desire. Once you succeed and have a single query that does what you want, I would put that query in the data pump's Source component. Then the Transformation component becomes a simple Copy Column. In other words, you are trying to do in a Transformation Activex script what would more easily be done in a Source query.
In my opinion, the crucial matter in your problem is how to identify the duplicates and this is a matter of using several approaches, such as DISTINCT, GROUP BY, etc. You can find extensive help on identifying duplicates on this forum as well as other forums.
-
Thanks
But I am using the right approach because my task will has to be scheduled every day
Kindest Regards,Web programmer
-
Eirk,
That's OK. What I have suggested stilll allows you to schedule the DTS package. You are still using a DTS package and you are still using the data pump. The difference is that you are selecting the distinct, non-duplicates by means of a query in the Source, not by means of an Activex script in the transformation.
I hope you were not distracted by the mentioning of Query Analyzer. You use Query Analyzer to find your optimal T-SQL query that will provide the non-duplicates. Once you have that query, you copy it from Query Analyzer and paste it in the Source component of your data pump.
Can someone else please explain to Eirk why using a connection string in an Activex transformation script is not appropriate? I do not feel qualified or objective enough to do this. All I know is that I have a gut feeling that what he is doing is not appropriate.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply