Scope parameter: multiple

Showing posts with label multiple. Show all posts

Friday, March 30, 2012

Scripting "Create to File" for multiple objects

What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database?

I would like a separate file for each object (table, view, or stored procedure).

Could someone point me in the right direction?

Thanks,

Paul
Seperate File - Use SMO programming using .NET and create it.

Wednesday, March 28, 2012

ScriptComponent with multiple ReadOnly variables

I'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:

The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.
When you type, "Me.Variables." in your code, what pops up in the list?|||I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.

I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.|||

mr_superlove wrote:
I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.
I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.

You are using the comma "," right?|||Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.|||

mr_superlove wrote:
Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.
I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

It works great for me. If I type in "myvar1,myvar2" and they do, in fact, exist at the component level (are scoped appropriately), then things work. If I remove the comma, or mistype the var name, I get the error you indicated. I'm sure you've done it already, but double check the spelling and the scope of the variables you are listing in the ReadOnlyVariables section.

Phil|||Are you referencing a variable that has an expression in it that could be causing scope issues, or something?|||

mr_superlove wrote:
Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.
I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

Just a small thing. There's no space after the comma is there?

If there is, there shouldn't be. People have been caught out by that before.

I know its simple but I just wanted to check.

-Jamie

|||Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.

I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.|||

mr_superlove wrote:
Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.
I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.

The interface for this is really bad isn't it? They should provide a combo box for you to select the variables that you want. Or at least explicitly check that the syntax of the property value is correct.

