Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

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

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

Conversion from float to varchar

--SCRIPT :
CREATE TABLE [t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [t1] VALUES(1.0 , null )
INSERT INTO [t1] VALUES(3.1099999999999999 , null )
INSERT INTO [t1] VALUES(2.1000000000000001 , null )
What is required that copying data from column [id] to column [charid] with
all trailing decimal values.
-KhurramCREATE TABLE [#t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [#t1] VALUES(1.0 , null )
INSERT INTO [#t1] VALUES(3.1099999999999999 , null )
INSERT INTO [#t1] VALUES(2.1000000000000001 , null )
UPDATE #t1
SET charid =FLOOR(id)
Select * from #t1
HTH, Jens Suessmeyer.|||Look up the STR function in Books Online.
http://msdn.microsoft.com/library/d.../>
us_412q.asp
Plus some reading on data modeling might prove to be of great help.
MLsqlsql

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
Philip
Philip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is exe
cuted?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is e
xecuted?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controling Names of Agents

Hello there
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>

Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>

Control flow in scripts and 'GO'

(Same problem as yesterday).
Using controlflow in scripts and using 'GO' in a script
conflicts.
I use the control flow to check if the scripts are run
in the correct order and have not run before.
Some scripts 'create' views so a 'GO' is needed
in those scripts. This conflicts with the controlflow.
Any solutions to this 'problem' ?
ben brugmanControl of flow handling is confined inside a batch, we can't change this. So the 'solution' depends
on what you want to do with the control of flow handling.
If you are after exiting the script on error, you can do a raiserror with state 127 if you execute
the script using OSQL (this doesn't work with QA).
Or do a raiserror with a severe severity level. Check out the scripts for pubs and northwind for how
this can be done.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben brugman" <ben@.niethier.nl> wrote in message news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> (Same problem as yesterday).
> Using controlflow in scripts and using 'GO' in a script
> conflicts.
> I use the control flow to check if the scripts are run
> in the correct order and have not run before.
> Some scripts 'create' views so a 'GO' is needed
> in those scripts. This conflicts with the controlflow.
> Any solutions to this 'problem' ?
> ben brugman
>|||Thanks Tibor,
We are using the QA, using the raiserror with a high (above 19) severity
error, I think
is inappropriate and might lead to confusion.
So we have to work around this 'problem'.
> Check out the scripts for pubs and northwind for how
> this can be done.
What scripts are you refering to ? Where ?
Thanks for your time,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> Control of flow handling is confined inside a batch, we can't change this.
So the 'solution' depends
> on what you want to do with the control of flow handling.
> If you are after exiting the script on error, you can do a raiserror with
state 127 if you execute
> the script using OSQL (this doesn't work with QA).
> Or do a raiserror with a severe severity level. Check out the scripts for
pubs and northwind for how
> this can be done.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > (Same problem as yesterday).
> >
> > Using controlflow in scripts and using 'GO' in a script
> > conflicts.
> >
> > I use the control flow to check if the scripts are run
> > in the correct order and have not run before.
> > Some scripts 'create' views so a 'GO' is needed
> > in those scripts. This conflicts with the controlflow.
> >
> > Any solutions to this 'problem' ?
> >
> > ben brugman
> >
> >
>|||> What scripts are you refering to ? Where ?
The scripts I'm referring to are named instpubs.sql and instmsdb.sql, I believe. They should be
found in the SQL Server install directory and on the SQL Server install CD. You use these scripts to
re-create the database. However, I believe that they use the high severity level technique.
From where are you executing the scripts? If you use OSQL or ISQL, I suggest you check out the state
127 option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben brugman" <ben@.niethier.nl> wrote in message news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...
> Thanks Tibor,
> We are using the QA, using the raiserror with a high (above 19) severity
> error, I think
> is inappropriate and might lead to confusion.
> So we have to work around this 'problem'.
> > Check out the scripts for pubs and northwind for how
> > this can be done.
> What scripts are you refering to ? Where ?
> Thanks for your time,
> ben brugman
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > Control of flow handling is confined inside a batch, we can't change this.
> So the 'solution' depends
> > on what you want to do with the control of flow handling.
> > If you are after exiting the script on error, you can do a raiserror with
> state 127 if you execute
> > the script using OSQL (this doesn't work with QA).
> > Or do a raiserror with a severe severity level. Check out the scripts for
> pubs and northwind for how
> > this can be done.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > > (Same problem as yesterday).
> > >
> > > Using controlflow in scripts and using 'GO' in a script
> > > conflicts.
> > >
> > > I use the control flow to check if the scripts are run
> > > in the correct order and have not run before.
> > > Some scripts 'create' views so a 'GO' is needed
> > > in those scripts. This conflicts with the controlflow.
> > >
> > > Any solutions to this 'problem' ?
> > >
> > > ben brugman
> > >
> > >
> >
> >
>|||>
> From where are you executing the scripts? If you use OSQL or ISQL, I
suggest you check out the state
> 127 option.
We are using the QA for executing the scripts.
ben brugman
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:eJv0r1j6DHA.3804@.tk2msftngp13.phx.gbl...
> > Thanks Tibor,
> >
> > We are using the QA, using the raiserror with a high (above 19) severity
> > error, I think
> > is inappropriate and might lead to confusion.
> > So we have to work around this 'problem'.
> >
> > > Check out the scripts for pubs and northwind for how
> > > this can be done.
> > What scripts are you refering to ? Where ?
> >
> > Thanks for your time,
> > ben brugman
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:u6PSJSj6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > Control of flow handling is confined inside a batch, we can't change
this.
> > So the 'solution' depends
> > > on what you want to do with the control of flow handling.
> > > If you are after exiting the script on error, you can do a raiserror
with
> > state 127 if you execute
> > > the script using OSQL (this doesn't work with QA).
> > > Or do a raiserror with a severe severity level. Check out the scripts
for
> > pubs and northwind for how
> > > this can be done.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:Oy3jy5i6DHA.1556@.tk2msftngp13.phx.gbl...
> > > > (Same problem as yesterday).
> > > >
> > > > Using controlflow in scripts and using 'GO' in a script
> > > > conflicts.
> > > >
> > > > I use the control flow to check if the scripts are run
> > > > in the correct order and have not run before.
> > > > Some scripts 'create' views so a 'GO' is needed
> > > > in those scripts. This conflicts with the controlflow.
> > > >
> > > > Any solutions to this 'problem' ?
> > > >
> > > > ben brugman
> > > >
> > > >
> > >
> > >
> >
> >
>

Consuming a Recordset in a Script Transformation

I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.

Now I want to write that recordset to a flat file.

There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.

Anyone have any ideas on how to do this in a script transformation?

There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

|||

DarrenSQLIS wrote:

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

I echo what Darren has said. And if you need proof, go here:

Comparing performance of a raw file against a recordset destination
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx

-Jamie

constraints and altering tables

I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary> This works for the most part, but I have found that there are constraints
> being created on some columns that did not exist before the conversion.
> For
> example, I have a table which has a primary key on it's identity columns
> defined to automatically insert a new value at each insert incremented by
> 1.
> After the conversion, there is an additional constraint placed on this
> table
> which prevents a value of NULL from being added, which should be a problem
> due to the IDENTITY column, yet attempting to do an insert on this table
> generates an error saying that a NULL value cannot be inserted. I'm not
> manually inserting anything, this should just bump the id value by one and
> do the insert, but this new constraint prevents this, leaving me with a
> table I can no longer insert into.
Is the IDENTITY property still set for the column? If so, then what does
your INSERT statement look like? If not, then your script is, well,
imperfect.|||Yes, It is still set. the insert statement does inserts using enumerated
column names.
INSERT INTO RptTable (
ShortName, FullName, Description,
StockReport, ReportFilename,
AutoprintReport, PrintDaily, PrintWly, PrintMonthly,
ExportReport, ExportDaily, ExportWly, ExportMonthly, ExportFormat,
DisplayOrder, MinDateVariable, MaxDateVariable, Visible,
ReportCategoryId, HelpFilename, CLCompliant,
Param1_Label, Param1_Description)
VALUES
(@.InstallReport_ShortName, @.InstallReport_FullName,
@.InstallReport_Description,
0, @.InstallReport_ReportFilename,
0, 0, 0, 0,
0, 0, 0, 0, @.InstallReport_ExportFormat,
50, @.InstallReport_MinDateVariable, @.InstallReport_MaxDateVariable,
@.InstallReport_Visible,
dbo.idw_get_report_category_id( @.InstallCategory_ShortName ),
@.InstallReport_HelpFilename, 0,
@.InstallReport_Var1Title, @.InstallReport_Var1Description
);
The identity is on a column called ReportId, which is defined as (at table
creation time)
ReportId int PRIMARY KEY NOT NULL IDENTITY(1,1)
Thanks.
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O39thSqzFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Is the IDENTITY property still set for the column? If so, then what does
> your INSERT statement look like? If not, then your script is, well,
> imperfect.
>
>|||Gary,
Perhaps there is an INSTEAD OF trigger on the table (which requires that
you specify values for all non-null columns that have no DEFAULT constraint,
even if those values will not ultimately be inserted anywhere), or you have
IDENTITY_INSERT set to ON for this table.
If these guesses are wrong, please cut and paste the error message you get.
Steve Kass
Drew University
Gary wrote:

>Yes, It is still set. the insert statement does inserts using enumerated
>column names.
>INSERT INTO RptTable (
> ShortName, FullName, Description,
> StockReport, ReportFilename,
> AutoprintReport, PrintDaily, PrintWly, PrintMonthly,
> ExportReport, ExportDaily, ExportWly, ExportMonthly, ExportFormat,
> DisplayOrder, MinDateVariable, MaxDateVariable, Visible,
> ReportCategoryId, HelpFilename, CLCompliant,
> Param1_Label, Param1_Description)
>VALUES
> (@.InstallReport_ShortName, @.InstallReport_FullName,
>@.InstallReport_Description,
> 0, @.InstallReport_ReportFilename,
> 0, 0, 0, 0,
> 0, 0, 0, 0, @.InstallReport_ExportFormat,
> 50, @.InstallReport_MinDateVariable, @.InstallReport_MaxDateVariable,
>@.InstallReport_Visible,
> dbo.idw_get_report_category_id( @.InstallCategory_ShortName ),
>@.InstallReport_HelpFilename, 0,
> @.InstallReport_Var1Title, @.InstallReport_Var1Description
> );
>The identity is on a column called ReportId, which is defined as (at table
>creation time)
>ReportId int PRIMARY KEY NOT NULL IDENTITY(1,1)
>Thanks.
>-Gary
>"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:O39thSqzFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>
>