Changing Connections in DTS Packages

I have 4-5 DTS packages with more than 100 connections. If I want to change the connections of the source (Oracle Database) from Server 1 to Server 2, how do I do it dynamically...? I know that I can go to Disconnected Edit and change the connections manually , but it is a very tedious process as there are more than 100 connections.

Thanks.I'm moving this thread to the Microsoft SQL Server Forum where it will get attention from users with more Microsoft experience.

Do you actually have more than 100 connections (the Oracle Connection icon is the gold twin-disk-drives)? I have not EVER seen that happen, and I've seen a lot of Oracle to SQL Server DTS packages!

-PatP|||Pat

Thanks for the reply. If I add up all the connections in 3-4 packages I will have 100 connections.

Let me know if you know any solution.
Thanks.sql

Changing connections (Does't work with "Script Task"...)

Hi,

I've a simple package which basically copies a few record from a Source connection to a Destination connection.
This package must run ten times, every time with a different Source and Destination.

I dont want to duplicate the tasks inside the package and set the right connections at design time. I'm looking for a way to parametrize the connections.

In DTS2000 I could use an "Activex Script" to access the Package's Connections and change them at design time. The new "Script Task" does not allow access to others task, so it can't be done anymore. (There's is still the "Activex Script" task on my toolbox, but i get errors trying to place it on the design surface).

Package Configurations also does not seems the right way: to use multiple configurations I should invoke the package from the command line multiple times with different command line parameters, while I wuold like to have everything in one place (ie 1 package file instead of 1 batch file, 1 package file and 10 package configurations files).

Regards,
CorradoYou are no longer allowed to access task and package properties at runtime and change things. Part of the rational is that it makes for potentially unstable and unpredicatble packages. So you know have a structured way of doing this through configurations and expressions.

Expressions can be set on connection, task and some component properties. Most UI's expose connections, or they are also available in the VS properties grid.

For example you could use an Exec SQL Task to get a recordset of connection information. You could then use the For Each Loop container to crack this, and for each iteration assign the value(s) to a variable. You can then use the variable(s) within an expression on the connection property(s).

Think expressions instead of ActiveX Script Tasks for glue code.

Changing connection string to data source

Can I change databases for a SqlDataSource in code behind and still use the edit,inset,delete capabilities of the SQLDataSource control?

yes u can..

for change of connection string

SqlDataSource1.ConnectionString =ConfigurationManager.ConnectionStrings[0].ToString();

or

SqlDataSource1.ConnectionString ="UR connection string";

Changing Column Length

I have a table which gets it feed from external source. I need to chnge the
length of the column on DBs which are in Prod. The table has couple of
indexes and FK relationship (NOT on the column that I need to change).
Number of records is around 36 million. I have never done something like
this before. Will there be any issues that I need to be aware off?If you are increasing the length of a varchar column using ALTER
TABLE...ALTER COLUMN, only meta-data changes are needed so the operation
should be very fast. This acquires a Sch-M lock though, so no other users
can use the table during the ALTER.
Hope this helps.
Dan Guzman
SQL Server MVP
"XXX" <sa@.nomail.com> wrote in message
news:uePmOBn2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I have a table which gets it feed from external source. I need to chnge the
>length of the column on DBs which are in Prod. The table has couple of
>indexes and FK relationship (NOT on the column that I need to change).
>Number of records is around 36 million. I have never done something like
>this before. Will there be any issues that I need to be aware off?
>

Change XML/XSD Source Location - Gives Lineage Errors!

Hi,

I have developed a SSIS package on my desktop and the package involves loading of XML data into a database. The XML does not have inline schema and I generated the XSD file from SSIS.

I used Derived Column and Data Conversion to load the data into the database.

Now, i want to migrate the package to a server. Now, when i change the path of the XML and XSD files, all the tasks show error such as

"Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow...."

Why is this so? I am using the same XML/XSD files after i moved to the server.

Please advice a fix for this?

Regards,

Vikram

I was looking into this and i think i understand why this is happenning:

When we actually select a XML/XSD file, for every column which flows throught he data flow, SSIS assigns it a Lineage ID which is Unique and this Lineage ID is used internally.

When i change the XML/XSD file location, SSIS is assigning new Lineage ID's for the columns in the XML file.

The Data Flow tasks down of the XML source still try to look for the Lineage ID which was generated while the tasks were created.

As the Lineage ID's changed, they thrown error such as "Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow....". Here, 3586 is the Lineage ID which was created initially.

I am not sure how to avoid this error but once this error occurs, we have the open the immediate task after the XML source and fix it.

If anyone has an idea how to avoid this (This might help in large packages during migrations), please enlighten us.

Regards,

Vikram

Change values in SSIS.

Howdy all. In DTS there was a way too change the values of a column during
and export to another table using ActiveX Script:
Source:
Table1
column1
column2
Destination
Table2
column1 (I want this to contain the exact values from Table1.column1)
column2 (I want this to contain the values of Table1.column2 + 10)
Basically you would do this through the mappings, instead of a Copy Column,
you would use an AxtiveX script for that column. How can this be done in
SSIS?
TIA, ChrisRThanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5FAF7EA7-D0E7-41FB-AF37-B70DA73AE95E@.microsoft.com...
> Hi Chris
> You can use a script component as a transform task see
> http://www.sqlis.com/default.aspx?307,1
> John
> "ChrisR" wrote:
> > Howdy all. In DTS there was a way too change the values of a column
during
> > and export to another table using ActiveX Script:
> >
> > Source:
> > Table1
> > column1
> > column2
> >
> > Destination
> > Table2
> > column1 (I want this to contain the exact values from Table1.column1)
> > column2 (I want this to contain the values of Table1.column2 + 10)
> >
> > Basically you would do this through the mappings, instead of a Copy
Column,
> > you would use an AxtiveX script for that column. How can this be done in
> > SSIS?
> >
> > TIA, ChrisR
> >
> >
> >
> >
> >

Change the Shared Data Source when deploying

I am trying to move my reports (and data sources) from one server to another.
I can move everything fine, but when I go to execute a report, it says
invalid data source, even though the data source exists with the same name as
it did on the original server. I can manually go through each report and
point them to the data source and it works fine, but I need to get this
working with little or no intervention.
--
JasonMake sure you create the shared datasource first before deploying any
reports that reference it, it should get picked up fine then.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:8005DE7C-15C1-4AAC-82C4-83F1C3FFDB75@.microsoft.com...
>I am trying to move my reports (and data sources) from one server to
>another.
> I can move everything fine, but when I go to execute a report, it says
> invalid data source, even though the data source exists with the same name
> as
> it did on the original server. I can manually go through each report and
> point them to the data source and it works fine, but I need to get this
> working with little or no intervention.
> --
> Jason

Change Shared Data Source at runtime based on a parameter

I have 11 databases identical databases except that they have different usename and passwords. I want to setup 11 Shared Data Sources and set the report to choose a different Shared Data Source based off of a parameter. Is this possiable

Changing shared data sources dynamically is currently not supported.

The closest you can get through expressions in the report is to use a non-shared data source with an expression-based connection string. This option and other options are discussed in more detail in this relating thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16395&SiteID=1

-- Robert

Change script from MSAccess to SQL access.

I'm using this script I found on the web. After some tweaking here and there, it works great. I'd like to change it to pull data from a SQL source instead of Access. Anyone know how to do this, give me some pointers?

Here is the script
*****************************************************************************************************************

<%@. LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<%
Dim DATA_PATH, Conn, DataRecords, email, user, pass, sendmail
'Maps to database. Change to your database path.
DATA_PATH=Server.Mappath("membersdb.mdb")
' Create and intiate data connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 30
Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"
Set DataRecords = Server.CreateObject("ADODB.Recordset")
email=request.form("email")
'The magic query to look for registered members in the database
DataRecords.Open "SELECT * FROM MEMBERS WHERE email = '" & email & "'", Conn, 0, 1
%>
<%
'checks if email address exists in the database before sending a message.
if DataRecords.EOF then
%>
We could not find <%=email%> in our database.
<% Else %>
<%
'sets variables
email = request.form("email")
'chooses username and password from database that correspond to submitted email address.
user = DataRecords.Fields("usernames")
pass = DataRecords.Fields("password")
Set sendmail = Server.CreateObject("CDONTS.NewMail")
'put the webmaster address here
sendmail.From = "Someone@.somewhere.com"
'The mail is sent to the address entered in the previous page.
sendmail.To = email
'Enter the subject of your mail here
sendmail.Subject = "Membership Login Information"
'This is the content of thr message.
sendmail.Body = "Per your request your account login information is: " & vbCrlf & vbCrlf _
& "Username=" & user & vbCrlf _
& "Password=" & pass & vbCrlf
'this sets mail priority.... 0=low 1=normal 2=high
'sendmail.Importance = 3 commented out causes a error for a call or procedure (line 41)
sendmail.Send
%>
We just sent your login information to <%=email%>.
You should receive it shortly.
<%
' Close Data Access Objects and free connection variables
Conn.Close
Set DataRecords = Nothing
Set Conn = Nothing
Set sendmail = Nothing
%><%end if%>

***************************************************************************************************************
And here's my SQL connection stuff
Private Sub Class_Initialize()
ConnectionString = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx"
User = "usr"
Password = "pswrd"
Set Converter = New clsConverter
Converter.DateFormat = Array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss")
Converter.BooleanFormat = Array(1, 0, Empty)
Set objConnection = Server.CreateObject("ADODB.Connection")
Database = "MSSQLServer"
Set Errors = New clsErrors
End Sub
****************************************************************************************************************

sure would appreciate some help!

You can use a different driver in the connection string. Connectionstrings can be found here: www.connectionstrings.com

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

As Jens suggested you should be able to simply replace

Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"

with

Conn.Open "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx;Password=<your pwd here>"

Please be careful about storing the password into the script itself, it is not secure.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Change partition Source through XMLA

Hi everyone,

I've got a small problem that I hope that someone can help me with. I am quite new to datawarehousing, so when I started out I didn't take partitioning measuregroups into consideration. Now my problem is taht the actual processing of my cubes takes to much time. Therfore I decided to start creating partitions, but since I have quite a lot of measuregroups I wanted to create them in SSIS.

This has worked out fine and the partitions are created, but since we are using standard edition of ssas 2005 I can't craete more than 3 partitions a measuregroup. Therfore I decided to alter my design a bit more and actually have one working partition and one history partition.

My questions are:

1. Since my old partition is based on a DsvTableBinding scheme, I guess I need to change them to QueryBinding instead. I guess that I will only have to do this the first time and afterwards, when the working partition is merged with the history partition everything should be working fine without any problems of double counting.

2. Are there any problems browsing a cube with this design? I mean if I have 2 different partition and want to look at data in both of them at the same time?

I hope this makes sense :)

Best Regards

Stefan Ghose

Hello Stefan,

You approach makes sense and should work. Browsing both partitions will be posible. However you should pay attention to the following:

1. Depending on the amount of your data, the historical partition might grow and eventually can became very large, which will slow down data retrival from it.

2. Merging partitions works quite well and tries to make the best job possible of avoiding fragmentation, but nevertheless some fragmentation will happen, which can cause both data expansion and query slowdown. Once in a while you'd need to do Full Process of the historical data.

3. Usually during partition merge you need to pay attention to aggregations in the result. (Result will have only agregations available in both partitions). With your approach, I don't foresee any problems, because probably your current partion will have the same aggregations (or more) then historical one.

4. It's not required, but still recomended to set the partition slice.

Hope this helps,

Irina

|||

Hi Irina,

could you please describe benefits of the partition slice setting. Where AS earns income from this feature? Only during quering or someweher else?

|||

The following article explains quite well about slices and filters. It refers to AS 2000 but has a lot of good information:

http://msdn2.microsoft.com/en-us/library/aa902650(sql.80).aspx

Search for "Slices and Filters"

|||

Hi Irina,

thank you for the answers. It helped me set up a scalable design that I will be able to reuse for all my fact table. I combined my needs with a post by Jamie on blogs.conchango.com

http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx

It works really well, and I have managed to shave an hour off my measure group processing.

Kind Regards

Stefan Ghose

change management for SQL source code

a question for you MVPs..
Is there a standard for SQL source code change management. I can do this with all my VB & .NET code using SourceSafe and a build and deploy application that validates all the source so I can deploy from a label giving me full versioning capability of my code. Is there something out there that can do the same for SQL?
regards
Mike Daviesmike
I am not a MVP as you have asked but let me give you some article to read
http://www.sql-server-performance.com/gr_change_managemenet.asp
"mike davies" <anonymous@.discussions.microsoft.com> wrote in message
news:8A57742F-51CE-4CED-8F30-091C0AC1BA60@.microsoft.com...
> a question for you MVPs...
> Is there a standard for SQL source code change management. I can do this
with all my VB & .NET code using SourceSafe and a build and deploy
application that validates all the source so I can deploy from a label
giving me full versioning capability of my code. Is there something out
there that can do the same for SQL?
> regards,
> Mike Davies|||Unfortunately there still doesn't seem to be a tool that can help me with my process. With my procedural code (VB and .NET code) there are many applications out there that give me what I want. I know there are tools that say they provide change management - but they are, are tools to show the differences between databases. How do I know the database is a reflection of my source code? I could still be deploying broken code. Sadly they are lacking and claim to be something they are not.
Doesn't anyone know of a tool that can give me the auditability of my source code that I desire.
regards,
Mark Baekdal

change management for SQL source code

a question for you MVPs...
Is there a standard for SQL source code change management. I can do this wit
h all my VB & .NET code using SourceSafe and a build and deploy application
that validates all the source so I can deploy from a label giving me full ve
rsioning capability of my c
ode. Is there something out there that can do the same for SQL?
regards,
Mike Daviesmike
I am not a MVP as you have asked but let me give you some article to read
http://www.sql-server-performance.c...managemenet.asp
"mike davies" <anonymous@.discussions.microsoft.com> wrote in message
news:8A57742F-51CE-4CED-8F30-091C0AC1BA60@.microsoft.com...
> a question for you MVPs...
> Is there a standard for SQL source code change management. I can do this
with all my VB & .NET code using SourceSafe and a build and deploy
application that validates all the source so I can deploy from a label
giving me full versioning capability of my code. Is there something out
there that can do the same for SQL?
> regards,
> Mike Davies|||Unfortunately there still doesn't seem to be a tool that can help me with my
process. With my procedural code (VB and .NET code) there are many applicat
ions out there that give me what I want. I know there are tools that say the
y provide change management
- but they are, are tools to show the differences between databases. How do
I know the database is a reflection of my source code? I could still be depl
oying broken code. Sadly they are lacking and claim to be something they are
not.
Doesn't anyone know of a tool that can give me the auditability of my source
code that I desire.
regards,
Mark Baekdal