Would you mind raising something at Connect (http://connect.microsoft.com) asking for some changes to this.

-Jamie

Script works in DTS, but has problems as a Stored Procedure

I have a script that builds multiple tables and then builds tables from those tables, etc..

Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence.

As a result, some of the tables are blank when this script is run as a stored procedure.

Do I need to use transactions to prevent this compilation problem, or is there an easier setting that I can use to keep everything in the original sequence?

Thanks in advance.Anyone have any ideas on this? I can't use "GO", but I am still still having this compilation issue.sql

Monday, March 26, 2012

Script to populate for a large DB

can someone help me provide a script that will populate around 50GB of data
? Ideally it would be multiple tables with some various data types...if I
could add more data to Northwind or pubs or even AdventureWorks, that would
be fine..
ThanksOn May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
> can someone help me provide a script that will populate around 50GB of data
> ? Ideally it would be multiple tables with some various data types...if I
> could add more data to Northwind or pubs or even AdventureWorks, that would
> be fine..
> Thanks
BCP and Bulk Insert are good options for populating large amounts of
data quickly. This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
Also, you could consider using SSIS (Integration Services), if you are
using SQL Server 2005. It has some pretty useful ETL/transformation
options that should be helpful.
http://msdn2.microsoft.com/en-us/library/ms167031.aspx
http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am aware of these tools, but I wanted scripts where I could just run and
it would start populating..
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178481390.533564.160560@.y5g2000hsa.googlegroups.com...
> On May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
>> can someone help me provide a script that will populate around 50GB of
>> data
>> ? Ideally it would be multiple tables with some various data types...if I
>> could add more data to Northwind or pubs or even AdventureWorks, that
>> would
>> be fine..
>> Thanks
>
> BCP and Bulk Insert are good options for populating large amounts of
> data quickly. This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms175915.aspx
> Also, you could consider using SSIS (Integration Services), if you are
> using SQL Server 2005. It has some pretty useful ETL/transformation
> options that should be helpful.
> http://msdn2.microsoft.com/en-us/library/ms167031.aspx
> http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Hassan
TestBase www.tenerus.com
TESTBytes www.cai.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:uURKzgDkHHA.3484@.TK2MSFTNGP02.phx.gbl...
>I am aware of these tools, but I wanted scripts where I could just run and
>it would start populating..
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178481390.533564.160560@.y5g2000hsa.googlegroups.com...
>> On May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
>> can someone help me provide a script that will populate around 50GB of
>> data
>> ? Ideally it would be multiple tables with some various data types...if
>> I
>> could add more data to Northwind or pubs or even AdventureWorks, that
>> would
>> be fine..
>> Thanks
>>
>> BCP and Bulk Insert are good options for populating large amounts of
>> data quickly. This link might be helpful.
>> http://msdn2.microsoft.com/en-us/library/ms175915.aspx
>> Also, you could consider using SSIS (Integration Services), if you are
>> using SQL Server 2005. It has some pretty useful ETL/transformation
>> options that should be helpful.
>> http://msdn2.microsoft.com/en-us/library/ms167031.aspx
>> http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
>> Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>

Script to populate for a large DB

Friday, March 23, 2012

script to combine multiple rows into a single row

Hi everyone,

I really appreciate if anyone could help me with this tricky problem
that I'm having. I'm looking for a sample script to combine data in
multiple rows into one row. I'm using sqlserver. This is how data is
stored in the table.

ID Color
111 Blue
111 Yellow
111 Pink
111 Green

This is the result that I would like to have.

ID Color
111 Blue, Yellow, Pink, Green

There is no definite number of colors per ID. I have to use ID to
group these colors into one row. Therefore, ID becomes a unique key
in the table.

Appreciate your help and time. Thank you in advancelie_valerie@.yahoo.com (Valerie) wrote in message news:<f4825946.0308141505.4c29ff89@.posting.google.com>...
> Hi everyone,
> I really appreciate if anyone could help me with this tricky problem
> that I'm having. I'm looking for a sample script to combine data in
> multiple rows into one row. I'm using sqlserver. This is how data is
> stored in the table.
> ID Color
> 111 Blue
> 111 Yellow
> 111 Pink
> 111 Green
> This is the result that I would like to have.
> ID Color
> 111 Blue, Yellow, Pink, Green
> There is no definite number of colors per ID. I have to use ID to
> group these colors into one row. Therefore, ID becomes a unique key
> in the table.
> Appreciate your help and time. Thank you in advance

The short answer is that you should consider doing this in your client
application. For a longer answer, have a look at the thread "newbie
sql query question" in this group from a day or two ago.

Simonsql

Script to combine multiple rows into 1 single row

Hi,

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:

Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete

I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete

There is no definite number of materials per Company.

I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'The
Iterative Method' but my knowledge of SQL is very limited and I don't know
how to use this code to get what I need.

Can anyone help me?Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:
>
Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete
>
I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete
>
There is no definite number of materials per Company.
>
I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
'The Iterative Method' but my knowledge of SQL is very limited and I
don't know how to use this code to get what I need.

And that article covers the opposite process - unpacking the list.

Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.

Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:

SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a

o What is the datatype of Material? That is, is varchar or nvarchar?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

I hope it's not to late to get help on this one!

Here are the answers you are looking for:

1) I'm using SQL 2000
2) 40
3) nvarchar

To clarify the field names, it is 'material_name' instead of 'material' and
is 'to_company' instead of 'company_id'

Thanks!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98A19BE4CF73BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I'm working on a system migration and I need to combine data from
>multiple
>rows (with the same ID) into one comma separated string. This is how the
>data is at the moment:
>>
>Company_ID Material
>0x00C00000000053B86 Lead
>0x00C00000000053B86 Sulphur
>0x00C00000000053B86 Concrete
>>
>I need it in the following format:
>Company_ID Material
>0x00C00000000053B86 Lead, Sulphur, Concrete
>>
>There is no definite number of materials per Company.
>>
>I have read the part of
>http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
>'The Iterative Method' but my knowledge of SQL is very limited and I
>don't know how to use this code to get what I need.

>
And that article covers the opposite process - unpacking the list.
>
Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.
>
Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:
>
SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a
>
o What is the datatype of Material? That is, is varchar or nvarchar?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I hope it's not to late to get help on this one!
>
Here are the answers you are looking for:
>
1) I'm using SQL 2000
2) 40
3) nvarchar

I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.

Here is an example of a query that runs in Northwind. First run:

select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s

