Promoting DTS pacakages

Environment: SQL Server 2000

I have made a few DTS packages (in testing environment). Now I have to promote them to production environment. Whats the best way to do this? One of the ways I can think of is save the DTS packages in visual basic format and go through the VB code and check for any dependent variables etc. But once a DTS packages is saved as VB code it cant be saved back as a package. So how would you use the DTS package (in VB code) on SQL Server 2000.Open the package in Design.

Click Package, Save As...

Select the server you would like to save the package to.

Open it on the new server and check for dependencies.

That's all there is to it.|||Hi!

For transferring a DTS-Package from one Server to another or for Backup purposes I use a little free tool called "DTSBackup 2000". You can find the description and the download link on the following page (http://www.sqldts.com/?242).

Hope that makes it easier!
Greetings,
Carsten|||Originally posted by CarstenK
Hi!

For transferring a DTS-Package from one Server to another or for Backup purposes I use a little free tool called "DTSBackup 2000". You can find the description and the download link on the following page (http://www.sqldts.com/?242).

Hope that makes it easier!
Greetings,
Carsten

Thanks, the software helped me out.

programmatically schedule the package for later execution in store procedure -

Hello,
Is it possible programmatically schedule the DTS package for later execution in store procedure or maybe in trigger?When you say later execution, please be more specific and describe the scenario you are trying to apply this to.|||Originally posted by rnealejr
When you say later execution, please be more specific and describe the scenario you are trying to apply this to.

For example, user sets flag thru user interface. This action updates some field in table. I would like to have an "update" trigger for this table that will schedule the DTS package to be executed at night. There is some option to do it in VB but it will be cool to perform this thru store procedure.|||You can use the sp_add_schedule or sp_update_schedule - however, a job must already exist for the DTS package.|||Thank you. It's msdb..sp_update_jobschedule.

Thanks again!|||Happy to help.

Programmatically change property

In the old DTS, we can use the ActiveX Script to change any task's property programmatically.

Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?

Say, I want to change the Fuzzy look up reference table name.

Can we do it?

Hi,

No, you can't do this using the Script Task.

You CAN change any property of a task or container at runtime using a property expression: http://www.google.co.uk/search?hl=en&q=ssis+property+expression&meta=

You can change some properties of components (Fuzzy Lookup is a comoponent, not a task) at runtime using the same technique, but not many. The component properties that can be changed by property expressions are surfaced in the properties pane for the task in which the component resides.

-Jamie

Programmatic killing connections

We want to create canned database restore jobs - possibly
as DTS packages.
We believe we need a way to find and kill database
connections, so that the actual restore can proceed.
Anyone done this ?
TIA
Hi Jim
Killing connections is one way. Another is to put the database into single
user mode, using the rollback immediate option, eg:
alter database [dbname] set single_user with rollback immediate
Check out ALTER DATABASE in Books Online for more info:
http://msdn.microsoft.com/library/en...aa-az_4e5h.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:ba1001c479c2$5c468bc0$a601280a@.phx.gbl...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA
|||I found a sample script to close all connections on:
http://khsw.blogspot.com/2004/08/sto...nnect-all.html
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message news:<ba1001c479c2$5c468bc0$a601280a@.phx.gbl>...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA

Programmatic killing connections

We want to create canned database restore jobs - possibly
as DTS packages.
We believe we need a way to find and kill database
connections, so that the actual restore can proceed.
Anyone done this ?
TIAHi Jim
Killing connections is one way. Another is to put the database into single
user mode, using the rollback immediate option, eg:
alter database [dbname] set single_user with rollback immediate
Check out ALTER DATABASE in Books Online for more info:
http://msdn.microsoft.com/library/e..._aa-az_4e5h.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:ba1001c479c2$5c468bc0$a601280a@.phx.gbl...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA|||I found a sample script to close all connections on:
http://khsw.blogspot.com/2004/08/st...onnect-all.html
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message news:<ba1001c479c2$5c468
bc0$a601280a@.phx.gbl>...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA

Programmatic killing connections

We want to create canned database restore jobs - possibly
as DTS packages.
We believe we need a way to find and kill database
connections, so that the actual restore can proceed.
Anyone done this ?
TIAHi Jim
Killing connections is one way. Another is to put the database into single
user mode, using the rollback immediate option, eg:
alter database [dbname] set single_user with rollback immediate
Check out ALTER DATABASE in Books Online for more info:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_4e5h.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:ba1001c479c2$5c468bc0$a601280a@.phx.gbl...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA|||I found a sample script to close all connections on:
http://khsw.blogspot.com/2004/08/stored-procedure-to-disconnect-all.html
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message news:<ba1001c479c2$5c468bc0$a601280a@.phx.gbl>...
> We want to create canned database restore jobs - possibly
> as DTS packages.
> We believe we need a way to find and kill database
> connections, so that the actual restore can proceed.
> Anyone done this ?
> TIA

programatically move to the next column

Hello All...
I am writing to an .xls via an ActiveX script in a DTS.
I need to programatically move to the next column when
the data dictates.
After checking params, I'd like to simply say...
If I am currently in column B, I need to make the
CurCol (Current Column) = C
Is there anything that works like
CurCol = Select 'b' + 1
Thanks in advance,
bob mcclellanNot sure if I follow your requirements, but using column names directly is
the most reliable approach. If you want the "column number" for some reason,
you'd have to query the metadata, for instance like:
DECLARE @.index INT
SET @.index =1
SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
Anith|||Hello Anith.
I simply want to increment alphabetically.
I know that I can create a table with all the columns of
a spreadsheet and then increment through the table
but I was wondering if there was a way to simply say
what comes after B and then what comes after C...
the same way that you can say
declare @.i int
Select @.i = 1
Select @.i = @.i +1
I would like to say
declare @.c char(2)
Select @.c = 'A'
Select @.c = @.c + 1
I just want to gain control over what column in a spreadsheet I
am writing to.
like... .range(@.c + "1) = myValue
Thanks in advance,
bob mcclellan
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eYudD1KCFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Not sure if I follow your requirements, but using column names directly is
> the most reliable approach. If you want the "column number" for some
> reason, you'd have to query the metadata, for instance like:
> DECLARE @.index INT
> SET @.index =1
> SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
> --
> Anith
>|||One common approach is to use a staging table that corresponds to your
source data and then manipulate the data into your final table
structure using an INSERT...SELECT.
ActiveX does support referencing a column by position number in a
fields collection but I'm not sure that will help you very much. Maybe
if you explain the actual problem someone can help you with an
alternative. Why can't you reference columns by name?
--
David Portas
SQL Server MVP
--|||I misread your post. I though you were imporiting an Excel spreadsheet
rather than exporting it.
Is SQL Server the data source? If so, I expect it's possible to write a
SELECT statement that prepares the data in the form your spreadsheet
requires. If you want help, please post DDL for your tables, sample
data INSERTs and show your required end result.
--
David Portas
SQL Server MVP
--|||Hello David...
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much.
--this is part of the code that I use to write the labels into Column A.
----
--
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Save
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
----
--
I've created a proc that builds the cross ref table I need...
To identify each Column increment so that I can dynamically
write the cell range to the column I need ...
I'm sorry David if this explanation is not Clear....
I can make it work, using the table created from the following proc...
Thanks in advance,
bob mcclellan
----
--
alter Proc AlphaIncrement_MakeTable
as
Set NoCount On
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'AlphaIncrement')
DROP TABLE AlphaIncrement
CREATE TABLE AlphaIncrement
(
Alpha_ID int
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Letter char(2) Not Null
)
declare @.i int
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'a'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'b'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'c'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'd'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'e'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'f'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'g'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'h'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 119
begin
Insert into AlphaIncrement (Letter)
SELECT 'i'+CHAR(@.i)
Select @.i = @.i + 1
end
select * from AlphaIncrement order by alpha_ID
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354249.455002.35430@.l41g2000cwc.googlegroups.com...
> One common approach is to use a staging table that corresponds to your
> source data and then manipulate the data into your final table
> structure using an INSERT...SELECT.
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much. Maybe
> if you explain the actual problem someone can help you with an
> alternative. Why can't you reference columns by name?
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David...
I really appreciate it.
I actually worked through this prior to reading your post.
Thanks again...
bob mcclellan
Following is the script I put together ....
'***************************************
*********************************
'D:\Common\Mailers\Batches\AvgRatesByCla
ss\Avg.Rates.By.Class.xls
'Populate Spreadsheet with Monthly Rates
'***************************************
*********************************
Function Main()
Dim xlApp
Dim xlBook
Dim i
Set xlApp = CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open(" D:\Common\Mailers\Batches\AvgRatesByClas
s\Avg.Rates.By
.Class.xls")
Dim sqlText
sqlText = "select arc_ID, Class from avgRatesClasses"
sqlColsText = "Select * from alphaIncrement"
dim con
dim rs
dim rsCols
set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")
set rsCols = createobject("ADODB.recordset")
con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
= MyWorkDB; user id = 'me'; password ='mypassword';"
rs.open sqltext, con
rsCols.open sqlColstext, con
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Worksheets(1).Range("A1") = "CLASS"
xlBook.Save
rs.movenext
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
sqlText = "Select c.arc_id, d.* from avgRatesClassesData d " _
& " inner join avgRatesClasses c on " _
& " d.class = c.class " _
& "order by yr desc, Mnth desc, d.class " _
rs.close
rs.open sqltext, con
'Move to Column B
rsCols.movenext
dim CurMnthDate
CurMnthDate = rs.fields("yrMnth")
dim CurCol
CurCol = rtrim(rsCols.fields("letter"))
with xlBook.Worksheets(1)
.Range(CurCol & 1) = rs.fields("yrMnth")
do while not rs.eof
if CurMnthDate <> rs.fields("yrMnth") then
rsCols.movenext
CurCol = rtrim(rsCols.fields("letter"))
.Range(CurCol & "1") = rs.fields("yrMnth")
CurMnthDate = rs.fields("yrMnth")
end if
.Range(CurCol &rs.fields("arc_id")) = rs.fields("avgRate")
rs.movenext
loop
end with
xlBook.Save
rs.close
set rs = nothing
rsCols.close
set rsCols = nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354731.006660.43150@.g14g2000cwa.googlegroups.com...
>I misread your post. I though you were imporiting an Excel spreadsheet
> rather than exporting it.
> Is SQL Server the data source? If so, I expect it's possible to write a
> SELECT statement that prepares the data in the form your spreadsheet
> requires. If you want help, please post DDL for your tables, sample
> data INSERTs and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.

Programatically creating dataflow tasks require assembly reference

Hi,

I am creating dataflow tasj using following

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.

How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?

Thanks

Mohit

The assemblies should be in GAC on production machine as well, if you selected SSIS during install. Selecting Workstation Components > SDK also puts them to C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies. Make sure you install SSIS and SDK features on production machine.|||

Do you really need to SDK on production or server machines? Surely the base SSIS install would put them in the GAC and that should be sufficient.

|||Probably not if the script component is precompiled. I'm not sure about non-precompiled script component - it may need SDK directory to compile the code, but I'm not sure.