Reading a variable in a script taks used as source

Hi,

I'm using a script task as source and I need to read a global variable.

I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)

This gives an errero on 'Dts.' saying it can not resolve it.

If you'd like, you can predict what variable you'd like to read or write to, you can use the ReadOnlyVariable or ReadWriteVariable properties to pass in the variable to your script. At that point, you won't need the variable dispenser. Then, you could read the variable by using code as easy as this:

LocalVariable = DTS.Variables("VariableName").Value

This video may help you as well: http://www.jumpstarttv.com/Media.aspx?vid=6

-- Brian Knight

|||Hi,

I've typed in the variables in the ReadOnlyVariables, but how can I access them in the script?

Also, 'LocalVariable = DTS.Variables("VariableName").Value' doesn't work (because it's a source script?). I get an error that VB is unable to resolve 'dts'.

I'm using folowing import:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.Runtime

Thank you

Read SP source code from VB

Hi,
I'm developing an application to crypt SQL databases and i need to
read SP and Views source code from VB6, if you know how to do this
please answer me.

Greetings."Carpi" <carlos.rodriguez@.sparks-software.com> wrote in message
news:5280f320.0401080621.514fd41f@.posting.google.c om...
> Hi,
> I'm developing an application to crypt SQL databases and i need to
> read SP and Views source code from VB6, if you know how to do this
> please answer me.
> Greetings.

Probably the best place to start is the .Script method of the SQLDMO
objects, which will generate the CREATE statements for all object types.

Simon

Read source data without waiting for possible locks

hi!

I wonder if anyone can tell me how we can run select queries in an OLE DB data flow task and tell the target SQL 2000 server it should allow reads at all time. Currently when a lock is on the source table our SSIS package will sit and wait untill the lock on the source table is gone.

Thanks.

Marc

Try issueing the READUNCOMMITTED or NOLOCK switch.

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

|||

Hi,

When I simulate a lock on a table with:

begin transaction
update table set column = 'test' where columnkey = 1

and I execute the package reading from this table it will wait untill I commit the transaction. If I change the select statement in the package in select ....... with (nolock) and try running it again it still keeps waiting. However, when I execute the same SQL statement in management studio I will get the results.

Can you explain this?

Thanks

|||

Use NOLOCK on your SSIS query.