(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:

SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

If your maximum number is 8, you will need to add one more line.

Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.

If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.

Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products]. I tried
modifying the script but it doesn't work:

SELECT to_company,
MAX(CASE OD.rowno WHEN 1 THEN Material_Name END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + Material_Name END), '')
FROM Material__Bridge AS OD
GROUP BY OD.to_company
ORDER BY OD.to_company

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

Any ideas?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AED9C3878CYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I hope it's not to late to get help on this one!
>>
>Here are the answers you are looking for:
>>
>1) I'm using SQL 2000
>2) 40
>3) nvarchar

>
I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.
>
Here is an example of a query that runs in Northwind. First run:
>
select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s
>
(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:
>
SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID
>
If your maximum number is 8, you will need to add one more line.
>
Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.
>
If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I could have done the script with product ids instead of product names
but that seemed boring.

Quote:

Originally Posted by

The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.

It might be that you want to be spoon-fed a solution, but I have this funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.

I totally agree with not being spoon fed! I'm sorry I came across as wanting
to be. I'll try and work out what your script is doing :o) Thanks for your
help!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AF7D09BCD42Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>Thanks for the script. The difference between the Northwind database and
>mine is that all the data I want to get access to is in one table (unlike
>Northwind where it is spread over [order details[ and [products].

>
I could have done the script with product ids instead of product names
but that seemed boring.
>

Quote:

Originally Posted by

>It says there is an invalid column name 'rowno' - I guess this is right
>because there is no column with that name in my database! However, when I
>check in Northwind, there isn't one called that there either!

>
The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.
>
It might be that you want to be spoon-fed a solution, but I have this
funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to
understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I totally agree with not being spoon fed! I'm sorry I came across as
wanting to be. I'll try and work out what your script is doing :o)

There is one thing I should have pointed out. In my query there was this
part:

(SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD

That is a *derived table*. A derived table is logically a temp table in
the query so to speak, but not materialised, and the actually computation
order can be different as long as the result is the same. Derived tables
is an enormously powerful tool to build complex queries with, and saves
you from using real temp tables.

Friday, March 9, 2012

Script component with multiple outputs

Hi,

I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.

If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.

In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.

Any help on this matter would be greatfully recieved.

Cheers,

Grant

There are some basica exampes of this in Books Online, http://msdn2.microsoft.com/en-us/library/849dd38a-abb5-4702-a413-882aae3980a5(SQL.90).aspx, are they any help?

Yes to the try catch idea.

|||

Here is some additional good links:

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

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

Thanks,

Greg Van

Mullem

script component not executed

I have a ssis project that contains a parent package and 2 child packages. The parent package loads data from multiple flat files into a database and then kicks off the 2 child packages using separate execute package tasks.

The child package has a data flow.Within the data flow data is extracted from a database. The data is transformed using a script component and then loaded into a second database.

The problem I have is that the second child package is not working. It appears as if the data is being extracted fine.However the script component does not seem to be being executed so the columns that are being transformed are not being changed and so the write to the database fails. When I send the error rows to a database table with all the fields varchar(200) the write completes but the transformed columns are blank.

Also if I put a message box or ComponentMetaData.FireInformation in the script component I get no output.

However when i run this project on my development machine it runs fine but when I run it on the staging server it gives the problems explained above.

Any ideas please?

Thanks

You have two child packages. Do they both have scripts? Do the child packages run in-process or out-of-process?

How are you executing the packages in each environment? Any differences there?

Friday, March 30, 2012

Scripting "Create to File" for multiple objects

Wednesday, March 28, 2012

ScriptComponent with multiple ReadOnly variables

Script works in DTS, but has problems as a Stored Procedure

Monday, March 26, 2012

Script to populate for a large DB

Script to populate for a large DB

Script to populate for a large DB

Friday, March 23, 2012

script to combine multiple rows into a single row

Script to combine multiple rows into 1 single row

Friday, March 9, 2012

Script component with multiple outputs

script component not executed

Scope parameter

Blog Archive

About Me