How can I reset a database surrogate PK using SSIS?

I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.

How can I do that using SSIS package?

thanks,

Aref

A quick Google revelas that you can use DBCC CHECKIDENT to do this

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

You can issue the command using the Execute SQL Task.

-Jamie

How can I process each row in result set to access properties on another package o

Take a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>Thanks, Dan -- but I can't seem to get my head around your suggestion. Basi
cally, what I
want is to be able to specify a Source Query that would return a bunch of ro
ws. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a
"Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specif
y
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> want to invoke
> I want to set
> the best
> even though I
> "Source" record
> execute that
> User").
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrot
e:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of r
ows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
DTS[vbcol=seagreen]
specify[vbcol=seagreen]
that I[vbcol=seagreen]
row,[vbcol=seagreen]
would be[vbcol=seagreen]
Task",[vbcol=seagreen]
each[vbcol=seagreen]
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of exist
ing DTS
objects, rather than having to write a lot of code. Alas, it seems like it'
s not quite
the case, even though DTS seems uniquely qualified to do this type of thing
(almost).
Since it can use a Connection to issue a query on that remote server and pro
cess the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Que
ry Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX t
ransformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> Sorry, but I don't understand what you mean by <withOUT doing anything to
a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataS
ou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> DTS
> specify
> that I
> row,
> would be
> Task",
> each
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -
- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you c
an
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
_2_1/202-5145180-8774263" target="_blank">http://www.amazon.co.uk/exec/obidos...5145180-8774263
Regards,
Paul Ibisonsql

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanks
Hi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>
|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml we
b
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
>

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanks
Hi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>
|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml we
b
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
>

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
> > Hello there
> >
> > I want to know if there is a way I can make a DTS package log to an xml web
> > service and query a functions for data that i can insert into a table ?
> >
> > thanks
> >
> >
> >

How can I install SSIS on client side?

Hi,

We just moved from SQL 2000 to SQL 2005 Ent. To use SSIS service, I need install this package on client side. But when I install the 2005 on my PC(Win XP Prof), I did not find any place I can include it. Anyone has similar experience or know how to do it?

Thanks in advance,

Jie

Hi Jwei,

Take That CD 1 and choose Workstation components, Books Online and development tools.

That'll install the following components:

.BOL

.Native client

.BIDS

.SQLXML4

.MSXML6

.OWCL11 (Office Windows Components)

.SQL Server Backward-Compatibility Files

HTH

|||

Stop! Make sure you understand the license changes for SSIS compared to DTS. Put simply you can no longer redistribute SSIS as a client component, like you could with DTS. SSIS is a server component, so machines must be licensed with server license, not just a CAL.

|||you're right...|||

Thanks a lot for taking time to respond to my post.

I had problem with the license change, just could not believe it.

"no longer redistribute SSIS as a client component, like you could with DTS" is a key to me.

|||

Then, how can I install only SSIS Client on my work station?

Please help

Thanks

|||Run setup. Choose the Integration Services and Workstation components options.|||

TDRao wrote:

Then, how can I install only SSIS Client on my work station?

SSIS client components are installed when you install Workstation components - that gives you BIDS (with ability to design and debug SSIS projects and packages) and SSMS (with ability to connect to SSIS service and monitor package execution).

To run packages in production you need SSIS server components, which are installed when you install Integration Services. That requires server license.

|||

Thanks a lot for the solution

How can I install SSIS on client side?

Hi,

We just moved from SQL 2000 to SQL 2005 Ent. To use SSIS service, I need install this package on client side. But when I install the 2005 on my PC(Win XP Prof), I did not find any place I can include it. Anyone has similar experience or know how to do it?

Thanks in advance,

Jie

Hi Jwei,

Take That CD 1 and choose Workstation components, Books Online and development tools.

That'll install the following components:

.BOL

.Native client

.BIDS

.SQLXML4

.MSXML6

.OWCL11 (Office Windows Components)

.SQL Server Backward-Compatibility Files

HTH

|||

Stop! Make sure you understand the license changes for SSIS compared to DTS. Put simply you can no longer redistribute SSIS as a client component, like you could with DTS. SSIS is a server component, so machines must be licensed with server license, not just a CAL.

|||you're right...|||

Thanks a lot for taking time to respond to my post.

I had problem with the license change, just could not believe it.

"no longer redistribute SSIS as a client component, like you could with DTS" is a key to me.

|||

Then, how can I install only SSIS Client on my work station?

Please help

Thanks

|||Run setup. Choose the Integration Services and Workstation components options.|||

TDRao wrote:

Then, how can I install only SSIS Client on my work station?

SSIS client components are installed when you install Workstation components - that gives you BIDS (with ability to design and debug SSIS projects and packages) and SSMS (with ability to connect to SSIS service and monitor package execution).

To run packages in production you need SSIS server components, which are installed when you install Integration Services. That requires server license.

|||

Thanks a lot for the solution

how can I have this package execute in loop....I want to change every lines that are not c

declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')Without a problem description, including table DDL and sample data, a
meaningful response is not likely.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:eZTmg.15162$XT2.164998@.wagner.videotron.net...
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>

How can I get the package name within a script?

Greetings,

I need to programmatically grab the name of the executing package so that it can be used in logging. Does someone have a code sample for this?

Thanks!

Use the system variable, PackageName.|||

Thanks.

For those who may benefit from a code sample:

Dim source AsString

Dim vars As Variables

Dts.VariableDispenser.LockOneForRead("System:ackageName", vars)

source = vars("PackageName").Value.ToString()

|||Also the other options (perhaps not for this example) are to use a derived column to add the system variable to the data flow, or in the control flow, using an Execute SQL task, you can map the variable to a parameter in the SQL statement.

How can I FTP 3 files

Hello,

I am new to SSIS.

I am trying to write a simple package that saves data into some tables from elsewhere in the database, extracts it into flat files and then FTPs them to another file system.

I have a control flow with three tasks. [1] Execute SQL (Executes a stored proc to fill the tables with data) [2] A data flow to extract the data into 3 flat files [3] An FTP task to move the data to the other system.

In my data flow I have three parallel streams each with an OLE-DB source and a Flat file destination so that three flat s files get produced in parallel.

The problem I have is that I cant get the FTP task to handle the files. I can get the flat files produced fine and I write them to the c: drive. But how do I program the FTP transform to pick up the three files and dispatch them? I am guessing that I need to edit the FTP task, choose the file transfer tab and edit the local parameters section to tell it to pick up the three files........but how?

As mentioned I am new to SSIS so simple+clear answers appreciated.

Thanks in advance

MGale1

This is a completely reasonable request. What I would recommend is to put each file you create in a ForEach Loop Container. Each time you loop to create a new file, inside the loop you can use the FTP Task to send the one file. You can dynamically set the file you wish to send by setting an expression on the Connection Manager that holds the file's name. I know I didn't specify exactly how to each step but you can see a more in depth about how to use SSIS expressions to set the package to be dynamic here: http://www.jumpstarttv.com/Media.aspx?vid=34.

I know you mentioned that you wanted to create the file inside the same data flow. Unfortunately, the files will have to be FTP individually to the destination. If you were to receive files from a FTP site, the task accepts wild cards (give me all the *.txt files from the ftp server). When you send files though, it's a different story. SSIS is going to force you to use a File Connection Manager to specify which file you wish to send. You cannot send multiple files at one time other than doing it in a loop as I mentioned above. At least, I'm not sure of a way to do it :).

For a slew of SSIS how-to starter videos, you can see this link: http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd

|||From what you've written, it sounds like you could use three FTP tasks, one for each file.