Management Studio may be issuing that behind the scenes. (You're deep into Transact-SQL territory here, and there is a better forum for this discussion.)

Rdl files on SourceSafe

I can't get my rdl files off source safe into my solution. Error "The host
name in the certificate is invalid or does not match."Hi Cadel,
Happy New Year!
Based on my experience, error message "The host name in the certificate is
invalid or does not match" usually suggests some redirection logic in your
application for incoming requests. For example, the request to
http://localhost would redirect to another machine using https://. The URL
being redirected to did not match the URL in the SSL Certificate resulting
in the message. The problem is that once you add the redirection logic,
you will not be able to open the project to disable it.
You should disable the redirection or add logic to not redirect if the
request is from http://localhost or from any of the above pages. You could
also add an <appSettings> section to the web.config. Then in your code
check the app setting and perform the redirection based on this value.
This will allow you to enable/disable the redirection by changing the
web.config. Here is a link to the <appSettings> information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/ht
ml/gngrfappsettingselement.asp
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I only have a Solution file. I created a new solution in VB 2003 and I'm
trying to Add project from Source Control.
It seems my reporting service reports are stuck in source safe.
I'm able to add other projects in, but when I try to add my reports folder
it errors, "The source control server folder you selected contains no Visual
Studio .NET solution or project file. Please select a different server
folder."
So, then I select my Solution file and that's when I get the error, "The
host name in the certificate is invalid or does not match."
How do I add my rdl files from Source safe to my new solution file?
"Michael Cheng [MSFT]" wrote:
> Hi Cadel,
> Happy New Year!
> Based on my experience, error message "The host name in the certificate is
> invalid or does not match" usually suggests some redirection logic in your
> application for incoming requests. For example, the request to
> http://localhost would redirect to another machine using https://. The URL
> being redirected to did not match the URL in the SSL Certificate resulting
> in the message. The problem is that once you add the redirection logic,
> you will not be able to open the project to disable it.
> You should disable the redirection or add logic to not redirect if the
> request is from http://localhost or from any of the above pages. You could
> also add an <appSettings> section to the web.config. Then in your code
> check the app setting and perform the redirection based on this value.
> This will allow you to enable/disable the redirection by changing the
> web.config. Here is a link to the <appSettings> information:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/ht
> ml/gngrfappsettingselement.asp
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Can you access Source Safe through its own UI instead of through Visual
Studio? If so, do a Get Latest on the Source Safe folder that contains
your *.rdl files. Then check if that folder contains a *.rptproj file.
Sounds like it probably doesn't. However, you should be able to
create a new one by right clicking your solution in Visual Studio's
Solution Explorer and choosing Add | New Project. After adding the
project, you should be able to right-click it add choose Add | Existing
Item to add each *.rdl to the project. Then right-click again on the
project and choose Add Project to Source Control.
Hope this helps.
PS - Judging from his answer, I don't think Mr.Cheng understood your
question.|||Thanks that worked for the rdl files, but my rds files come up with an error
when I try "Add existing item", the error is "Deserialization failed: There
is an error in XML document (4,4)."
"Lee" wrote:
> Can you access Source Safe through its own UI instead of through Visual
> Studio? If so, do a Get Latest on the Source Safe folder that contains
> your *.rdl files. Then check if that folder contains a *.rptproj file.
> Sounds like it probably doesn't. However, you should be able to
> create a new one by right clicking your solution in Visual Studio's
> Solution Explorer and choosing Add | New Project. After adding the
> project, you should be able to right-click it add choose Add | Existing
> Item to add each *.rdl to the project. Then right-click again on the
> project and choose Add Project to Source Control.
> Hope this helps.
> PS - Judging from his answer, I don't think Mr.Cheng understood your
> question.
>|||There are other threads on this forum for "deserialization failed" and
some of the responses (some from MSFT folks) seem to suggest that the
report designer sometimes creates invalid XML (which is what the RDS
files contain). Can you post the contents of one of the RDS files that
produces the error? Preferably the one that gave you the message you
posted earlier...I would guess that (4,4) is a line number and position
reference and that may help us find the problem.|||<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
<Name>Claims</Name>
<DataSourceID>a4687848-e640-49e1-b479-
É¥962314bea</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>data source=db;initial catalog=Claims</ConnectString>
</ConnectionProperties>
</RptDataSource>
"Lee" wrote:
> There are other threads on this forum for "deserialization failed" and
> some of the responses (some from MSFT folks) seem to suggest that the
> report designer sometimes creates invalid XML (which is what the RDS
> files contain). Can you post the contents of one of the RDS files that
> produces the error? Preferably the one that gave you the message you
> posted earlier...I would guess that (4,4) is a line number and position
> reference and that may help us find the problem.
>|||Looks like you've got a corrupted GUID between your <DataSourceID> and
</DataSourceID> tags. The portion to the right of the 4th hyphen
should be a string of 12 characters made up of any combination of 0
through 9 and/or a through e (case-insensitive). Instead, it looks
like some non-printing characters got in there somehow (as evidenced by
the little square immediately after the 4th hyphen). This GUID was
probably produced by Visual Studio when you defined the data source
(i.e., the .rds). It's anybody's guess as to how it got corrupted.
Anyway, all you care about is fixing it...you could try to replace the
entire GUID (i.e., everything between the <DataSourceID> and
</DataSourceID> tags) with a new GUID you generate yourself (choose
Tools | Create GUID in Visual Studio), but the safer approach may be to
delete the data source (i.e., the .rds) and recreate it (with the same
name, etc., so your reports will still work).|||Hello Cadel,
I just wanted to check back with you on the status of this issue. Please
don't hesitate to contact us if you have any further questions or concerns.
Thanks very much, and have a great weekend!
Hope this helps!
Sincerely,
Dana Brash
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Rdl files on SourceSafe
>thread-index: AcYVLugG5aBk+6DmRSKkEhRPzSx0NQ==>X-WBNR-Posting-Host: 204.87.106.66
>From: =?Utf-8?B?TWlrZSBM?= <Cadel@.nospam.nospam>
>References: <9369838B-3C59-4BAD-AB21-90F42C375AC7@.microsoft.com>
<szZS6QaEGHA.2660@.TK2MSFTNGXA02.phx.gbl>
<B592D9E9-8504-4634-B688-230942D12718@.microsoft.com>
<1136487927.680465.74720@.z14g2000cwz.googlegroups.com>
<F018555D-9444-4F85-9E35-4E29573C51ED@.microsoft.com>
<1136584470.444321.281380@.f14g2000cwb.googlegroups.com>
>Subject: Re: Rdl files on SourceSafe
>Date: Mon, 9 Jan 2006 07:11:03 -0800
>Lines: 23
>Message-ID: <F36CF917-EFCE-4E0D-8D79-22CCDBEDF5D6@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:66418
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
><?xml version="1.0" encoding="utf-8"?>
><RptDataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <Name>Claims</Name>
> <DataSourceID>a4687848-e640-49e1-b479-
>É¥962314bea</DataSourceID>
> <ConnectionProperties>
> <Extension>SQL</Extension>
> <ConnectString>data source=db;initial catalog=Claims</ConnectString>
> </ConnectionProperties>
></RptDataSource>
>"Lee" wrote:
>> There are other threads on this forum for "deserialization failed" and
>> some of the responses (some from MSFT folks) seem to suggest that the
>> report designer sometimes creates invalid XML (which is what the RDS
>> files contain). Can you post the contents of one of the RDS files that
>> produces the error? Preferably the one that gave you the message you
>> posted earlier...I would guess that (4,4) is a line number and position
>> reference and that may help us find the problem.
>>
>|||Hope what helps?|||Hi Cadel,
You can try to add it manually to VSS.
1. Open VSS Explorer, navigate to the project folder.
2. Click the add file button on the Toolbar and add it.
Kevin Yu
======="This posting is provided "AS IS" with no warranties, and confers no
rights."|||That worked.
Thank you.
"Lee" wrote:
> Looks like you've got a corrupted GUID between your <DataSourceID> and
> </DataSourceID> tags. The portion to the right of the 4th hyphen
> should be a string of 12 characters made up of any combination of 0
> through 9 and/or a through e (case-insensitive). Instead, it looks
> like some non-printing characters got in there somehow (as evidenced by
> the little square immediately after the 4th hyphen). This GUID was
> probably produced by Visual Studio when you defined the data source
> (i.e., the .rds). It's anybody's guess as to how it got corrupted.
> Anyway, all you care about is fixing it...you could try to replace the
> entire GUID (i.e., everything between the <DataSourceID> and
> </DataSourceID> tags) with a new GUID you generate yourself (choose
> Tools | Create GUID in Visual Studio), but the safer approach may be to
> delete the data source (i.e., the .rds) and recreate it (with the same
> name, etc., so your reports will still work).
>

RB - Multiple Data Source for Report Builder

I add two data sources to the designer. Create a new dsv using one data source first. Then right click in the designer to add a new table. This time I use the second data source. When I create a Report Model and run it, an error occurred: "Message: Invalid object name 'dbo.tblTrade'. Command: SELECT COUNT(*) FROM [dbo].[tblTrade] t"

It does not recognize the second data source. Look at the property of the dsv, it only points to the first data source, not the other one...

If you look at the XML code for the dsv, there is a DataSourceID tag right above the Schema tag. Can this tag be expanded to include the 2nd data source? Can the XML code be tweaked to include the second data source?

</Annotations>

<DataSourceID>Db House01</DataSourceID>

<Schema>

|||"Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. A Data Source View for a Report Model Project, unlike a Data Source View for an Analysis Services Project, can only reference a single Data Source. "

Is this true?

http://www.databasejournal.com/features/mssql/article.php/10894_3598931_4

|||

Yes, that is correct.

|||By creating a named query can overcome the single data source limitation.|||

This solution does not work. If one try to create model bases on such Data source View, table entity for the named query defined in DSV, which is not bases on prime data source, is not being created.

Can you please give some steps to over come this problem?

|||

MSDN Online Book link below:

http://msdn2.microsoft.com/en-us/library/ms175683.aspx

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt

raw file source avoid bad data?

I want to have a data flow like this:

1. raw file source
2. validate data? or conditional split?
3. sql server destination

there is some bad data in my raw file. by bad data I mean datetimes that are out of the range that sql server can handle.

this should be simple right?What created the raw file and where did the data within it originally come from?|||a different package that does a raw extract from an oracle rear end.|||Well then the fact that its in a raw file is irrelevant. Your issue is the conversion between Oracle data types and SQL data types.

What columns does it complain about?
What is the datatype of those columns in your SSIS package?
What is the the datatype of the columns in the Oracle source?
What is the the datatype of the columns that you are trying to populate in the SQL Server destination?
What error messages do you get?

You need to provide more information than simply "by bad data I mean datetimes that are out of the range that sql server can handle."

-Jamie

|||[SQL Server Destination [31]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

they are datatime on both sides.

I was hoping there was something in the conditional split thingie but there is no ISDATE() function.

that is really all I need. I just want to skip the rows with bad dates. I can't fix the data it is just bad and there is nothing I can do about it.

it's like a 1,000 rows in millions is bad so it's not a big deal. seriously it's not a big deal.

I just want to avoid importing those rows with bad data. or better yet scrubbing the bad values to nulls.|||Seems like if you know what your bad dates look like (or you know what a good date looks like, which you should since BOL states what is valid for different types in SQL Server) then you could use a conditional split or a script component to filter the dates out. Obviously for the conditional split, it wouldn't be as simple as if there was an ISDATE method but it could be done with DATEPART comparisons against the know bad (or good) values.

Matt|||

datepart would ***-u-me a valid date be passed?

I will have to investigate the script option.

The implementation of the conditional split transformation seems pretty weak. Why not provide access to the entire managed runtime in the condition part?

Expressions seem like an afterthought. I would expect a lot more.

I'm just missing something here this should be easy and I'm making it way to hard.

|||Hi,
I'm puzzled by your sceptism of the expression language. Does it not do what you need?

Access to the entire managed runtime (I presume you mean the dotnet framework) is provided through the script component (which gives you regular expressions et al). As such, is there really a need to do that through the Derived Column Component as well?

Interested to hear your thoughts.

-Jamie|||

I'm reading the help on expressions. It does not seem like it will do what I need; and forcing me to use vb gives me facial ticks. I just don't like vb. I know a lot of people live and die by it but it's not for me. I’ll use it if I have to but I avoid it if I can.

why not make expressions work like they do in reporting services? you can do almost anything. In-line without writing a custom script thingies (even though it's vb based)

why invent yet another limited set of functions/conditions?

I think it's just a learning curve thing for me and eventually I figure out what I want to do.

all I want to do is write code that does

if ( isdate(mydate))
select mydate
else
select null

that's it; seems simple but now I have to learn the in's and out's of yet another vb based subsystem to make it happen with a script component.

I’m really sure I’m missing something really basic here and the light bulb will go on and I’ll say “cool I can just do it this way”.

|||There is no IS_DATE() function. I agree that would be handy.

IF...ELSE... can be provided using the conditional operator

-Jamie|||

so debugging script components does not work?

nice.

all the time I have wasted with integration services already.

I could have hand written a console c# app to to the same thing I was trying to do with integrations services.

I think I will wait for integration services 2.0 by that time it might be actually usefull.

Raw File as Source for Multiple Packages

I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.

The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.

My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.

My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.

If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!

Thanks in advance.

Why not use a SQL Server table instead?|||

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

|||

omegarazor wrote:

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

This is the process, but I would like to add two refinements:

If you're going to be dynamically creating and dropping the table, be certain to build the first Execute SQL task so that it has a "IF EXISTS .. DROP TABLE; CREATE TABLE" logic, so it will run correctly regardless of whether the table already exists at run time. (Use the script generation tools in SSMS to build this script.) Set DelayValidation = True for any tasks that rely on the table, so the package can run regardless of whether the table exists.