Hi All,
I have a Column with identity(1,1) .
Using script how to set that to identity(2,2)
Thanks in advance.
SupriyaAssume your original table with identity (1,1) is called test. You can go
through the script below to change the seed and increment.
CREATE TABLE dbo.Tmp_test
(
id int NOT NULL IDENTITY (2, 2),
string varchar(90) NULL
)
SET IDENTITY_INSERT dbo.Tmp_test ON
IF EXISTS(SELECT * FROM dbo.test)
INSERT INTO dbo.Tmp_test (id, string)
SELECT id, string FROM dbo.test WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo.Tmp_test OFF
DROP TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||Well, I thought I remembered that you could do this, so I tried to whomp a
little something up. Couldn't get it to work. But then I focus on 2000 docs,
while using 2005. So finally, I looked at the 2005 docs: this is an exact
copy/paste from 2005 BOL: ALTER TABLE:
--
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
--
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'.
--
If the supported syntax doesn't match BOL, we got a problem somewhere. So,
if this is true, then you're stuck creating a new table and doing the
insert.
Sorry,
Jay
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||On Thu, 13 Sep 2007 06:48:38 -0700, Jay wrote:
>Well, I thought I remembered that you could do this, so I tried to whomp a
>little something up. Couldn't get it to work. But then I focus on 2000 docs,
>while using 2005. So finally, I looked at the 2005 docs: this is an exact
>copy/paste from 2005 BOL: ALTER TABLE:
>--
>CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
>CompanyName NvarChar (50))
>INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
>ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
Hi Jay,
If you go to that page in Books Online again, you'll see at the top of
the screen that this applies to SQL Server 2005 Compact Edition. This is
a different product, that is for some mysterious reason documented in
the same version of BOL (and I've lost track of the number of times I
myself felll victim to this misunderstanding - especially since the CE
version of BOL tends to come up before the "regular SQL Server" docs).
Changing identity is only supported on CE, and not on "normal" editions
of SQL Server.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
google for DBCC CHECKIDENT RESEED
isnt this the easiest way?|||does work on 2005.
use tempdb
if object_id('t') is not null drop table t
CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
(10))
insert into t(col2) values('one')
DBCC CHECKIDENT (t, RESEED, 200)
insert into t(col2) values('two')
select * from t
"densial" <densial@.gmail.com> wrote in message
news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
> google for DBCC CHECKIDENT RESEED
> isnt this the easiest way?
>|||I get it now.
This isn't resetting the initial value, or the increment, it is setting the
highest value used so the next value comes in above it.
Not what the OP asked for, usefull, but it doesn't change the increment from
1, to 2.
"Jay" <spam@.nospam.org> wrote in message
news:eGmGiZG%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> does work on 2005.
> use tempdb
> if object_id('t') is not null drop table t
> CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
> (10))
> insert into t(col2) values('one')
> DBCC CHECKIDENT (t, RESEED, 200)
> insert into t(col2) values('two')
> select * from t
> "densial" <densial@.gmail.com> wrote in message
> news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
>> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
>> google for DBCC CHECKIDENT RESEED
>> isnt this the easiest way?
>sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Friday, March 23, 2012
How to change default script generation options in SQL 2005 tools or VS 2005
Ok, this has been driving me crazy in both SQL Management Studio and VS 2005 Server Explorer. How do you change the default script generation options?
When I right-click a stored procedure, table, etc. in the SQL Management Studio's Object Explorer, or in the VS 2005 Server Explorer and choose to generate a create script for that object it does not prompt me for any scripting options (like do I want to script dependant objects (where my answer is a resounding NO! but the default that ships with the tools is yes)).
I wouldn't mind complaining that I should be able to select multiple items in these tools and generate scripts for all selected items all at once. This doesn't seem to happen in Management studio at all, and if you do this in VS 2005's server explorer it always throws all your selected objects into one big ugly script file. I know that is more a vs issue than a SQL one, but if anyone has an answer to that I'd appreciate it.
In SQL management studio, in the Generate Scripts Wizard (the only way to gen more than one object at a time) is there a way to 1) make it create different files for each object I script and 2) is there, PLEASE, a way to stop the generated scripts for procs/functions from generating the IF statement to check to see if the object exists before the create statement? The reason for this is that I really do not want my create statement executed via the EXEC command as a string. You loose all your color coding, and if you generated in VS 2005 the editor has really annoying behavior when you use the CTRL + Arrow keys inside a string and you also loose the ability to edit the SQL block using the GUI query builder.
Anyway, any help with this would be appreciated.
Thanks
Hi, did you ever get a solution to this?(I've come across the same problem)
Thanks,
Steve|||
There is an options page in the RTM version of the Generate Scripts Wizard in SQL Server Management Studio. There is an option for "Generate Script for Dependent Objects", and the default is "False."
There is also an option for "Include If NOT EXISTS", but the default there is "True." Setting the option to "False" makes the wizard omit the check.
The Generate Scripts Wizard can copy the generated SQL to a single file, the clipboard, or to a new SQL editor instance in SSMS.
|||Hi,My specific problem was the fact that there was no options page if you chose to script the object by right-clicking the object in SQL Mgmt Studio.
I know if you right-click the database, that does open a wizard window and lets you change options but not if you right-click the object, or use VS2005 to script the object.
In the old VS2003, when you dragged an object from the Server Explorer to the Solution Explorer it would pop-up the script wizard window, but now it doesn't and seems to use it's own settings (which I can't change!)
Thanks,
Steve|||
You're right, the script menu items in SSMS for the objects themselves have no configurable options. I've added this as a feature request for a future release of SQL Server Management Studio.
|||Excellent, thanks!
Stevesql
Wednesday, March 21, 2012
How to change columns to rows
I have a need to change the columns in a table to rows with values
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>
Monday, March 19, 2012
How to catch error and retry AFTER dts script step has ran
hello,
i am trying to figure out how to check for failure or success AFTER the script task has ran.
its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?
i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.
dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2
the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.
i have a java programming background. can anyone see this from my point of view and give me a hint?
this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...
i am trying to figure out how to check for failure or success AFTER the script task has ran.
its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?
i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.
dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2
the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.
i have a java programming background. can anyone see this from my point of view and give me a hint?
this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...
Monday, March 12, 2012
How to capture Error Messages in script
I know how to capture the error number using @.@.ERROR, but I don't know how
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>
how to call web service from within Script Component ?
Appreciate if anyone can show me the code to call a web service from Script Component ?
I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.
Thanks !!
Can you not populate a staging table to be used as such: - After the data flow completes loading a staging table, you'll have an execute SQL task in the control flow to select the data out of that table and store it in an Object variable.
- Use a foreach loop to loop through that object variable, mapping the various columns (if any) to variables
- Inside the foreach loop will be your Web service task. Each time it is called it will use the current value of the parameter variables
- After the foreach loop, you can add another execute SQL task to truncate the staging table if desired.
Does that make sense? At a high level anyway?|||
I think i got what you're saying. However, there is a lot of data. When we go through the data row by row, we take the row data and do 20 different things (update this and that in the database). There is one thing that depends on the output from the webservice.
So, if we use a staging table, memory could become an issue, maybe ? Also, looks like i'll have to to create Data Flow task "twice". The first Data flow task will read the flat file, do some lookup, etc, and put data into a staging table. Then call Web Service Task in the Control Flow. Now that i have the output from the webservice, i need to pass this big object variable back to a Data Flow task for further processing.
I'm just hoping there is a easy way to call the webservice from Script component.... is there a way ?
thanks
|||mf915 wrote:
Appreciate if anyone can show me the code to call a web service from Script Component ?
I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.
Thanks !!
Hi there,
If you buy Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/sr=8-1/qid=1171045629/ref=pd_bbs_sr_1/102-7891523-4086513?ie=UTF8&s=books) there is a downloadable extra chapter that explains EXACTLY how to do this. Many a time I have considered blogging it but that would effectively be plagiarsing Donald's book and I don't want to do that.
-Jamie
|||thanks ! but the book is not available from rationalpress.com until March :(|||
thanks ! but the book is not available from rationalpress.com until March :(
Don't worry. You can get the beta preview version. It contains the same stuff: http://www.amazon.com/Rational-Scripting-Integration-Services-Preview/dp/1932577211/sr=1-2/qid=1171050141/ref=sr_1_2/102-7891523-4086513?ie=UTF8&s=books
-Jamie
|||Here's one way to use a web service from within an SSIS script component.
1. From the command line, create a wrapper class for the web service using wsdl.exe
wsdl /language:VB http://ws.strikeiron.com/relauto/iplookup?WSDL /out:IPLookup.vb
2. Create an SSIS dataflow, adding in a script component transform.
3. In the script transform, from the Project menu, use "Add Existing Item.." and pull in the wrapper class generated in step 1. You may need to delete the first first few "garbage" characters, which are the Unicode byte order mark.
4. From the Project Menu again, choose Add Reference, and select both the System.Xml and System.Web.Services assemblies.
5. Reference the class from within the transform.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Private ws As DNS = New DNS()
' Wrapper for web service
Private dnsInfo As DNSInfo
' wrapper for Response from web service
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
Dim server As String = Row.ServerName
dnsInfo = ws.DNSLookup(server)
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, _
String.Format(" {0} maps to IPv4 {1}", server, dnsInfo.AddressList(0)), _
String.Empty, 0, True)
Catch ex As Exception
ComponentMetaData.FireError(1, ComponentMetaData.Name, _
ex.Message, String.Empty, 0, True)
End Try
End Sub
End Class
|||
Spot on as usual jaegd. This is how Donald describes doing it.
-Jamie
|||Well, I've been able to sucessfully connect to a web service (in my case a Sharepoint List) in the script component using the method described above, and now things are starting to get messy. I'm wondering if anyone has any advice or a "best practice suggestion" for the following scenario, where I'm taking the XML response of a web service and pushing into the data flow using the "XML Source" component:
1. In the script component, query a web service and get the XML response.
1b. Remove multiple namespaces from the XML response using XSLT, since the XML Source component can't handle them.
2. Get the schema of the web service response using the DataSet class/GetXMLSchema method.
3. Push the XML response data into a variable to be read by the "XML Source" component in the dataflow.
4. Write the inferred XML schema to a file. The file name is set using the System::ExecutionInstanceGUID, which ensures a unique file name linked to the particular package execution.
5. Open up the expressions for the data flow task, and set the XMLSchemaDefinition property for the XML Soruce Component to point to the schema file just written. Because this does file not exist at validation time, the data flow task property DelayValidation must be set to True.
Of course, it would be impossible to even create a data flow without having some sort of "starting point" schema to work with. I'm just trying to construct some sort of framework so I can add or remove an item from the Sharepoint List I'm accessing using via Web Service and it doesn't cause my package to fail all over the place because the schema doesn't exactly match the data.
With this (or perhaps a simpler, easier approach?) the dynamically generated schema can change as I make small changes to my sharepoint lists. I'm wondering if I'm making this too complicated, or if it would just be easier to have a maually-maintained repository of XML schemas somewhere in the filesystem.
|||I would just maintain your own XML schemas, and pass the values in through configurations. I'm not sure if there is a simpler or easier approach to a package that has a dynamic schema. Your destinations will have problems with flucuating schemas as well....and from a brief search on the forum building a package dynamically at runtime may be your only option.how to call web service from within Script Component ?
Appreciate if anyone can show me the code to call a web service from Script Component ?
I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.
Thanks !!
Can you not populate a staging table to be used as such: - After the data flow completes loading a staging table, you'll have an execute SQL task in the control flow to select the data out of that table and store it in an Object variable.
- Use a foreach loop to loop through that object variable, mapping the various columns (if any) to variables
- Inside the foreach loop will be your Web service task. Each time it is called it will use the current value of the parameter variables
- After the foreach loop, you can add another execute SQL task to truncate the staging table if desired.
Does that make sense? At a high level anyway?|||
I think i got what you're saying. However, there is a lot of data. When we go through the data row by row, we take the row data and do 20 different things (update this and that in the database). There is one thing that depends on the output from the webservice.
So, if we use a staging table, memory could become an issue, maybe ? Also, looks like i'll have to to create Data Flow task "twice". The first Data flow task will read the flat file, do some lookup, etc, and put data into a staging table. Then call Web Service Task in the Control Flow. Now that i have the output from the webservice, i need to pass this big object variable back to a Data Flow task for further processing.
I'm just hoping there is a easy way to call the webservice from Script component.... is there a way ?
thanks
|||mf915 wrote:
Appreciate if anyone can show me the code to call a web service from Script Component ?
I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.
Thanks !!
Hi there,
If you buy Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/sr=8-1/qid=1171045629/ref=pd_bbs_sr_1/102-7891523-4086513?ie=UTF8&s=books) there is a downloadable extra chapter that explains EXACTLY how to do this. Many a time I have considered blogging it but that would effectively be plagiarsing Donald's book and I don't want to do that.
-Jamie
|||thanks ! but the book is not available from rationalpress.com until March :(|||
thanks ! but the book is not available from rationalpress.com until March :(
Don't worry. You can get the beta preview version. It contains the same stuff: http://www.amazon.com/Rational-Scripting-Integration-Services-Preview/dp/1932577211/sr=1-2/qid=1171050141/ref=sr_1_2/102-7891523-4086513?ie=UTF8&s=books
-Jamie
|||
Here's one way to use a web service from within an SSIS script component.
1. From the command line, create a wrapper class for the web service using wsdl.exe
wsdl /language:VB http://ws.strikeiron.com/relauto/iplookup?WSDL /out:IPLookup.vb
2. Create an SSIS dataflow, adding in a script component transform.
3. In the script transform, from the Project menu, use "Add Existing Item.." and pull in the wrapper class generated in step 1. You may need to delete the first first few "garbage" characters, which are the Unicode byte order mark.
4. From the Project Menu again, choose Add Reference, and select both the System.Xml and System.Web.Services assemblies.
5. Reference the class from within the transform.
Imports SystemImports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Private ws As DNS = New DNS()
' Wrapper for web service
Private dnsInfo As DNSInfo
' wrapper for Response from web service
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
Dim server As String = Row.ServerName
dnsInfo = ws.DNSLookup(server)
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, _
String.Format(" {0} maps to IPv4 {1}", server, dnsInfo.AddressList(0)), _
String.Empty, 0, True)
Catch ex As Exception
ComponentMetaData.FireError(1, ComponentMetaData.Name, _
ex.Message, String.Empty, 0, True)
End Try
End Sub
End Class
|||
Spot on as usual jaegd. This is how Donald describes doing it.
-Jamie
|||
Well, I've been able to sucessfully connect to a web service (in my case a Sharepoint List) in the script component using the method described above, and now things are starting to get messy. I'm wondering if anyone has any advice or a "best practice suggestion" for the following scenario, where I'm taking the XML response of a web service and pushing into the data flow using the "XML Source" component:
1. In the script component, query a web service and get the XML response.
1b. Remove multiple namespaces from the XML response using XSLT, since the XML Source component can't handle them.
2. Get the schema of the web service response using the DataSet class/GetXMLSchema method.
3. Push the XML response data into a variable to be read by the "XML Source" component in the dataflow.
4. Write the inferred XML schema to a file. The file name is set using the System::ExecutionInstanceGUID, which ensures a unique file name linked to the particular package execution.
5. Open up the expressions for the data flow task, and set the XMLSchemaDefinition property for the XML Soruce Component to point to the schema file just written. Because this does file not exist at validation time, the data flow task property DelayValidation must be set to True.
Of course, it would be impossible to even create a data flow without having some sort of "starting point" schema to work with. I'm just trying to construct some sort of framework so I can add or remove an item from the Sharepoint List I'm accessing using via Web Service and it doesn't cause my package to fail all over the place because the schema doesn't exactly match the data.
With this (or perhaps a simpler, easier approach?) the dynamically generated schema can change as I make small changes to my sharepoint lists. I'm wondering if I'm making this too complicated, or if it would just be easier to have a maually-maintained repository of XML schemas somewhere in the filesystem.
|||I would just maintain your own XML schemas, and pass the values in through configurations. I'm not sure if there is a simpler or easier approach to a package that has a dynamic schema. Your destinations will have problems with flucuating schemas as well....and from a brief search on the forum building a package dynamically at runtime may be your only option.Friday, March 9, 2012
How to call a sub-package by name only?
You could of course simply use an Active Script task and call the package
that way OR an ExecuteProcess task with DTSRUN.
I have just followed the article and it works for me.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that has several "Execute Package Task" elements. I'd
like to make the
> sub-packages version-independent. I ran across this blurb:
> http://www.sqldts.com/default.aspx?216
> Which talks about blanking the PackageID. I've done exactly as this has
described (both
> by blanking the PackageID with "Disconnected Edit" *and* by having a blank
Global Variable
> that I set the PackageIDs to). However, when I invoke my outer package, I
keep getting an
> "invalid GUID".
> Any help would be greatly appreciated. Thanks!
> John Peterson
>
Thanks Allan!
I *think* I might be having some issues with the blank global variable being "<not
displayable>". That is, when I use the Global Variable editor to "blank" out my PackageID
GV, everything works. However, if I save my package (as Structured Storage), exit the
package, and re-run it, I consistently get the "Invalid GUID specified" error. If I edit
the PackageID GV to blank it out again, then it works.
I even added a ActiveX task to set the PackageID GV:
' Ensure that the PackageID global variable is blank.
DTSGlobalVariables("PackageID").Value = ""
But upon initial load of the package, it still fails until I manually "dink" that GV.
If you have the time and inclination, could you try that and let me know of your results?
Many regards,
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> You could of course simply use an Active Script task and call the package
> that way OR an ExecuteProcess task with DTSRUN.
> I have just followed the article and it works for me.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> like to make the
> described (both
> Global Variable
> keep getting an
>
|||What I think then is happening is that you are saying "" which is not empty.
Why do you have a GV setting the Package GUID in the first place if you do
not want to use it?
I would remove this altogether or if you want to occasionally specify the
GUID and sometimes not then I would do it in an Active Script task where i
could decide to use/or not based on a certain condition
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I *think* I might be having some issues with the blank global variable
being "<not
> displayable>". That is, when I use the Global Variable editor to "blank"
out my PackageID
> GV, everything works. However, if I save my package (as Structured
Storage), exit the
> package, and re-run it, I consistently get the "Invalid GUID specified"
error. If I edit
> the PackageID GV to blank it out again, then it works.
> I even added a ActiveX task to set the PackageID GV:
> ' Ensure that the PackageID global variable is blank.
> DTSGlobalVariables("PackageID").Value = ""
> But upon initial load of the package, it still fails until I manually
"dink" that GV.
> If you have the time and inclination, could you try that and let me know
of your results?[vbcol=seagreen]
> Many regards,
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
package[vbcol=seagreen]
I'd[vbcol=seagreen]
has[vbcol=seagreen]
blank[vbcol=seagreen]
package, I
>
|||Sorry, I should have been more clear: I *always* want to call the sub-package by name
only, so I *do* want to use the GV to set the PackageID. The problem is, it seems like
DTS is sometimes replacing the actual string of "<not displayable>" for the PackageID when
I first load the package, which will then generate the "Invalid GUID specified" error. It
seems like I need to manually clear out the GV the first time I run it in the designer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> What I think then is happening is that you are saying "" which is not empty.
> Why do you have a GV setting the Package GUID in the first place if you do
> not want to use it?
> I would remove this altogether or if you want to occasionally specify the
> GUID and sometimes not then I would do it in an Active Script task where i
> could decide to use/or not based on a certain condition
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> being "<not
> out my PackageID
> Storage), exit the
> error. If I edit
> "dink" that GV.
> of your results?
> package
> I'd
> has
> blank
> package, I
>
|||John
If you never want to load the DTS package using either the Package and
Version GUID, then I'd do as Allan has suggested and put either,
a) Blank out the GUIDs using disconnected edit. They will only come back if
you edit that task in the designer.
b) Write a short ActiveScript task to blank out the GUIDs at the start of
the package.
Here is a script I use to accomplish that,
<-- Start -->
Dim oPkg
Dim colTasks
Dim sTsk
' open DTS package object so we can get the package details
Set oPkg = DTSGlobalVariables.Parent
' open object referring to Tasks collection
Set colTasks = oPkg.Tasks
' for each item in the collection
For Each sTsk in colTasks
' check if the task is an Execute Package task
If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
' create object referring to selected task
Set oTsk = oPkg.Tasks(sTsk.Name)
' blank out Package and VersionIDs so package loads by name only
' this will ensure that only the latest version is loaded.
oTsk.Properties("PackageID").Value = ""
oTsk.Properties("VersionID").Value = ""
' release object that was created
Set oTsk = Nothing
End If
Next
Set colTasks = Nothing
Set oPkg = Nothing
<-- End -->
Thanks
Phill
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more clear: I *always* want to call the
sub-package by name
> only, so I *do* want to use the GV to set the PackageID. The problem is,
it seems like
> DTS is sometimes replacing the actual string of "<not displayable>" for
the PackageID when
> I first load the package, which will then generate the "Invalid GUID
specified" error. It
> seems like I need to manually clear out the GV the first time I run it in
the designer.[vbcol=seagreen]
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
empty.[vbcol=seagreen]
do[vbcol=seagreen]
the[vbcol=seagreen]
i[vbcol=seagreen]
"blank"[vbcol=seagreen]
specified"[vbcol=seagreen]
know[vbcol=seagreen]
elements.[vbcol=seagreen]
this[vbcol=seagreen]
a
>
|||Thanks Phil!
Ah -- I thought I might need to constantly set the PackageID to blank (hence the global
variable approach). But it sounds like I really only need to do this *once* with the
Disconnected Edit feature (unless I edit the task again, of course).
Thanks for the sample script!
John Peterson
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> John
> If you never want to load the DTS package using either the Package and
> Version GUID, then I'd do as Allan has suggested and put either,
> a) Blank out the GUIDs using disconnected edit. They will only come back if
> you edit that task in the designer.
> b) Write a short ActiveScript task to blank out the GUIDs at the start of
> the package.
> Here is a script I use to accomplish that,
> <-- Start -->
> Dim oPkg
> Dim colTasks
> Dim sTsk
> ' open DTS package object so we can get the package details
> Set oPkg = DTSGlobalVariables.Parent
> ' open object referring to Tasks collection
> Set colTasks = oPkg.Tasks
> ' for each item in the collection
> For Each sTsk in colTasks
> ' check if the task is an Execute Package task
> If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
> ' create object referring to selected task
> Set oTsk = oPkg.Tasks(sTsk.Name)
> ' blank out Package and VersionIDs so package loads by name only
> ' this will ensure that only the latest version is loaded.
> oTsk.Properties("PackageID").Value = ""
> oTsk.Properties("VersionID").Value = ""
> ' release object that was created
> Set oTsk = Nothing
> End If
> Next
> Set colTasks = Nothing
> Set oPkg = Nothing
> <-- End -->
> --
> Thanks
> Phill
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> sub-package by name
> it seems like
> the PackageID when
> specified" error. It
> the designer.
> empty.
> do
> the
> i
> "blank"
> specified"
> know
> elements.
> this
> a
>
|||"...then you can remove the dependency on the PackageID, by blanking out the
PackageID property of the ExecutePackageTask... This can be done as a
one-off exercise with the Disconnected Edit feature..."
"If you regularly migrate packages and do not want to have to worry about
this, you can add a Dynamic Properties task to perform this during package
run-time..."
Is it not clear that you do this once, unless you change things?
Darren Green
http://www.sqldts.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> Thanks Phil!
> Ah -- I thought I might need to constantly set the PackageID to blank
(hence the global
> variable approach). But it sounds like I really only need to do this
*once* with the[vbcol=seagreen]
> Disconnected Edit feature (unless I edit the task again, of course).
> Thanks for the sample script!
> John Peterson
>
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
of[vbcol=seagreen]
Then[vbcol=seagreen]
is,[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
not[vbcol=seagreen]
you[vbcol=seagreen]
specify[vbcol=seagreen]
where[vbcol=seagreen]
variable[vbcol=seagreen]
Structured[vbcol=seagreen]
manually[vbcol=seagreen]
me[vbcol=seagreen]
the[vbcol=seagreen]
as[vbcol=seagreen]
having[vbcol=seagreen]
outer
>
|||"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:eivN0%23fTEHA.3476@.tk2msftngp13.phx.gbl...
> "...then you can remove the dependency on the PackageID, by blanking out the
> PackageID property of the ExecutePackageTask... This can be done as a
> one-off exercise with the Disconnected Edit feature..."
> "If you regularly migrate packages and do not want to have to worry about
> this, you can add a Dynamic Properties task to perform this during package
> run-time..."
> Is it not clear that you do this once, unless you change things?
It wasn't clear to me. Does "migrate packages" mean change them? Or move them from place
to place? Or how do I interpret that?
> --
> Darren Green
> http://www.sqldts.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> (hence the global
> *once* with the
> if
> of
> Then
> is,
> for
> in
> not
> you
> specify
> where
> variable
> Structured
> manually
> me
> the
> as
> having
> outer
>
that way OR an ExecuteProcess task with DTSRUN.
I have just followed the article and it works for me.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that has several "Execute Package Task" elements. I'd
like to make the
> sub-packages version-independent. I ran across this blurb:
> http://www.sqldts.com/default.aspx?216
> Which talks about blanking the PackageID. I've done exactly as this has
described (both
> by blanking the PackageID with "Disconnected Edit" *and* by having a blank
Global Variable
> that I set the PackageIDs to). However, when I invoke my outer package, I
keep getting an
> "invalid GUID".
> Any help would be greatly appreciated. Thanks!
> John Peterson
>
Thanks Allan!
I *think* I might be having some issues with the blank global variable being "<not
displayable>". That is, when I use the Global Variable editor to "blank" out my PackageID
GV, everything works. However, if I save my package (as Structured Storage), exit the
package, and re-run it, I consistently get the "Invalid GUID specified" error. If I edit
the PackageID GV to blank it out again, then it works.
I even added a ActiveX task to set the PackageID GV:
' Ensure that the PackageID global variable is blank.
DTSGlobalVariables("PackageID").Value = ""
But upon initial load of the package, it still fails until I manually "dink" that GV.
If you have the time and inclination, could you try that and let me know of your results?
Many regards,
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> You could of course simply use an Active Script task and call the package
> that way OR an ExecuteProcess task with DTSRUN.
> I have just followed the article and it works for me.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> like to make the
> described (both
> Global Variable
> keep getting an
>
|||What I think then is happening is that you are saying "" which is not empty.
Why do you have a GV setting the Package GUID in the first place if you do
not want to use it?
I would remove this altogether or if you want to occasionally specify the
GUID and sometimes not then I would do it in an Active Script task where i
could decide to use/or not based on a certain condition
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I *think* I might be having some issues with the blank global variable
being "<not
> displayable>". That is, when I use the Global Variable editor to "blank"
out my PackageID
> GV, everything works. However, if I save my package (as Structured
Storage), exit the
> package, and re-run it, I consistently get the "Invalid GUID specified"
error. If I edit
> the PackageID GV to blank it out again, then it works.
> I even added a ActiveX task to set the PackageID GV:
> ' Ensure that the PackageID global variable is blank.
> DTSGlobalVariables("PackageID").Value = ""
> But upon initial load of the package, it still fails until I manually
"dink" that GV.
> If you have the time and inclination, could you try that and let me know
of your results?[vbcol=seagreen]
> Many regards,
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
package[vbcol=seagreen]
I'd[vbcol=seagreen]
has[vbcol=seagreen]
blank[vbcol=seagreen]
package, I
>
|||Sorry, I should have been more clear: I *always* want to call the sub-package by name
only, so I *do* want to use the GV to set the PackageID. The problem is, it seems like
DTS is sometimes replacing the actual string of "<not displayable>" for the PackageID when
I first load the package, which will then generate the "Invalid GUID specified" error. It
seems like I need to manually clear out the GV the first time I run it in the designer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> What I think then is happening is that you are saying "" which is not empty.
> Why do you have a GV setting the Package GUID in the first place if you do
> not want to use it?
> I would remove this altogether or if you want to occasionally specify the
> GUID and sometimes not then I would do it in an Active Script task where i
> could decide to use/or not based on a certain condition
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> being "<not
> out my PackageID
> Storage), exit the
> error. If I edit
> "dink" that GV.
> of your results?
> package
> I'd
> has
> blank
> package, I
>
|||John
If you never want to load the DTS package using either the Package and
Version GUID, then I'd do as Allan has suggested and put either,
a) Blank out the GUIDs using disconnected edit. They will only come back if
you edit that task in the designer.
b) Write a short ActiveScript task to blank out the GUIDs at the start of
the package.
Here is a script I use to accomplish that,
<-- Start -->
Dim oPkg
Dim colTasks
Dim sTsk
' open DTS package object so we can get the package details
Set oPkg = DTSGlobalVariables.Parent
' open object referring to Tasks collection
Set colTasks = oPkg.Tasks
' for each item in the collection
For Each sTsk in colTasks
' check if the task is an Execute Package task
If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
' create object referring to selected task
Set oTsk = oPkg.Tasks(sTsk.Name)
' blank out Package and VersionIDs so package loads by name only
' this will ensure that only the latest version is loaded.
oTsk.Properties("PackageID").Value = ""
oTsk.Properties("VersionID").Value = ""
' release object that was created
Set oTsk = Nothing
End If
Next
Set colTasks = Nothing
Set oPkg = Nothing
<-- End -->
Thanks
Phill
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more clear: I *always* want to call the
sub-package by name
> only, so I *do* want to use the GV to set the PackageID. The problem is,
it seems like
> DTS is sometimes replacing the actual string of "<not displayable>" for
the PackageID when
> I first load the package, which will then generate the "Invalid GUID
specified" error. It
> seems like I need to manually clear out the GV the first time I run it in
the designer.[vbcol=seagreen]
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
empty.[vbcol=seagreen]
do[vbcol=seagreen]
the[vbcol=seagreen]
i[vbcol=seagreen]
"blank"[vbcol=seagreen]
specified"[vbcol=seagreen]
know[vbcol=seagreen]
elements.[vbcol=seagreen]
this[vbcol=seagreen]
a
>
|||Thanks Phil!
Ah -- I thought I might need to constantly set the PackageID to blank (hence the global
variable approach). But it sounds like I really only need to do this *once* with the
Disconnected Edit feature (unless I edit the task again, of course).
Thanks for the sample script!
John Peterson
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> John
> If you never want to load the DTS package using either the Package and
> Version GUID, then I'd do as Allan has suggested and put either,
> a) Blank out the GUIDs using disconnected edit. They will only come back if
> you edit that task in the designer.
> b) Write a short ActiveScript task to blank out the GUIDs at the start of
> the package.
> Here is a script I use to accomplish that,
> <-- Start -->
> Dim oPkg
> Dim colTasks
> Dim sTsk
> ' open DTS package object so we can get the package details
> Set oPkg = DTSGlobalVariables.Parent
> ' open object referring to Tasks collection
> Set colTasks = oPkg.Tasks
> ' for each item in the collection
> For Each sTsk in colTasks
> ' check if the task is an Execute Package task
> If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
> ' create object referring to selected task
> Set oTsk = oPkg.Tasks(sTsk.Name)
> ' blank out Package and VersionIDs so package loads by name only
> ' this will ensure that only the latest version is loaded.
> oTsk.Properties("PackageID").Value = ""
> oTsk.Properties("VersionID").Value = ""
> ' release object that was created
> Set oTsk = Nothing
> End If
> Next
> Set colTasks = Nothing
> Set oPkg = Nothing
> <-- End -->
> --
> Thanks
> Phill
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> sub-package by name
> it seems like
> the PackageID when
> specified" error. It
> the designer.
> empty.
> do
> the
> i
> "blank"
> specified"
> know
> elements.
> this
> a
>
|||"...then you can remove the dependency on the PackageID, by blanking out the
PackageID property of the ExecutePackageTask... This can be done as a
one-off exercise with the Disconnected Edit feature..."
"If you regularly migrate packages and do not want to have to worry about
this, you can add a Dynamic Properties task to perform this during package
run-time..."
Is it not clear that you do this once, unless you change things?
Darren Green
http://www.sqldts.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> Thanks Phil!
> Ah -- I thought I might need to constantly set the PackageID to blank
(hence the global
> variable approach). But it sounds like I really only need to do this
*once* with the[vbcol=seagreen]
> Disconnected Edit feature (unless I edit the task again, of course).
> Thanks for the sample script!
> John Peterson
>
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
of[vbcol=seagreen]
Then[vbcol=seagreen]
is,[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
not[vbcol=seagreen]
you[vbcol=seagreen]
specify[vbcol=seagreen]
where[vbcol=seagreen]
variable[vbcol=seagreen]
Structured[vbcol=seagreen]
manually[vbcol=seagreen]
me[vbcol=seagreen]
the[vbcol=seagreen]
as[vbcol=seagreen]
having[vbcol=seagreen]
outer
>
|||"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:eivN0%23fTEHA.3476@.tk2msftngp13.phx.gbl...
> "...then you can remove the dependency on the PackageID, by blanking out the
> PackageID property of the ExecutePackageTask... This can be done as a
> one-off exercise with the Disconnected Edit feature..."
> "If you regularly migrate packages and do not want to have to worry about
> this, you can add a Dynamic Properties task to perform this during package
> run-time..."
> Is it not clear that you do this once, unless you change things?
It wasn't clear to me. Does "migrate packages" mean change them? Or move them from place
to place? Or how do I interpret that?
> --
> Darren Green
> http://www.sqldts.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> (hence the global
> *once* with the
> if
> of
> Then
> is,
> for
> in
> not
> you
> specify
> where
> variable
> Structured
> manually
> me
> the
> as
> having
> outer
>
How to call a sub-package by name only?
You could of course simply use an Active Script task and call the package
that way OR an ExecuteProcess task with DTSRUN.
I have just followed the article and it works for me.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that has several "Execute Package Task" elements. I'd
like to make the
> sub-packages version-independent. I ran across this blurb:
> http://www.sqldts.com/default.aspx?216
> Which talks about blanking the PackageID. I've done exactly as this has
described (both
> by blanking the PackageID with "Disconnected Edit" *and* by having a blank
Global Variable
> that I set the PackageIDs to). However, when I invoke my outer package, I
keep getting an
> "invalid GUID".
> Any help would be greatly appreciated. Thanks!
> John Peterson
>Thanks Allan!
I *think* I might be having some issues with the blank global variable being
"<not
displayable>". That is, when I use the Global Variable editor to "blank" ou
t my PackageID
GV, everything works. However, if I save my package (as Structured Storage)
, exit the
package, and re-run it, I consistently get the "Invalid GUID specified" erro
r. If I edit
the PackageID GV to blank it out again, then it works.
I even added a ActiveX task to set the PackageID GV:
' Ensure that the PackageID global variable is blank.
DTSGlobalVariables("PackageID").Value = ""
But upon initial load of the package, it still fails until I manually "dink"
that GV.
If you have the time and inclination, could you try that and let me know of
your results?
Many regards,
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> You could of course simply use an Active Script task and call the package
> that way OR an ExecuteProcess task with DTSRUN.
> I have just followed the article and it works for me.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> like to make the
> described (both
> Global Variable
> keep getting an
>|||What I think then is happening is that you are saying "" which is not empty.
Why do you have a GV setting the Package GUID in the first place if you do
not want to use it?
I would remove this altogether or if you want to occasionally specify the
GUID and sometimes not then I would do it in an Active Script task where i
could decide to use/or not based on a certain condition
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I *think* I might be having some issues with the blank global variable
being "<not
> displayable>". That is, when I use the Global Variable editor to "blank"
out my PackageID
> GV, everything works. However, if I save my package (as Structured
Storage), exit the
> package, and re-run it, I consistently get the "Invalid GUID specified"
error. If I edit
> the PackageID GV to blank it out again, then it works.
> I even added a ActiveX task to set the PackageID GV:
> ' Ensure that the PackageID global variable is blank.
> DTSGlobalVariables("PackageID").Value = ""
> But upon initial load of the package, it still fails until I manually
"dink" that GV.
> If you have the time and inclination, could you try that and let me know
of your results?
> Many regards,
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
package[vbcol=seagreen]
I'd[vbcol=seagreen]
has[vbcol=seagreen]
blank[vbcol=seagreen]
package, I[vbcol=seagreen]
>|||Sorry, I should have been more clear: I *always* want to call the sub-packa
ge by name
only, so I *do* want to use the GV to set the PackageID. The problem is, it
seems like
DTS is sometimes replacing the actual string of "<not displayable>" for the
PackageID when
I first load the package, which will then generate the "Invalid GUID specifi
ed" error. It
seems like I need to manually clear out the GV the first time I run it in th
e designer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> What I think then is happening is that you are saying "" which is not empt
y.
> Why do you have a GV setting the Package GUID in the first place if you do
> not want to use it?
> I would remove this altogether or if you want to occasionally specify the
> GUID and sometimes not then I would do it in an Active Script task where i
> could decide to use/or not based on a certain condition
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> being "<not
> out my PackageID
> Storage), exit the
> error. If I edit
> "dink" that GV.
> of your results?
> package
> I'd
> has
> blank
> package, I
>|||John
If you never want to load the DTS package using either the Package and
Version GUID, then I'd do as Allan has suggested and put either,
a) Blank out the GUIDs using disconnected edit. They will only come back if
you edit that task in the designer.
b) Write a short ActiveScript task to blank out the GUIDs at the start of
the package.
Here is a script I use to accomplish that,
<-- Start -->
Dim oPkg
Dim colTasks
Dim sTsk
' open DTS package object so we can get the package details
Set oPkg = DTSGlobalVariables.Parent
' open object referring to Tasks collection
Set colTasks = oPkg.Tasks
' for each item in the collection
For Each sTsk in colTasks
' check if the task is an Execute Package task
If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
' create object referring to selected task
Set oTsk = oPkg.Tasks(sTsk.Name)
' blank out Package and VersionIDs so package loads by name only
' this will ensure that only the latest version is loaded.
oTsk.Properties("PackageID").Value = ""
oTsk.Properties("VersionID").Value = ""
' release object that was created
Set oTsk = Nothing
End If
Next
Set colTasks = Nothing
Set oPkg = Nothing
<-- End -->
Thanks
Phill
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more clear: I *always* want to call the
sub-package by name
> only, so I *do* want to use the GV to set the PackageID. The problem is,
it seems like
> DTS is sometimes replacing the actual string of "<not displayable>" for
the PackageID when
> I first load the package, which will then generate the "Invalid GUID
specified" error. It
> seems like I need to manually clear out the GV the first time I run it in
the designer.
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
empty.[vbcol=seagreen]
do[vbcol=seagreen]
the[vbcol=seagreen]
i[vbcol=seagreen]
"blank"[vbcol=seagreen]
specified"[vbcol=seagreen]
know[vbcol=seagreen]
elements.[vbcol=seagreen]
this[vbcol=seagreen]
a[vbcol=seagreen]
>|||Thanks Phil!
Ah -- I thought I might need to constantly set the PackageID to blank (hence
the global
variable approach). But it sounds like I really only need to do this *once*
with the
Disconnected Edit feature (unless I edit the task again, of course).
Thanks for the sample script!
John Peterson
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> John
> If you never want to load the DTS package using either the Package and
> Version GUID, then I'd do as Allan has suggested and put either,
> a) Blank out the GUIDs using disconnected edit. They will only come back i
f
> you edit that task in the designer.
> b) Write a short ActiveScript task to blank out the GUIDs at the start of
> the package.
> Here is a script I use to accomplish that,
> <-- Start -->
> Dim oPkg
> Dim colTasks
> Dim sTsk
> ' open DTS package object so we can get the package details
> Set oPkg = DTSGlobalVariables.Parent
> ' open object referring to Tasks collection
> Set colTasks = oPkg.Tasks
> ' for each item in the collection
> For Each sTsk in colTasks
> ' check if the task is an Execute Package task
> If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
> ' create object referring to selected task
> Set oTsk = oPkg.Tasks(sTsk.Name)
> ' blank out Package and VersionIDs so package loads by name only
> ' this will ensure that only the latest version is loaded.
> oTsk.Properties("PackageID").Value = ""
> oTsk.Properties("VersionID").Value = ""
> ' release object that was created
> Set oTsk = Nothing
> End If
> Next
> Set colTasks = Nothing
> Set oPkg = Nothing
> <-- End -->
> --
> Thanks
> Phill
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> sub-package by name
> it seems like
> the PackageID when
> specified" error. It
> the designer.
> empty.
> do
> the
> i
> "blank"
> specified"
> know
> elements.
> this
> a
>|||"...then you can remove the dependency on the PackageID, by blanking out the
PackageID property of the ExecutePackageTask... This can be done as a
one-off exercise with the Disconnected Edit feature..."
"If you regularly migrate packages and do not want to have to worry about
this, you can add a Dynamic Properties task to perform this during package
run-time..."
Is it not clear that you do this once, unless you change things?
Darren Green
http://www.sqldts.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> Thanks Phil!
> Ah -- I thought I might need to constantly set the PackageID to blank
(hence the global
> variable approach). But it sounds like I really only need to do this
*once* with the
> Disconnected Edit feature (unless I edit the task again, of course).
> Thanks for the sample script!
> John Peterson
>
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
of[vbcol=seagreen]
Then[vbcol=seagreen]
is,[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
not[vbcol=seagreen]
you[vbcol=seagreen]
specify[vbcol=seagreen]
where[vbcol=seagreen]
variable[vbcol=seagreen]
Structured[vbcol=seagreen]
manually[vbcol=seagreen]
me[vbcol=seagreen]
the[vbcol=seagreen]
as[vbcol=seagreen]
having[vbcol=seagreen]
outer[vbcol=seagreen]
>|||"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:eivN0%23fTEHA.3476@.tk2msftngp13.phx.gbl...
> "...then you can remove the dependency on the PackageID, by blanking out t
he
> PackageID property of the ExecutePackageTask... This can be done as a
> one-off exercise with the Disconnected Edit feature..."
> "If you regularly migrate packages and do not want to have to worry about
> this, you can add a Dynamic Properties task to perform this during package
> run-time..."
> Is it not clear that you do this once, unless you change things?
It wasn't clear to me. Does "migrate packages" mean change them? Or move t
hem from place
to place? Or how do I interpret that?
> --
> Darren Green
> http://www.sqldts.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> (hence the global
> *once* with the
> if
> of
> Then
> is,
> for
> in
> not
> you
> specify
> where
> variable
> Structured
> manually
> me
> the
> as
> having
> outer
>
that way OR an ExecuteProcess task with DTSRUN.
I have just followed the article and it works for me.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that has several "Execute Package Task" elements. I'd
like to make the
> sub-packages version-independent. I ran across this blurb:
> http://www.sqldts.com/default.aspx?216
> Which talks about blanking the PackageID. I've done exactly as this has
described (both
> by blanking the PackageID with "Disconnected Edit" *and* by having a blank
Global Variable
> that I set the PackageIDs to). However, when I invoke my outer package, I
keep getting an
> "invalid GUID".
> Any help would be greatly appreciated. Thanks!
> John Peterson
>Thanks Allan!
I *think* I might be having some issues with the blank global variable being
"<not
displayable>". That is, when I use the Global Variable editor to "blank" ou
t my PackageID
GV, everything works. However, if I save my package (as Structured Storage)
, exit the
package, and re-run it, I consistently get the "Invalid GUID specified" erro
r. If I edit
the PackageID GV to blank it out again, then it works.
I even added a ActiveX task to set the PackageID GV:
' Ensure that the PackageID global variable is blank.
DTSGlobalVariables("PackageID").Value = ""
But upon initial load of the package, it still fails until I manually "dink"
that GV.
If you have the time and inclination, could you try that and let me know of
your results?
Many regards,
John Peterson
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> You could of course simply use an Active Script task and call the package
> that way OR an ExecuteProcess task with DTSRUN.
> I have just followed the article and it works for me.
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ObapPeJTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> like to make the
> described (both
> Global Variable
> keep getting an
>|||What I think then is happening is that you are saying "" which is not empty.
Why do you have a GV setting the Package GUID in the first place if you do
not want to use it?
I would remove this altogether or if you want to occasionally specify the
GUID and sometimes not then I would do it in an Active Script task where i
could decide to use/or not based on a certain condition
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan!
> I *think* I might be having some issues with the blank global variable
being "<not
> displayable>". That is, when I use the Global Variable editor to "blank"
out my PackageID
> GV, everything works. However, if I save my package (as Structured
Storage), exit the
> package, and re-run it, I consistently get the "Invalid GUID specified"
error. If I edit
> the PackageID GV to blank it out again, then it works.
> I even added a ActiveX task to set the PackageID GV:
> ' Ensure that the PackageID global variable is blank.
> DTSGlobalVariables("PackageID").Value = ""
> But upon initial load of the package, it still fails until I manually
"dink" that GV.
> If you have the time and inclination, could you try that and let me know
of your results?
> Many regards,
> John Peterson
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eUuOQuJTEHA.2128@.TK2MSFTNGP11.phx.gbl...
package[vbcol=seagreen]
I'd[vbcol=seagreen]
has[vbcol=seagreen]
blank[vbcol=seagreen]
package, I[vbcol=seagreen]
>|||Sorry, I should have been more clear: I *always* want to call the sub-packa
ge by name
only, so I *do* want to use the GV to set the PackageID. The problem is, it
seems like
DTS is sometimes replacing the actual string of "<not displayable>" for the
PackageID when
I first load the package, which will then generate the "Invalid GUID specifi
ed" error. It
seems like I need to manually clear out the GV the first time I run it in th
e designer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> What I think then is happening is that you are saying "" which is not empt
y.
> Why do you have a GV setting the Package GUID in the first place if you do
> not want to use it?
> I would remove this altogether or if you want to occasionally specify the
> GUID and sometimes not then I would do it in an Active Script task where i
> could decide to use/or not based on a certain condition
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OsbAS6JTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> being "<not
> out my PackageID
> Storage), exit the
> error. If I edit
> "dink" that GV.
> of your results?
> package
> I'd
> has
> blank
> package, I
>|||John
If you never want to load the DTS package using either the Package and
Version GUID, then I'd do as Allan has suggested and put either,
a) Blank out the GUIDs using disconnected edit. They will only come back if
you edit that task in the designer.
b) Write a short ActiveScript task to blank out the GUIDs at the start of
the package.
Here is a script I use to accomplish that,
<-- Start -->
Dim oPkg
Dim colTasks
Dim sTsk
' open DTS package object so we can get the package details
Set oPkg = DTSGlobalVariables.Parent
' open object referring to Tasks collection
Set colTasks = oPkg.Tasks
' for each item in the collection
For Each sTsk in colTasks
' check if the task is an Execute Package task
If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
' create object referring to selected task
Set oTsk = oPkg.Tasks(sTsk.Name)
' blank out Package and VersionIDs so package loads by name only
' this will ensure that only the latest version is loaded.
oTsk.Properties("PackageID").Value = ""
oTsk.Properties("VersionID").Value = ""
' release object that was created
Set oTsk = Nothing
End If
Next
Set colTasks = Nothing
Set oPkg = Nothing
<-- End -->
Thanks
Phill
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Sorry, I should have been more clear: I *always* want to call the
sub-package by name
> only, so I *do* want to use the GV to set the PackageID. The problem is,
it seems like
> DTS is sometimes replacing the actual string of "<not displayable>" for
the PackageID when
> I first load the package, which will then generate the "Invalid GUID
specified" error. It
> seems like I need to manually clear out the GV the first time I run it in
the designer.
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uMVOqBTTEHA.1732@.TK2MSFTNGP09.phx.gbl...
empty.[vbcol=seagreen]
do[vbcol=seagreen]
the[vbcol=seagreen]
i[vbcol=seagreen]
"blank"[vbcol=seagreen]
specified"[vbcol=seagreen]
know[vbcol=seagreen]
elements.[vbcol=seagreen]
this[vbcol=seagreen]
a[vbcol=seagreen]
>|||Thanks Phil!
Ah -- I thought I might need to constantly set the PackageID to blank (hence
the global
variable approach). But it sounds like I really only need to do this *once*
with the
Disconnected Edit feature (unless I edit the task again, of course).
Thanks for the sample script!
John Peterson
"Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
> John
> If you never want to load the DTS package using either the Package and
> Version GUID, then I'd do as Allan has suggested and put either,
> a) Blank out the GUIDs using disconnected edit. They will only come back i
f
> you edit that task in the designer.
> b) Write a short ActiveScript task to blank out the GUIDs at the start of
> the package.
> Here is a script I use to accomplish that,
> <-- Start -->
> Dim oPkg
> Dim colTasks
> Dim sTsk
> ' open DTS package object so we can get the package details
> Set oPkg = DTSGlobalVariables.Parent
> ' open object referring to Tasks collection
> Set colTasks = oPkg.Tasks
> ' for each item in the collection
> For Each sTsk in colTasks
> ' check if the task is an Execute Package task
> If InStr(1, sTsk.Name, "DTSExecutePackageTask", vbTextCompare ) > 0 Then
> ' create object referring to selected task
> Set oTsk = oPkg.Tasks(sTsk.Name)
> ' blank out Package and VersionIDs so package loads by name only
> ' this will ensure that only the latest version is loaded.
> oTsk.Properties("PackageID").Value = ""
> oTsk.Properties("VersionID").Value = ""
> ' release object that was created
> Set oTsk = Nothing
> End If
> Next
> Set colTasks = Nothing
> Set oPkg = Nothing
> <-- End -->
> --
> Thanks
> Phill
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:urAJ4tVTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> sub-package by name
> it seems like
> the PackageID when
> specified" error. It
> the designer.
> empty.
> do
> the
> i
> "blank"
> specified"
> know
> elements.
> this
> a
>|||"...then you can remove the dependency on the PackageID, by blanking out the
PackageID property of the ExecutePackageTask... This can be done as a
one-off exercise with the Disconnected Edit feature..."
"If you regularly migrate packages and do not want to have to worry about
this, you can add a Dynamic Properties task to perform this during package
run-time..."
Is it not clear that you do this once, unless you change things?
Darren Green
http://www.sqldts.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> Thanks Phil!
> Ah -- I thought I might need to constantly set the PackageID to blank
(hence the global
> variable approach). But it sounds like I really only need to do this
*once* with the
> Disconnected Edit feature (unless I edit the task again, of course).
> Thanks for the sample script!
> John Peterson
>
> "Phill Carter" <pcarter@.no-spam.bellpotter.com.au> wrote in message
> news:edtCtxaTEHA.204@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
of[vbcol=seagreen]
Then[vbcol=seagreen]
is,[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
not[vbcol=seagreen]
you[vbcol=seagreen]
specify[vbcol=seagreen]
where[vbcol=seagreen]
variable[vbcol=seagreen]
Structured[vbcol=seagreen]
manually[vbcol=seagreen]
me[vbcol=seagreen]
the[vbcol=seagreen]
as[vbcol=seagreen]
having[vbcol=seagreen]
outer[vbcol=seagreen]
>|||"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:eivN0%23fTEHA.3476@.tk2msftngp13.phx.gbl...
> "...then you can remove the dependency on the PackageID, by blanking out t
he
> PackageID property of the ExecutePackageTask... This can be done as a
> one-off exercise with the Disconnected Edit feature..."
> "If you regularly migrate packages and do not want to have to worry about
> this, you can add a Dynamic Properties task to perform this during package
> run-time..."
> Is it not clear that you do this once, unless you change things?
It wasn't clear to me. Does "migrate packages" mean change them? Or move t
hem from place
to place? Or how do I interpret that?
> --
> Darren Green
> http://www.sqldts.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e9rLYwdTEHA.1172@.TK2MSFTNGP11.phx.gbl...
> (hence the global
> *once* with the
> if
> of
> Then
> is,
> for
> in
> not
> you
> specify
> where
> variable
> Structured
> manually
> me
> the
> as
> having
> outer
>
Wednesday, March 7, 2012
How to Call a Script from T-SQL
Hello all,
I have a SQL script file with some statements in it. ... say c:\sai.sql. I want to call it through a T-SQL Procedure...Please let me know the process
SaiIf you DON'T need the script to run under the same SPID as your procedure you could always go the xp_cmdshell & i/osql route.
If you need to run under the same SPID, then you could load your script into a one row by one column table, select the text into a local variable and EXECUTE the variable.
I have a SQL script file with some statements in it. ... say c:\sai.sql. I want to call it through a T-SQL Procedure...Please let me know the process
SaiIf you DON'T need the script to run under the same SPID as your procedure you could always go the xp_cmdshell & i/osql route.
If you need to run under the same SPID, then you could load your script into a one row by one column table, select the text into a local variable and EXECUTE the variable.
How to call a dot net assembly from ssis
How to call a dot net assembly from ssis.
I have used a script component and went in the design script editor by clicking the "Design Script" button.From their using a object browser trying to load a dll file in references to be used in the ssis application.
But it gives error " Cannot browse the file"
Can any body help me in the same.
http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx
-Jamie
Sunday, February 19, 2012
How to build a script to extract 5% of the production Database dat
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
Try SELECT TOP 5 PERCENT instead.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
Try SELECT TOP 5 PERCENT instead.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
How to build a script to extract 5% of the production Database dat
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Productio
n
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Productio
n
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
How to build a script to extract 5% of the production Database dat
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian
Subscribe to:
Posts (Atom)