Advertisement

06.09.2008 at 11:43AM PDT, ID: 23470083
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.2

VB6 DTS code from SQL/Office 2000 -- Upgrade to Sql 2005/Excel 2007

Asked by gigglick in Visual Basic Programming, MS SQL Server, VB Database Programming

Tags:

Hi -

I have an old DTS package that was code generated by sql 2000.  We've upgrade to 2005 which was not an issue but now we'd like to upgrade to office 2007 as well.  The DTS package pushes excel data into sql server but it no longer functions with office 2007.  We could upgrade and save in the old .xls format but I'd like to move over to the xlsx or xlsm format.  Does anyone have suggestions on how/the best way to do this.  An ex of an old dts package is attached
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:

Private Sub Import_Pricing()
        Set goPackage = goPackageOld
 
        goPackage.Name = "Gopher2"
        goPackage.description = "DTS package description"
        goPackage.WriteCompletionStatusToNTEventLog = False
        goPackage.FailOnError = False
        goPackage.PackagePriorityClass = 2
        goPackage.MaxConcurrentSteps = 4
        goPackage.LineageOptions = 0
        goPackage.UseTransaction = True
        goPackage.TransactionIsolationLevel = 4096
        goPackage.AutoCommitTransaction = True
        goPackage.RepositoryMetadataOptions = 0
        goPackage.UseOLEDBServiceComponents = True
        'goPackage.LogToSQLServer = False
        'goPackage.LogServerFlags = 0
        'goPackage.FailPackageOnLogFailure = False
        'goPackage.ExplicitGlobalVariables = False
        'goPackage.PackageType = 0
        
 
Dim oConnProperty As DTS.OleDBProperty
 
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
 
Dim oConnection As DTS.Connection
 
'------------- a new connection defined below.
'For security purposes, the password is never scripted
 
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
 
        oConnection.ConnectionProperties("Data Source") = "C:\Myfile.xls"
        oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"
        
        oConnection.Name = "Connection 1"
        oConnection.id = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.datasource = "C:\Myfile.xls"
        oConnection.ConnectionTimeout = 60
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        'oConnection.Password = "<put the password here>"
 
goPackage.Connections.Add oConnection
Set oConnection = Nothing
 
'------------- a new connection defined below.
'For security purposes, the password is never scripted
 
Set oConnection = goPackage.Connections.New("SQLOLEDB")
 
        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("User ID") = "myuser  "
        oConnection.ConnectionProperties("Initial Catalog") = "mydb"
        oConnection.ConnectionProperties("Data Source") = "myserver"
        oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"
        
        
        oConnection.Name = "Connection 2"
        oConnection.id = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.datasource = "myserver"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "mydb"
        oConnection.UseTrustedConnection = True
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        oConnection.Password = "mypassword
 
goPackage.Connections.Add oConnection
Set oConnection = Nothing
 
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
 
Dim oStep As DTS.Step
Dim oPrecConstraint As DTS.PrecedenceConstraint
 
'------------- a new step defined below
 
Set oStep = goPackage.Steps.New
 
        oStep.Name = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Step"
        oStep.description = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"
        oStep.CommitSuccess = False
        oStep.RollbackFailure = False
        oStep.ScriptLanguage = "VBScript"
        oStep.AddGlobalVariables = True
        oStep.RelativePriority = 3
        oStep.CloseConnection = False
        oStep.ExecuteInMainThread = True
        oStep.IsPackageDSORowset = False
        oStep.JoinTransactionIfPresent = False
        oStep.DisableStep = False
       
        
goPackage.Steps.Add oStep
Set oStep = Nothing
 
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
 
'------------- call Task_Sub1 for task Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task (Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task)
Call Task_Sub1(goPackage)
 
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
 
'goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
goPackage.Execute
goPackage.UnInitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = Nothing
 
Set goPackageOld = Nothing
 
End Sub
 
 
'------------- define Task_Sub1 for task Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task (Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)
 
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
 
