Script Transformation

A new question !

When you use a script transformation you have input rows and output rows.

Is it possible to have a different number of rows at input and output ?

Thanks

i found something about it

http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsref9/html/1c3e92c7-a4fa-4fdd-b9ca-ac3069536274.asp|||That's the idea. An asynch component, you need to explicitly add rows to the output buffer, compared to a synchronous transform where you just redirect the input row to the output one for one.|||Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.|||

Coroebus wrote:

Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.

Coroebus,

This is what you want: http://www.sqldts.com/default.aspx?307

-Jamie|||Thanks a lot one more time !|||Please note that Books Online does include a simple sample of each of the 4 types of Script components - source, destination, transformation with synchronous and with asynchronous outputs -- in the section titled "Examples of Specific Types of Script Components ."

-Doug
|||I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

|||

Coroebus wrote:

I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

Why are you calling Row.NextRow()? I stand to be corrected but if this is in the Input0_ProcessInputRow() method then you don't need to do this because that method gets called for every input row.

-Jamie

Script to identify line count in an procedure

Hi Guys,
Do any one of you have any scripts which return the procedure name and
number of lines for all procedures in a database.
Say I have a server with 1000 stored procedure,I need a script which return:
Procedure name Number of lines
-- --
Thanks in advance
HariI think this should do it:
SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
FROM syscomments AS C
JOIN sysobjects AS O
ON C.id = O.id
WHERE O.xtype='P'
GROUP BY O.id, O.name
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot.
Regards
Hari
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:iMidnTtJieiJzlqiRVn-gQ@.giganews.com...
> I think this should do it:
> SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
> FROM syscomments AS C
> JOIN sysobjects AS O
> ON C.id = O.id
> WHERE O.xtype='P'
> GROUP BY O.id, O.name
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

Script to find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >

Script to find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script that Return the number of Rows for each Table on a DB

I Have 2 separate data bases with the same Tables and records, I create
a batch to synchronize my data every 2 hours but now I need a Script
that Return the number of Rows for each Table on each DB.
Can someone give me an Idea or the solution for this, I will really
appreciate it.This is what the code I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||This is what I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN
('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||Run this query against each database:
SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name, dbo.sysindexes.rowcnt
FROM dbo.sysobjects INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.status = 0)
ORDER BY dbo.sysobjects.name
"imagabo" wrote:

> I Have 2 separate data bases with the same Tables and records, I create
> a batch to synchronize my data every 2 hours but now I need a Script
> that Return the number of Rows for each Table on each DB.
> Can someone give me an Idea or the solution for this, I will really
> appreciate it.
>|||This is it, Thank you so much bschaettle, I own you one.

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollinger
If you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollingerIf you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

Script line number

When you get a server error message referring to a line number in a
script, is there an easy way to jump to the correct line? It's not
easy to count down in a big script or when there is a lot of white
space.

In WordPerfect we had a linenum macro that allowed you to enter the
line number and it would jump to the line using the same counting
method as the interpreter that generated the error message. Is there
something similar with TSQL, or another way?

Kerry"Kerry" <kerrytforums@.hotmail.com> wrote:
> When you get a server error message referring to a line number in a
> script, is there an easy way to jump to the correct line? It's not
> easy to count down in a big script or when there is a lot of white
> space.
> In WordPerfect we had a linenum macro that allowed you to enter the
> line number and it would jump to the line using the same counting
> method as the interpreter that generated the error message. Is there
> something similar with TSQL, or another way?
> Kerry

Kerry,

That will depend on your editor... most editors (including Query Analyzer
with SQL Server 2000, Notepad in WinXP, and Visual Studio) have a function
for jumping to a specific line number. In most editors I've worked with on
Windows, it's usually ctrl-G

