Connection Manager - variables

hello all,

I have a scenario where I like to reuse one project from one machine to another machine.

- Server name is variable.

- database name is variable

- table name is fixed.

How do I specify a variable name for "server name" and "database name" ?

Are you taking about Server/Database name of your Data Sources?

For that you can use package configurations storing those names in a table, xml file, etc

Rafael Salas

|||

You can select the connection manager then go to the properties pane to set the expression for these properties. In your case, you'd want to change the server name and initialcatalog properties, which are very common. You could set these to a variable of course like in your requirement and then additionally later use a configuration file to externalize this all.

Also, the same type of concepts in this video would help you as well: http://www.jumpstarttv.com/Media.aspx?vid=34

-- Brian Knight

Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km

Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km