Dim oCustomTask1 As DTS.DataPumpTask
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask
 
        oCustomTask1.Name = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"
        oCustomTask1.description = "Copy Data from Sheet1$ to [Mydb].[dbo].[Daily_Security_Pricing] Task"
        oCustomTask1.SourceConnectionID = 1
        oCustomTask1.SourceSQLStatement = "select `Ticker/Cusip`,`Ticker`,`Px_Mtd_Close`,`Unit_value`,`Date` from `Sheet1$`"
        oCustomTask1.DestinationConnectionID = 2
        oCustomTask1.DestinationObjectName = "[Mydb].[dbo].[Daily_Security_Pricing]"
        oCustomTask1.ProgressRowCount = 1000
        oCustomTask1.MaximumErrorCount = 0
        oCustomTask1.FetchBufferSize = 1
        oCustomTask1.UseFastLoad = True
        oCustomTask1.InsertCommitSize = 0
        oCustomTask1.ExceptionFileColumnDelimiter = "|"
        oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
        oCustomTask1.AllowIdentityInserts = False
        oCustomTask1.FirstRow = 0
        oCustomTask1.LastRow = 0
        oCustomTask1.FastLoadOptions = 2
 
        
Call oCustomTask1_Trans_Sub1(oCustomTask1)
                
                
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
 
End Sub
 
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
 
        Dim oTransformation As DTS.Transformation
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DirectCopyXform"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
          
                
                Set oColumn = oTransformation.SourceColumns.New("Date", 5)
                        oColumn.Name = "Date"
                        oColumn.Ordinal = 5
                        oColumn.Flags = 118
                        oColumn.Size = 0
                        oColumn.datatype = 7
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.SourceColumns.New("Ticker/Cusip", 1)
                        oColumn.Name = "Ticker/Cusip"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 102
                        oColumn.Size = 255
                        oColumn.datatype = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.SourceColumns.New("Ticker", 2)
                        oColumn.Name = "Ticker"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 102
                        oColumn.Size = 255
                        oColumn.datatype = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.SourceColumns.New("Px_Mtd_Close", 3)
                        oColumn.Name = "Px_Mtd_Close"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 118
                        oColumn.Size = 0
                        oColumn.datatype = 5
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.SourceColumns.New("Unit_value", 4)
                        oColumn.Name = "Unit_value"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 118
                        oColumn.Size = 0
                        oColumn.datatype = 5
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.DestinationColumns.New("Statement_Date", 1)
                        oColumn.Name = "Statement_Date"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 24
                        oColumn.Size = 0
                        oColumn.datatype = 135
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.DestinationColumns.New("Cusip", 2)
                        oColumn.Name = "Cusip"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 8
                        oColumn.Size = 9
                        oColumn.datatype = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.DestinationColumns.New("Ticker", 3)
                        oColumn.Name = "Ticker"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 104
                        oColumn.Size = 50
                        oColumn.datatype = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.DestinationColumns.New("Price", 4)
                        oColumn.Name = "Price"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 120
                        oColumn.Size = 0
                        oColumn.datatype = 5
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing
 
                Set oColumn = oTransformation.DestinationColumns.New("UnitValue", 5)
                        oColumn.Name = "UnitValue"
                        oColumn.Ordinal = 5
                        oColumn.Flags = 120
                        oColumn.Size = 0
                        oColumn.datatype = 5
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing
 
        Set oTransProps = oTransformation.TransformServerProperties
 
                
        Set oTransProps = Nothing
 
        oCustomTask1.Transformations.Add oTransformation
        Set oTransformation = Nothing
 
End Sub
[+][-]06.09.2008 at 12:13PM PDT, ID: 21745763

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.09.2008 at 12:27PM PDT, ID: 21745867

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.11.2008 at 07:57AM PDT, ID: 21760700

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Visual Basic Programming, MS SQL Server, VB Database Programming
Tags: VB 6
Sign Up Now!
Solution Provided By: gigglick
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628