Craig|||In Query Analyzer, you can double-click on the error message in the
result pane to jump to the source line in question.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Kerry" <kerrytforums@.hotmail.com> wrote in message
news:6e61c2be.0311140759.25063405@.posting.google.c om...
> When you get a server error message referring to a line number in a
> script, is there an easy way to jump to the correct line? It's not
> easy to count down in a big script or when there is a lot of white
> space.
> In WordPerfect we had a linenum macro that allowed you to enter the
> line number and it would jump to the line using the same counting
> method as the interpreter that generated the error message. Is there
> something similar with TSQL, or another way?
> Kerry

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:D
I hope you are not thinking about reversing the SSN in order to protect the
data? Any third grader could figure that out and would know all the SSNs.
Your best bet would be to replace them with actual random IDs. You might
try this approach...
Assuming you have SSN stored in one table, lets call it CUSTOMERS...
create a new table SSN_MASK. Insert into this table every unique SSN, along
with an identity column to give them a unique value. Then use the values in
this table to update SSN in the real table. Lastly, drop the SSN_MAsk table
(or at least truncate it) so no one can match the bogus SSNs to the real
thing.
create table SSN_MASK
(
NEW_SSN integer identity primary key not null,
OLD_SSN varchar(9) -- not sure how you defined it
);
insert into SSN_MASK (OLD_SSN) (select unique SSN from CUSTOMERS);
update CUSTOMERS
set SSN = (select
right('000000000' + cast(a.NEW_SSN as varchar(9)),9)
-- make sure the new SSN is 9 characters
from SSN_MASK as a
where a.OLD_SSN = CUSTOMERS.SSN);
drop table SSN_MASK;
<dmalhotr2001@.yahoo.com> wrote in message
news:1158351116.321489.122450@.i3g2000cwc.googlegro ups.com...
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:D
Hi
If you wish to make the data anonymous then you will need an algorithm that
is not easily breakable. Normal encryption procedures would not necessarily
give you the format you want, but just moving numbers or replacement may not
give you the require level of obfuscation.
To change multiple values of a given column you can add an extra column say
hasbeenchange which you set then the data has been changed. You can then
cursor through the records in the table and change each value that does not
already have hasbeenchange set
DECLARE @.ssn char(9)
DECLARE ssn_cursor CURSOR FOR
SELECT ssn
FROM Mytable WHERE hasbeenchange = 0
ORDER BY SSN
OPEN ssn_cursor
FETCH NEXT FROM ssn_cursor INTO @.ssn
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE mytable
SET ssn = fn_jiggerypokery(ssn)
WHERE ssn = @.ssn
AND hasbeenchange = 0
FETCH NEXT FROM ssn_cursor INTO @.ssn
END
CLOSE ssn_cursor
DEALLOCATE ssn_cursor
John
"dmalhotr2001@.yahoo.com" wrote:

> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>
|||dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
For what purpose do you want to do this? One reason might be that you
want to generate some test data without compromising any confidential
information. In that case you could use the CHECKSUM or RAND functions
to generate some arbitrary values.
Another reason might be that you want to secure the data. "Scrambling"
a number has nothing to do with any meaningful kind of security so here
are some other suggestions instead. You can encrypt the data and/or to
limit access to column/rows using the SQL Server security model.
Probably you'll want to use a combination of both.
SQL Server 2005 has encryption functions built-in, so take a look at
the encryption topics in Books Online. In earlier versions you can use
third party software to achieve the same thing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||John,
Can I get a copy of dbo.fn_jiggerypokery()?
With a name like that, it 'sounds' interesting...
;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E64B9234-AACC-4453-998E-21ACCFA81699@.microsoft.com...[vbcol=seagreen]
> Hi
> If you wish to make the data anonymous then you will need an algorithm
> that
> is not easily breakable. Normal encryption procedures would not
> necessarily
> give you the format you want, but just moving numbers or replacement may
> not
> give you the require level of obfuscation.
> To change multiple values of a given column you can add an extra column
> say
> hasbeenchange which you set then the data has been changed. You can then
> cursor through the records in the table and change each value that does
> not
> already have hasbeenchange set
> DECLARE @.ssn char(9)
> DECLARE ssn_cursor CURSOR FOR
> SELECT ssn
> FROM Mytable WHERE hasbeenchange = 0
> ORDER BY SSN
> OPEN ssn_cursor
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE mytable
> SET ssn = fn_jiggerypokery(ssn)
> WHERE ssn = @.ssn
> AND hasbeenchange = 0
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> END
> CLOSE ssn_cursor
> DEALLOCATE ssn_cursor
> John
> "dmalhotr2001@.yahoo.com" wrote:

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:DHi
If you wish to make the data anonymous then you will need an algorithm that
is not easily breakable. Normal encryption procedures would not necessarily
give you the format you want, but just moving numbers or replacement may not
give you the require level of obfuscation.
To change multiple values of a given column you can add an extra column say
hasbeenchange which you set then the data has been changed. You can then
cursor through the records in the table and change each value that does not
already have hasbeenchange set
DECLARE @.ssn char(9)
DECLARE ssn_cursor CURSOR FOR
SELECT ssn
FROM Mytable WHERE hasbeenchange = 0
ORDER BY SSN
OPEN ssn_cursor
FETCH NEXT FROM ssn_cursor INTO @.ssn
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE mytable
SET ssn = fn_jiggerypokery(ssn)
WHERE ssn = @.ssn
AND hasbeenchange = 0
FETCH NEXT FROM ssn_cursor INTO @.ssn
END
CLOSE ssn_cursor
DEALLOCATE ssn_cursor
John
"dmalhotr2001@.yahoo.com" wrote:

> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>|||dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
For what purpose do you want to do this? One reason might be that you
want to generate some test data without compromising any confidential
information. In that case you could use the CHECKSUM or RAND functions
to generate some arbitrary values.
Another reason might be that you want to secure the data. "Scrambling"
a number has nothing to do with any meaningful kind of security so here
are some other suggestions instead. You can encrypt the data and/or to
limit access to column/rows using the SQL Server security model.
Probably you'll want to use a combination of both.
SQL Server 2005 has encryption functions built-in, so take a look at
the encryption topics in Books Online. In earlier versions you can use
third party software to achieve the same thing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John,
Can I get a copy of dbo.fn_jiggerypokery()?
With a name like that, it 'sounds' interesting...
;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E64B9234-AACC-4453-998E-21ACCFA81699@.microsoft.com...[vbcol=seagreen]
> Hi
> If you wish to make the data anonymous then you will need an algorithm
> that
> is not easily breakable. Normal encryption procedures would not
> necessarily
> give you the format you want, but just moving numbers or replacement may
> not
> give you the require level of obfuscation.
> To change multiple values of a given column you can add an extra column
> say
> hasbeenchange which you set then the data has been changed. You can then
> cursor through the records in the table and change each value that does
> not
> already have hasbeenchange set
> DECLARE @.ssn char(9)
> DECLARE ssn_cursor CURSOR FOR
> SELECT ssn
> FROM Mytable WHERE hasbeenchange = 0
> ORDER BY SSN
> OPEN ssn_cursor
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE mytable
> SET ssn = fn_jiggerypokery(ssn)
> WHERE ssn = @.ssn
> AND hasbeenchange = 0
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> END
> CLOSE ssn_cursor
> DEALLOCATE ssn_cursor
> John
> "dmalhotr2001@.yahoo.com" wrote:
>

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:DI hope you are not thinking about reversing the SSN in order to protect the
data? Any third grader could figure that out and would know all the SSNs.
Your best bet would be to replace them with actual random IDs. You might
try this approach...
Assuming you have SSN stored in one table, lets call it CUSTOMERS...
create a new table SSN_MASK. Insert into this table every unique SSN, along
with an identity column to give them a unique value. Then use the values in
this table to update SSN in the real table. Lastly, drop the SSN_MAsk table
(or at least truncate it) so no one can match the bogus SSNs to the real
thing.
create table SSN_MASK
(
NEW_SSN integer identity primary key not null,
OLD_SSN varchar(9) -- not sure how you defined it
);
insert into SSN_MASK (OLD_SSN) (select unique SSN from CUSTOMERS);
update CUSTOMERS
set SSN = (select
right('000000000' + cast(a.NEW_SSN as varchar(9)),9)
-- make sure the new SSN is 9 characters
from SSN_MASK as a
where a.OLD_SSN = CUSTOMERS.SSN);
drop table SSN_MASK;
<dmalhotr2001@.yahoo.com> wrote in message
news:1158351116.321489.122450@.i3g2000cwc.googlegroups.com...
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:DI hope you are not thinking about reversing the SSN in order to protect the
data? Any third grader could figure that out and would know all the SSNs.
Your best bet would be to replace them with actual random IDs. You might
try this approach...
Assuming you have SSN stored in one table, lets call it CUSTOMERS...
create a new table SSN_MASK. Insert into this table every unique SSN, along
with an identity column to give them a unique value. Then use the values in
this table to update SSN in the real table. Lastly, drop the SSN_MAsk table
(or at least truncate it) so no one can match the bogus SSNs to the real
thing.
create table SSN_MASK
(
NEW_SSN integer identity primary key not null,
OLD_SSN varchar(9) -- not sure how you defined it
);
insert into SSN_MASK (OLD_SSN) (select unique SSN from CUSTOMERS);
update CUSTOMERS
set SSN = (select
right('000000000' + cast(a.NEW_SSN as varchar(9)),9)
-- make sure the new SSN is 9 characters
from SSN_MASK as a
where a.OLD_SSN = CUSTOMERS.SSN);
drop table SSN_MASK;
<dmalhotr2001@.yahoo.com> wrote in message
news:1158351116.321489.122450@.i3g2000cwc.googlegroups.com...
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>|||>> create table SSN_MASK
(EW_SSN integer identity primary key not null,
OLD_SSN varchar(9)); -- not sure how you defined it <<
SSN is always CHAR(9). Simply numbering it with a proprietary feature
is not that good; you have destroyed the data type. Here is one we
used in procedural code with arrays.
CREATE TABLE SSN_masks
(shift_id INTEGER NOT NULL PRIMARY KEY -- cols 8 & 9
CHECK (shift_id BETWEEN 00 AND 99),
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER NOT NULL,
col6 INTEGER NOT NULL,
col7 INTEGER NOT NULL);
You take the last two digits of the SSN (fastest changing values) and
look up a vector that tells you how to shift the remaining seven
digits.
shift(x) = ABS((x + col_n) % 11-10)
Since you have 100 different masks, the data gets scrambled pretty good
and it destroys the area digits which would otherwise repeat and give
information about the population geographic distribution. This is also
reversible because we preserve the shift identifier in the output;
shift all of them and it is a bitch to unscramble without the shift
array. Then you change the array when you get the next sample.

scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:DHi
If you wish to make the data anonymous then you will need an algorithm that
is not easily breakable. Normal encryption procedures would not necessarily
give you the format you want, but just moving numbers or replacement may not
give you the require level of obfuscation.
To change multiple values of a given column you can add an extra column say
hasbeenchange which you set then the data has been changed. You can then
cursor through the records in the table and change each value that does not
already have hasbeenchange set
DECLARE @.ssn char(9)
DECLARE ssn_cursor CURSOR FOR
SELECT ssn
FROM Mytable WHERE hasbeenchange = 0
ORDER BY SSN
OPEN ssn_cursor
FETCH NEXT FROM ssn_cursor INTO @.ssn
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE mytable
SET ssn = fn_jiggerypokery(ssn)
WHERE ssn = @.ssn
AND hasbeenchange = 0
FETCH NEXT FROM ssn_cursor INTO @.ssn
END
CLOSE ssn_cursor
DEALLOCATE ssn_cursor
John
"dmalhotr2001@.yahoo.com" wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>|||dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
For what purpose do you want to do this? One reason might be that you
want to generate some test data without compromising any confidential
information. In that case you could use the CHECKSUM or RAND functions
to generate some arbitrary values.
Another reason might be that you want to secure the data. "Scrambling"
a number has nothing to do with any meaningful kind of security so here
are some other suggestions instead. You can encrypt the data and/or to
limit access to column/rows using the SQL Server security model.
Probably you'll want to use a combination of both.
SQL Server 2005 has encryption functions built-in, so take a look at
the encryption topics in Books Online. In earlier versions you can use
third party software to achieve the same thing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John,
Can I get a copy of dbo.fn_jiggerypokery()?
With a name like that, it 'sounds' interesting...
;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E64B9234-AACC-4453-998E-21ACCFA81699@.microsoft.com...
> Hi
> If you wish to make the data anonymous then you will need an algorithm
> that
> is not easily breakable. Normal encryption procedures would not
> necessarily
> give you the format you want, but just moving numbers or replacement may
> not
> give you the require level of obfuscation.
> To change multiple values of a given column you can add an extra column
> say
> hasbeenchange which you set then the data has been changed. You can then
> cursor through the records in the table and change each value that does
> not
> already have hasbeenchange set
> DECLARE @.ssn char(9)
> DECLARE ssn_cursor CURSOR FOR
> SELECT ssn
> FROM Mytable WHERE hasbeenchange = 0
> ORDER BY SSN
> OPEN ssn_cursor
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE mytable
> SET ssn = fn_jiggerypokery(ssn)
> WHERE ssn = @.ssn
> AND hasbeenchange = 0
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> END
> CLOSE ssn_cursor
> DEALLOCATE ssn_cursor
> John
> "dmalhotr2001@.yahoo.com" wrote:
>> Hi,
>> I'm trying to scramble the ssn#s within our database. I would need a 9
>> digit number to be converted into another 9 digit number in our dev
>> database.
>> Example #1:
>> ssn: 123456789 converts to 987654321
>>
>> Also there is a catch, there is a possibility that there could be
>> duplicate ssn within a table due to bad data. I was the 2 records with
>> the same actual ssn# to be converted into the same scrambled ssn# using
>> sql server (so that the scrambled ssn#s match) for this issue.
>> Is there a way to do this?
>> Thanks
>> :D
>>