database file locations: February 2012

Saturday, February 25, 2012

new hardware - sqlserver 2000 transfer

Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
Thanks
See if this helps:
How to move SQL Server to a new computer?
http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
Thanks
|||If you have cubes, you'll have to back them up using the cube backup
program..
additionally you may have to change the datasource property in Analysis
services ( if you are changing the server name.)
You may also have to change the client connectivity stuff for people who
connect to the server ( if the server name is changing..)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
> Hi!
> I have a sqlserver 2000 (windows 2000 server) running and my machine is
now
> too slow.
> So I want to buy a new one (dualprocessor) and transfer my database to
that
> server.
> I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could
I
> get that all to my new server?
> my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
> how could I get all these things to my new server?
> Who can me say the steps?
> Thanks
>

new hardware - sqlserver 2000 transfer

Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
ThanksSee if this helps:
How to move SQL Server to a new computer?
http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
Thanks|||If you have cubes, you'll have to back them up using the cube backup
program..
additionally you may have to change the datasource property in Analysis
services ( if you are changing the server name.)
You may also have to change the client connectivity stuff for people who
connect to the server ( if the server name is changing..)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
> Hi!
> I have a sqlserver 2000 (windows 2000 server) running and my machine is
now
> too slow.
> So I want to buy a new one (dualprocessor) and transfer my database to
that
> server.
> I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could
I
> get that all to my new server?
> my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
> how could I get all these things to my new server?
> Who can me say the steps?
> Thanks
>

new hardware - sqlserver 2000 transfer

Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
ThanksSee if this helps:
How to move SQL Server to a new computer?
http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
Hi!
I have a sqlserver 2000 (windows 2000 server) running and my machine is now
too slow.
So I want to buy a new one (dualprocessor) and transfer my database to that
server.
I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could I
get that all to my new server?
my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
how could I get all these things to my new server?
Who can me say the steps?
Thanks|||If you have cubes, you'll have to back them up using the cube backup
program..
additionally you may have to change the datasource property in Analysis
services ( if you are changing the server name.)
You may also have to change the client connectivity stuff for people who
connect to the server ( if the server name is changing..)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1091784043.72762@.news.liwest.at...
> Hi!
> I have a sqlserver 2000 (windows 2000 server) running and my machine is
now
> too slow.
> So I want to buy a new one (dualprocessor) and transfer my database to
that
> server.
> I have a lot of dts jobs, user, roles, rights, cubes, dimensions how could
I
> get that all to my new server?
> my main sqlserver database is 120 GB is on a 136 GB drive f in windows.
> how could I get all these things to my new server?
> Who can me say the steps?
> Thanks
>

New Hardware

I have found a couple of web sites that discuss moving a sql server and its
database from one machine to another but nothing in good detail.
I have also looked out on the Knowledgebase and have found nothing as of
yet. I know there has to be some articles on this.
Anybody have a link to one?
--
Paul BergsonIn your Knowledge Base search you missed:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
Jacco Schalkwijk
SQL Server MVP
"Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
news:ueLrT3zSFHA.2128@.TK2MSFTNGP15.phx.gbl...
>I have found a couple of web sites that discuss moving a sql server and its
> database from one machine to another but nothing in good detail.
> I have also looked out on the Knowledgebase and have found nothing as of
> yet. I know there has to be some articles on this.
> Anybody have a link to one?
> --
> Paul Bergson
>
>|||Actually this isn't what I need this is to move individual db's not the
entire system. It is required that I move all the system db's as well.
--
Paul Bergson
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e3TqbC0SFHA.3184@.TK2MSFTNGP15.phx.gbl...
> In your Knowledge Base search you missed:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
> news:ueLrT3zSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> >I have found a couple of web sites that discuss moving a sql server and
its
> > database from one machine to another but nothing in good detail.
> >
> > I have also looked out on the Knowledgebase and have found nothing as of
> > yet. I know there has to be some articles on this.
> >
> > Anybody have a link to one?
> >
> > --
> >
> > Paul Bergson
> >
> >
> >
>|||Please clarify...
moving a few user databases OR all the databases (system and user)?
"Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
news:u8PWyq0SFHA.3684@.TK2MSFTNGP10.phx.gbl...
> Actually this isn't what I need this is to move individual db's not the
> entire system. It is required that I move all the system db's as well.
> --|||1. backup your DBs on first server
2. restore your DBs on second server
3. on second server create user accounts that you wanted to transferred from
first server to second server
4. in each DB run sp_change_users_login 'Update_One' to map each database
user (select * from sysusers) to the new accts you just created in step 3.
You can write a cursor to run dynamically.
hth,
"Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
news:u8PWyq0SFHA.3684@.TK2MSFTNGP10.phx.gbl...
> Actually this isn't what I need this is to move individual db's not the
> entire system. It is required that I move all the system db's as well.
> --
> Paul Bergson
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:e3TqbC0SFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> In your Knowledge Base search you missed:
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
>>
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
>> news:ueLrT3zSFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> >I have found a couple of web sites that discuss moving a sql server and
> its
>> > database from one machine to another but nothing in good detail.
>> >
>> > I have also looked out on the Knowledgebase and have found nothing as
>> > of
>> > yet. I know there has to be some articles on this.
>> >
>> > Anybody have a link to one?
>> >
>> > --
>> >
>> > Paul Bergson
>> >
>> >
>> >
>>
>|||Go to google groups and search this NG for "move system database". IIRC,
Andrew Kelly has posted a rather complete list of links that cover this
frequently asked topic.
"Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
news:u8PWyq0SFHA.3684@.TK2MSFTNGP10.phx.gbl...
> Actually this isn't what I need this is to move individual db's not the
> entire system. It is required that I move all the system db's as well.
> --
> Paul Bergson
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:e3TqbC0SFHA.3184@.TK2MSFTNGP15.phx.gbl...
> > In your Knowledge Base search you missed:
> >
> > HOW TO: Move Databases Between Computers That Are Running SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
> > news:ueLrT3zSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> > >I have found a couple of web sites that discuss moving a sql server and
> its
> > > database from one machine to another but nothing in good detail.
> > >
> > > I have also looked out on the Knowledgebase and have found nothing as
of
> > > yet. I know there has to be some articles on this.
> > >
> > > Anybody have a link to one?
> > >
> > > --
> > >
> > > Paul Bergson
> > >
> > >
> > >
> >
> >
>|||New hardware. Retiring the old sql server. I need to move everything...
--
Paul Bergson
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:#Nr$t#0SFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Please clarify...
> moving a few user databases OR all the databases (system and user)?
>
> "Paul Bergson" <pbergson_nospam@.allete.com> wrote in message
> news:u8PWyq0SFHA.3684@.TK2MSFTNGP10.phx.gbl...
> > Actually this isn't what I need this is to move individual db's not the
> > entire system. It is required that I move all the system db's as well.
> >
> > --
>

New Guy Question: Which came first ... the chicken or the checkpoint file?

Hello all...here's a question from a newbie ...

I'm attempting to configure my SSIS package to use a checkpoint file. I've checked the docs, and I believe I've set all the package properties for this ... except they are looking for the name of the checkpoint file. I've tried a couple of experiments such as c:\myCheckpoint.txt and c:\myCheckpoint.xml ... just for grins ... to see if SSIS would generate a checkpoint file for me at the start of the package ... no luck:

Message: Checkpoint file "C:\myCheckpoint.xml" failed to open due to error
0x80070002 "The system cannot find the file specified.".

From these runtime errors I've seen, it appears the checkpoint file should be an XML file that is created w/ specific nodes, etc. My question is ... how do I create the checkpoint file in advance so that when the package is executed it will find it and run?

SSIS ... pretty cool stuff!

Thanks!This is controlled by Package.CheckpointUsage property.
You probably have it set to 'Always' which causes package
to require the checkpoint file. You need to set it to 'IfExists' -
then the checkpoint file will be used if it exists.|||Yeah, that's what I figured too. The property options are Always, IfExists and Never. The doc doesn't specify that a file is created for you if you use Always, and on practical experimentation this didn't seem to help. Perhaps there's another property I missed; I did try to follow all the Package property settings that appeared to relate to checkpoints.

Thank you very much for trying!

DB|||The checkpoint is created automatically when the package fails (if it is configured to use checkpoints).

When you set CheckpointUsage=Always, you tell package that it has to start from previously saved checkpoint, i.e. the checkpoint file is expected to exist.

If you don't have checkpoint file yet, set CheckpointUsage=IfExists and run the package. It will run fine, and if it fails the package will create a checkpoint file. Next time when you run this package it will use that checkpoint.|||Thank you very, very much for your help! That makes sense!

Doug|||

Being another newbe, this post has been exceedingly helpful - I ran into the same issue!

Thanks! :-)

New Guy Question: Which came first ... the chicken or the checkpoint file?

Hello all...here's a question from a newbie ...

Message: Checkpoint file "C:\myCheckpoint.xml" failed to open due to error
0x80070002 "The system cannot find the file specified.".

SSIS ... pretty cool stuff!

Thank you very much for trying!

DB|||The checkpoint is created automatically when the package fails (if it is configured to use checkpoints).

When you set CheckpointUsage=Always, you tell package that it has to start from previously saved checkpoint, i.e. the checkpoint file is expected to exist.

Doug|||

Being another newbe, this post has been exceedingly helpful - I ran into the same issue!

Thanks! :-)

New Guy Question 2: SQL on-the-fly at package runtime?

Here I go again...thanks for your patience...

I'm trying to create a single, all-purpose SSIS package that would move data
from a source database table to a matching target database table. The
requirement is to create the package such that the SQL statement used by SSIS
could be supplied at runtime. This would allow a single package to be used
for different SQL statements, representing different tables, rows and
columns, etc. as needed. The data movement combinations are highly variable,
so I'm hoping to find a flexible one-size-fits-all solution with a single
package.

I've looked at setting up a string variable for the SQL statement, and
populating it at runtime using a config file or command line SET statement.
The package is initially built with a temporary SQL statement for the
variable ("select x from dual" -- appologies to the Oracle folks.) When I
use a config file and substitute in the SQL statement I want to use on a
given run (i.e. select * from myTable) SSIS complains about validation,
column mismatch problems, etc. I've played with shutting off validation, but
I'm not sure how to get around the output and metadata column problems, etc.

I suspect that I may also face this issue with every standard SSIS transformation that is called in my data flow...

I'd like to try to avoid writing custom components w/ C# for this. Before I
go down that route I was wondering if anyone had tried this or had a
suggestion.

Thanks so much!Your observations are correct - it is simple to change SQL statement with configuration or with property expressions, but the data flow "remembers" columns and other metadata about transforms, so it is not easy to change metadata at runtime. Writing custom components would not help much - the issue is not in the component, but in the data flow task that keeps metadata about data paths. It needs it to be able to move data between components, thus the source or transform can't just start producing data that does not conform to the its metadata in data flow task.

Several people overcame this problem by programmatically modifying or building SSIS package before execution - this is not very simple, but works.|||Well, at least it sounds like I asked a reasonable question for a new guy!

Let me see if I understand what you're saying:

1. Building a "pre-compiled" package with a custom C# Data Flow Source component might allow me to use SQL on the fly, and even specify output and metadata column specifications in my custom component. However, all the downstream standard SSIS Data Flow Transformations and Data Flow Destinations would not be able to accept any flow of data from my custom component because of a lack of metadata (essentially the SQL statement and columns) that would not be available to them at package build/compile time. So, this route sounds like a no go.

2. What you're saying is that I can use C# to programatically build and execute an entire package on-the-fly to pull this off. From reading the docs, it *appears* that I can build a Data Flow Source component w/ SQL on the fly, and establish output column metadata for that source component in my code. Once I've done this, it *appears* that I can then sequentially add the standard SSIS Transformations and Data Flow Destinations as needed, as they WILL be aware of my table and output column metadata established earlier by my custom component. Once I've strung all the package components together, I build the package and *poof* ... ad hoc SQL in a "single package." The name of the game is to first put all the pieces in place and then build the package.

Is my understanding correct, especially on #2 where I'm assuming I'll be able to use downstream standard SSIS components. This is going to be pretty tough to code (for an average Joe like me) and I have to be very, very careful not to go down the wrong path here as I'm under serious time pressure.

SSIS is a GREAT product!

Thanks!|||Yeah, this is pretty good understanding. One change - you probably don't need a custom Source component, a standard OLEDB Source/SQL Source or ADO.NET Source should be fine.

Regarding #2- basically, using the programmatic API to construct the package you can do everything that you do in designer (since the designer uses the same API). But it is much simpler to use the designer :)

Another possible solution is to start with a template package created in designer, then modify it programmatically before executing, updating the data flow column metadata to match the source table.|||OK, I understand your point on the OLE DB Source. That makes sense, and I've seen that in the programming examples in the docs.

On your last point, I'm not sure I follow you. From what you're saying it *sounds* like I can create a BI package using the SSIS GUI and then somehow export the source code as a template that represents all the package components out to C#. Once they are in C# I can use this source code with VS 2005, modify it and then build my own custom package, including the twist w/ the on-the-fly SQL. Did I get this right?

Assuming I'm following you, I've been poking around the BI designer, VS .Net 2005 (C#) and I've RTFM'd as much as I could. I do see a grayed out File / Export Template option when I'm working in the SSIS GUI, but I haven't figured out how to "activate" it. So, I'm missing some piece of the puzzle here.

Thank you for your patience with the new guy. Great support!

DB|||

We don't have ability to export SSIS package to code, although there is a third party effort to provide this functionality - see http://www.ivolva.com/ssis_code_generator.html

What you can do is create template DTSX in designer (just a regular DTSX file, I mean logically it will be template, but it is a regular package when it comes to designer), save it somewhere. Then you load this DTSX file into object model (Package.Load) and then modify it to build you custom package. This way you only have to worry about what is special for this specific instance of package, leaving the stuff that is shared as created in the designer.

|||OK, the above all makes sense.

Here's my last (and probably unfair because it is a judgement call) question on this thread. Thanks again for your patient responses.

Assuming I can pull off the coding effort and we do a reasonable job of testing, etc., is the idea of using "dynamic" SQL with SSIS sensible in this context? I can see A LOT of leverage by having the capability of creating a "single", standard SSIS package to handle a wide variety of SQL statements that will vary all the time.

Since I cannot accomplish this with a single static SSIS package, I'm essentially looking at simulating this by combining my dynamic SQL statements with some standard C# source code. Every time I want to move some data from here to there I have to perform a build on a SQL Server 2005 host using SSIS.

We REALLY want to use SSIS to pull this all together. Strictly looking at this from the SSIS product point-of-view, with an emphasis on reliability, assuming that I have a total of 300 possible SQL statements to deal with, am I better off:

1. Building (and therefore maintaining) 300 individual packages using all the standard SSIS GUI tools, etc. or

2. Working out the programming for a "standard package" (which should be easier to maintan) but will require a new build on a given SQL Server 2005 host each and every time I want to use it to move data?

I guess my concern is that I don't want to go down a difficult development path and two months later realize that I'm really going against the grain of the way SSIS was intended to be used. Having to constantly build packages on the fly is also a concern.

Thanks again!|||Doug,

An "all-purpose" package is a lot to ask for. On the other hand, assuming that the data movements you're talking about are similar in structure and workflow, you shouldn't need to "constantly build package on the fly."

The Integration Services object model is indeed available for you to create, configure and execute a new package line by line, or to load, optionally reconfigure, and execute an existing package.

My colleague Michael has suggested taking the best of both worlds -- save all the pieces that won't change in a "template" package, and use managed code in a custom application to load, reconfigure, and run that package dynamically. So instead of an all-purpose package, it's more accurate to say that you'd have an all-purpose SSIS application.

Configuring a saved "template" package for changing data sources and destinations (and you haven't mentioned what type of transformations you're doing in between) would require a bunch of lines of code, let's not deny it. You would need to configure the columns collections on various data flow components and possibly other properties as well. There are some Books Online topics and samples to get you started on this, though not a complete pre-packaged solution of the type you're looking for.

The degree of effort involved will depend in large part on how much one package will differ from another...if it's the same data movement but with different columns, not too big a deal (conceptually at least). And you could be an early SSIS hero for sharing what you learn! Best wishes,

-Doug
|||Mike and Doug:

Thank you so very much for your help on this. This has been one of the best customer service experiences I've had with a software company in a long time. I appreciate the fact that you were both willing to help me with looking at the strategy, and not just limiting your comments to "safe", scripted answers as so many other companies do. This exchange was worth the cost of the MSDN subscription alone.

The good news is that I think I have a pretty good idea of how to attack the problem. The bad news is that I'll probably be back with more questions!

All the best,

Doug B|||

Hello-

Has anyone made any progress on this issue? I am trying to programmatically modify the output columns and related downstream input columns in an existing SSIS package . I have boiled this down to a very simple sample scenario, to no avail:

I have a "template" package that contains an Ole DB source that reads 2 columns from a Sql Server table. The source has data access mode "Sql command from variable", which references a package variable that contains the query string (select col1, col2 from table1). The source connects to a flat file destination, which writes out a text file with the values from the 2 columns. In a separate project, I load the package & programmatically change the query string to select only 1 column (select col1 from table1). Here is the condensed sample code I am using:

Application app = new Application();

Package m_package = app.LoadPackage(packageName, null);

m_dataFlow = ((TaskHost)m_package.Executables[0]).InnerObject as MainPipe;

// the package variable is linked to the source (Sql command from variable)

m_package.Variables["SqlQueryString"].Value = "select col1 from table1";

// get the data flow source component for the input csv file

IDTSComponentMetaData90 sourceComponentMetaData = m_dataFlow.ComponentMetaDataCollection["InputSql"];

CManagedComponentWrapper sourceComponentWrapper = sourceComponentMetaData.Instantiate();

// -- this causes an error

//sourceComponentWrapper.ReinitializeMetaData();

// input columns are valid, so this does not help

if (!sourceComponentMetaData.AreInputColumnsValid)

sourceComponentMetaData.RemoveInvalidInputColumns();

// -- AcquireConnections fails

//sourceComponentWrapper.AcquireConnections(null);

//sourceComponentWrapper.ReinitializeMetaData();

//sourceComponentWrapper.ReleaseConnections();

DTSValidationStatus validationStatus = sourceComponentWrapper.Validate();

// status is DTSValidationStatus.VS_ISCORRUPT (?)

// there are still 2 output columns coming out of the source

foreach (IDTSOutputColumn90 outputColumn in sourceComponentMetaData.OutputCollection[0].OutputColumnCollection)

Console.WriteLine(outputColumn.Name + ", " + outputColumn.DataType.ToString());

// todo: configure inputs of destination

DTSExecResult result = m_package.Execute();

When I open the package in the designer to try to do this manually, I have to do the following:

1. Change the query string variable manually.

2. Open the source - it says "The component is not in a valid state.... the external metadata column (col2) needs to be removed from the external metadata column collection. Do you want the component to fix these errors automatically?". [I click yes]

3. Open the destination. The column mapping error dialog comes up & I select "Delete invalid column refererence" & click Apply.

What are the object calls that correspond to these actions (#2 & #3)? I cannot figure out what object calls correspond to these actions.

Do I need to manually remove all the columns and re-add them? I tried this also, but had similar problems.

|||Has anyone made any progess with this. I have a similar problem and am running into the same issue.|||

Any progress on this ... anyone at all?

HELP!!!!!

|||

Hi,

Even I had been struggling with the same problem as stated above. Do find my code. May this can be helpfull

ublicClass ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

PublicSub Main()

' Add your code here

Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application()

Dim packagename AsObject = Dts.Connections("DynamicDFTPackage.dtsx").AcquireConnection(Nothing)

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)

Dim th As Microsoft.SqlServer.Dts.Runtime.TaskHost

Dim mp As MainPipe

th = CType(package.Executables("EpiMartDataFlowTask"), Microsoft.SqlServer.Dts.Runtime.TaskHost)

mp = CType(th.InnerObject, MainPipe)

'TODO: remove below statement if not required latter

'package.Variables("SqlQuery").Value = "Select action_key from action"

Dim sourceComponentMetaData As IDTSComponentMetaData90 = mp.ComponentMetaDataCollection("EpiMartSource")

Dim sourceComponentWrapper As CManagedComponentWrapper = sourceComponentMetaData.Instantiate()

'Initialize the component

sourceComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If sourceComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

sourceComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("SourceConnection"))

EndIf

sourceComponentWrapper.SetComponentProperty("AccessMode", 2)

sourceComponentWrapper.SetComponentProperty("SqlCommand", "Select action_key from action")

sourceComponentWrapper.AcquireConnections(vbNull)

sourceComponentWrapper.ReinitializeMetaData()

sourceComponentWrapper.ReleaseConnections()

IfNot sourceComponentMetaData.AreInputColumnsValid Then

sourceComponentMetaData.RemoveInvalidInputColumns()

EndIf

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Get the destination information

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim destinationComponentMetaData As IDTSComponentMetaData90

destinationComponentMetaData = mp.ComponentMetaDataCollection("SandboxDestination")

Dim destinationComponentWrapper As CManagedComponentWrapper = destinationComponentMetaData.Instantiate()

'Initialize the component

destinationComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If destinationComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

destinationComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("DestinationConnection"))

EndIf

'TODO: Need to check if below 3 statements are required and positioned at the right place

'destinationComponentWrapper.AcquireConnections(vbNull)

'destinationComponentWrapper.ReinitializeMetaData()

'destinationComponentWrapper.ReleaseConnections()

' This will remove the existing path

mp.PathCollection.RemoveAll()

' Create the path.

Dim path As IDTSPath90 = mp.PathCollection.New()

path.AttachPathAndPropagateNotifications(sourceComponentMetaData.OutputCollection(0), destinationComponentMetaData.InputCollection(0))

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Select the columns

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ForEach input As IDTSInput90 In sourceComponentMetaData.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

ForEach vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

destinationComponentWrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

EndSub

EndClass

New Guy Question 2: SQL on-the-fly at package runtime?

Here I go again...thanks for your patience...

I suspect that I may also face this issue with every standard SSIS transformation that is called in my data flow...

I'd like to try to avoid writing custom components w/ C# for this. Before I
go down that route I was wondering if anyone had tried this or had a
suggestion.

Let me see if I understand what you're saying:

SSIS is a GREAT product!

Thanks!|||Yeah, this is pretty good understanding. One change - you probably don't need a custom Source component, a standard OLEDB Source/SQL Source or ADO.NET Source should be fine.

Thank you for your patience with the new guy. Great support!

DB|||

We don't have ability to export SSIS package to code, although there is a third party effort to provide this functionality - see http://www.ivolva.com/ssis_code_generator.html

|||OK, the above all makes sense.

Here's my last (and probably unfair because it is a judgement call) question on this thread. Thanks again for your patient responses.

1. Building (and therefore maintaining) 300 individual packages using all the standard SSIS GUI tools, etc. or

Thanks again!|||Doug,

The Integration Services object model is indeed available for you to create, configure and execute a new package line by line, or to load, optionally reconfigure, and execute an existing package.

-Doug
|||Mike and Doug:

The good news is that I think I have a pretty good idea of how to attack the problem. The bad news is that I'll probably be back with more questions!

All the best,

Doug B|||

Hello-

Application app = new Application();

Package m_package = app.LoadPackage(packageName, null);

m_dataFlow = ((TaskHost)m_package.Executables[0]).InnerObject as MainPipe;

// the package variable is linked to the source (Sql command from variable)

m_package.Variables["SqlQueryString"].Value = "select col1 from table1";

// get the data flow source component for the input csv file

IDTSComponentMetaData90 sourceComponentMetaData = m_dataFlow.ComponentMetaDataCollection["InputSql"];

CManagedComponentWrapper sourceComponentWrapper = sourceComponentMetaData.Instantiate();

// -- this causes an error

//sourceComponentWrapper.ReinitializeMetaData();

// input columns are valid, so this does not help

if (!sourceComponentMetaData.AreInputColumnsValid)

sourceComponentMetaData.RemoveInvalidInputColumns();

// -- AcquireConnections fails

//sourceComponentWrapper.AcquireConnections(null);

//sourceComponentWrapper.ReinitializeMetaData();

//sourceComponentWrapper.ReleaseConnections();

DTSValidationStatus validationStatus = sourceComponentWrapper.Validate();

// status is DTSValidationStatus.VS_ISCORRUPT (?)

// there are still 2 output columns coming out of the source

foreach (IDTSOutputColumn90 outputColumn in sourceComponentMetaData.OutputCollection[0].OutputColumnCollection)

Console.WriteLine(outputColumn.Name + ", " + outputColumn.DataType.ToString());

// todo: configure inputs of destination

DTSExecResult result = m_package.Execute();

When I open the package in the designer to try to do this manually, I have to do the following:

1. Change the query string variable manually.

3. Open the destination. The column mapping error dialog comes up & I select "Delete invalid column refererence" & click Apply.

What are the object calls that correspond to these actions (#2 & #3)? I cannot figure out what object calls correspond to these actions.

Do I need to manually remove all the columns and re-add them? I tried this also, but had similar problems.

|||Has anyone made any progess with this. I have a similar problem and am running into the same issue.|||

Any progress on this ... anyone at all?

HELP!!!!!

|||

Hi,

Even I had been struggling with the same problem as stated above. Do find my code. May this can be helpfull

ublic Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

' Add your code here

Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application()

Dim packagename As Object = Dts.Connections("DynamicDFTPackage.dtsx").AcquireConnection(Nothing)

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)

Dim th As Microsoft.SqlServer.Dts.Runtime.TaskHost

Dim mp As MainPipe

th = CType(package.Executables("EpiMartDataFlowTask"), Microsoft.SqlServer.Dts.Runtime.TaskHost)

mp = CType(th.InnerObject, MainPipe)

'TODO: remove below statement if not required latter

'package.Variables("SqlQuery").Value = "Select action_key from action"

Dim sourceComponentMetaData As IDTSComponentMetaData90 = mp.ComponentMetaDataCollection("EpiMartSource")

Dim sourceComponentWrapper As CManagedComponentWrapper = sourceComponentMetaData.Instantiate()

'Initialize the component

sourceComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If sourceComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

sourceComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("SourceConnection"))

End If

sourceComponentWrapper.SetComponentProperty("AccessMode", 2)

sourceComponentWrapper.SetComponentProperty("SqlCommand", "Select action_key from action")

sourceComponentWrapper.AcquireConnections(vbNull)

sourceComponentWrapper.ReinitializeMetaData()

sourceComponentWrapper.ReleaseConnections()

If Not sourceComponentMetaData.AreInputColumnsValid Then

sourceComponentMetaData.RemoveInvalidInputColumns()

End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Get the destination information

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim destinationComponentMetaData As IDTSComponentMetaData90

destinationComponentMetaData = mp.ComponentMetaDataCollection("SandboxDestination")

Dim destinationComponentWrapper As CManagedComponentWrapper = destinationComponentMetaData.Instantiate()

'Initialize the component

destinationComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If destinationComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

destinationComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("DestinationConnection"))

End If

'TODO: Need to check if below 3 statements are required and positioned at the right place

'destinationComponentWrapper.AcquireConnections(vbNull)

'destinationComponentWrapper.ReinitializeMetaData()

'destinationComponentWrapper.ReleaseConnections()

' This will remove the existing path

mp.PathCollection.RemoveAll()

' Create the path.

Dim path As IDTSPath90 = mp.PathCollection.New()

path.AttachPathAndPropagateNotifications(sourceComponentMetaData.OutputCollection(0), destinationComponentMetaData.InputCollection(0))

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Select the columns

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For Each input As IDTSInput90 In sourceComponentMetaData.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

destinationComponentWrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

End Sub

End Class

New Guy Question 2: SQL on-the-fly at package runtime?

Here I go again...thanks for your patience...

I suspect that I may also face this issue with every standard SSIS transformation that is called in my data flow...

I'd like to try to avoid writing custom components w/ C# for this. Before I
go down that route I was wondering if anyone had tried this or had a
suggestion.

Let me see if I understand what you're saying:

SSIS is a GREAT product!

Thanks!|||Yeah, this is pretty good understanding. One change - you probably don't need a custom Source component, a standard OLEDB Source/SQL Source or ADO.NET Source should be fine.

Thank you for your patience with the new guy. Great support!

|||

We don't have ability to export SSIS package to code, although there is a third party effort to provide this functionality - see http://www.ivolva.com/ssis_code_generator.html

|||OK, the above all makes sense.

Here's my last (and probably unfair because it is a judgement call) question on this thread. Thanks again for your patient responses.

1. Building (and therefore maintaining) 300 individual packages using all the standard SSIS GUI tools, etc. or

Thanks again!

|||Doug,

The Integration Services object model is indeed available for you to create, configure and execute a new package line by line, or to load, optionally reconfigure, and execute an existing package.

-Doug|||Mike and Doug:

The good news is that I think I have a pretty good idea of how to attack the problem. The bad news is that I'll probably be back with more questions!

All the best,

Doug B|||

Hello-

Application app = new Application();

Package m_package = app.LoadPackage(packageName, null);

m_dataFlow = ((TaskHost)m_package.Executables[0]).InnerObject as MainPipe;

// the package variable is linked to the source (Sql command from variable)

m_package.Variables["SqlQueryString"].Value = "select col1 from table1";

// get the data flow source component for the input csv file

IDTSComponentMetaData90 sourceComponentMetaData = m_dataFlow.ComponentMetaDataCollection["InputSql"];

CManagedComponentWrapper sourceComponentWrapper = sourceComponentMetaData.Instantiate();

// -- this causes an error

//sourceComponentWrapper.ReinitializeMetaData();

// input columns are valid, so this does not help

if (!sourceComponentMetaData.AreInputColumnsValid)

sourceComponentMetaData.RemoveInvalidInputColumns();

// -- AcquireConnections fails

//sourceComponentWrapper.AcquireConnections(null);

//sourceComponentWrapper.ReinitializeMetaData();

//sourceComponentWrapper.ReleaseConnections();

DTSValidationStatus validationStatus = sourceComponentWrapper.Validate();

// status is DTSValidationStatus.VS_ISCORRUPT (?)

// there are still 2 output columns coming out of the source

foreach (IDTSOutputColumn90 outputColumn in sourceComponentMetaData.OutputCollection[0].OutputColumnCollection)

Console.WriteLine(outputColumn.Name + ", " + outputColumn.DataType.ToString());

// todo: configure inputs of destination

DTSExecResult result = m_package.Execute();

When I open the package in the designer to try to do this manually, I have to do the following:

1. Change the query string variable manually.

3. Open the destination. The column mapping error dialog comes up & I select "Delete invalid column refererence" & click Apply.

What are the object calls that correspond to these actions (#2 & #3)? I cannot figure out what object calls correspond to these actions.

Do I need to manually remove all the columns and re-add them? I tried this also, but had similar problems.

|||Has anyone made any progess with this. I have a similar problem and am running into the same issue.|||

Any progress on this ... anyone at all?

HELP!!!!!

|||

Hi,

Even I had been struggling with the same problem as stated above. Do find my code. May this can be helpfull

ublic Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

' Add your code here

Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application()

Dim packagename As Object = Dts.Connections("DynamicDFTPackage.dtsx").AcquireConnection(Nothing)

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)

Dim th As Microsoft.SqlServer.Dts.Runtime.TaskHost

Dim mp As MainPipe

th = CType(package.Executables("EpiMartDataFlowTask"), Microsoft.SqlServer.Dts.Runtime.TaskHost)

mp = CType(th.InnerObject, MainPipe)

'TODO: remove below statement if not required latter

'package.Variables("SqlQuery").Value = "Select action_key from action"

Dim sourceComponentMetaData As IDTSComponentMetaData90 = mp.ComponentMetaDataCollection("EpiMartSource")

Dim sourceComponentWrapper As CManagedComponentWrapper = sourceComponentMetaData.Instantiate()

'Initialize the component

sourceComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If sourceComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

sourceComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("SourceConnection"))

End If

sourceComponentWrapper.SetComponentProperty("AccessMode", 2)

sourceComponentWrapper.SetComponentProperty("SqlCommand", "Select action_key from action")

sourceComponentWrapper.AcquireConnections(vbNull)

sourceComponentWrapper.ReinitializeMetaData()

sourceComponentWrapper.ReleaseConnections()

If Not sourceComponentMetaData.AreInputColumnsValid Then

sourceComponentMetaData.RemoveInvalidInputColumns()

End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Get the destination information

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim destinationComponentMetaData As IDTSComponentMetaData90

destinationComponentMetaData = mp.ComponentMetaDataCollection("SandboxDestination")

Dim destinationComponentWrapper As CManagedComponentWrapper = destinationComponentMetaData.Instantiate()

'Initialize the component

destinationComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If destinationComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

destinationComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("DestinationConnection"))

End If

'TODO: Need to check if below 3 statements are required and positioned at the right place

'destinationComponentWrapper.AcquireConnections(vbNull)

'destinationComponentWrapper.ReinitializeMetaData()

'destinationComponentWrapper.ReleaseConnections()

' This will remove the existing path

mp.PathCollection.RemoveAll()

' Create the path.

Dim path As IDTSPath90 = mp.PathCollection.New()

path.AttachPathAndPropagateNotifications(sourceComponentMetaData.OutputCollection(0), destinationComponentMetaData.InputCollection(0))

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Select the columns

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For Each input As IDTSInput90 In sourceComponentMetaData.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

destinationComponentWrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

End Sub

End Class

New Guy Question 2: SQL on-the-fly at package runtime?

Here I go again...thanks for your patience...

I suspect that I may also face this issue with every standard SSIS transformation that is called in my data flow...

I'd like to try to avoid writing custom components w/ C# for this. Before I
go down that route I was wondering if anyone had tried this or had a
suggestion.

Let me see if I understand what you're saying:

SSIS is a GREAT product!

Thanks!|||Yeah, this is pretty good understanding. One change - you probably don't need a custom Source component, a standard OLEDB Source/SQL Source or ADO.NET Source should be fine.

Thank you for your patience with the new guy. Great support!

|||

We don't have ability to export SSIS package to code, although there is a third party effort to provide this functionality - see http://www.ivolva.com/ssis_code_generator.html

|||OK, the above all makes sense.

Here's my last (and probably unfair because it is a judgement call) question on this thread. Thanks again for your patient responses.

1. Building (and therefore maintaining) 300 individual packages using all the standard SSIS GUI tools, etc. or

Thanks again!

|||Doug,

The Integration Services object model is indeed available for you to create, configure and execute a new package line by line, or to load, optionally reconfigure, and execute an existing package.

-Doug|||Mike and Doug:

The good news is that I think I have a pretty good idea of how to attack the problem. The bad news is that I'll probably be back with more questions!

All the best,

Doug B|||

Hello-

Application app = new Application();

Package m_package = app.LoadPackage(packageName, null);

m_dataFlow = ((TaskHost)m_package.Executables[0]).InnerObject as MainPipe;

// the package variable is linked to the source (Sql command from variable)

m_package.Variables["SqlQueryString"].Value = "select col1 from table1";

// get the data flow source component for the input csv file

IDTSComponentMetaData90 sourceComponentMetaData = m_dataFlow.ComponentMetaDataCollection["InputSql"];

CManagedComponentWrapper sourceComponentWrapper = sourceComponentMetaData.Instantiate();

// -- this causes an error

//sourceComponentWrapper.ReinitializeMetaData();

// input columns are valid, so this does not help

if (!sourceComponentMetaData.AreInputColumnsValid)

sourceComponentMetaData.RemoveInvalidInputColumns();

// -- AcquireConnections fails

//sourceComponentWrapper.AcquireConnections(null);

//sourceComponentWrapper.ReinitializeMetaData();

//sourceComponentWrapper.ReleaseConnections();

DTSValidationStatus validationStatus = sourceComponentWrapper.Validate();

// status is DTSValidationStatus.VS_ISCORRUPT (?)

// there are still 2 output columns coming out of the source

foreach (IDTSOutputColumn90 outputColumn in sourceComponentMetaData.OutputCollection[0].OutputColumnCollection)

Console.WriteLine(outputColumn.Name + ", " + outputColumn.DataType.ToString());

// todo: configure inputs of destination

DTSExecResult result = m_package.Execute();

When I open the package in the designer to try to do this manually, I have to do the following:

1. Change the query string variable manually.

3. Open the destination. The column mapping error dialog comes up & I select "Delete invalid column refererence" & click Apply.

What are the object calls that correspond to these actions (#2 & #3)? I cannot figure out what object calls correspond to these actions.

Do I need to manually remove all the columns and re-add them? I tried this also, but had similar problems.

|||Has anyone made any progess with this. I have a similar problem and am running into the same issue.|||

Any progress on this ... anyone at all?

HELP!!!!!

|||

Hi,

Even I had been struggling with the same problem as stated above. Do find my code. May this can be helpfull

ublic Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

' Add your code here

Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application()

Dim packagename As Object = Dts.Connections("DynamicDFTPackage.dtsx").AcquireConnection(Nothing)

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)

Dim th As Microsoft.SqlServer.Dts.Runtime.TaskHost

Dim mp As MainPipe

th = CType(package.Executables("EpiMartDataFlowTask"), Microsoft.SqlServer.Dts.Runtime.TaskHost)

mp = CType(th.InnerObject, MainPipe)

'TODO: remove below statement if not required latter

'package.Variables("SqlQuery").Value = "Select action_key from action"

Dim sourceComponentMetaData As IDTSComponentMetaData90 = mp.ComponentMetaDataCollection("EpiMartSource")

Dim sourceComponentWrapper As CManagedComponentWrapper = sourceComponentMetaData.Instantiate()

'Initialize the component

sourceComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If sourceComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

sourceComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("SourceConnection"))

End If

sourceComponentWrapper.SetComponentProperty("AccessMode", 2)

sourceComponentWrapper.SetComponentProperty("SqlCommand", "Select action_key from action")

sourceComponentWrapper.AcquireConnections(vbNull)

sourceComponentWrapper.ReinitializeMetaData()

sourceComponentWrapper.ReleaseConnections()

If Not sourceComponentMetaData.AreInputColumnsValid Then

sourceComponentMetaData.RemoveInvalidInputColumns()

End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Get the destination information

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim destinationComponentMetaData As IDTSComponentMetaData90

destinationComponentMetaData = mp.ComponentMetaDataCollection("SandboxDestination")

Dim destinationComponentWrapper As CManagedComponentWrapper = destinationComponentMetaData.Instantiate()

'Initialize the component

destinationComponentWrapper.ProvideComponentProperties()

'Specify the connection Manager

If destinationComponentMetaData.RuntimeConnectionCollection.Count > 0 Then

destinationComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("DestinationConnection"))

End If

'TODO: Need to check if below 3 statements are required and positioned at the right place

'destinationComponentWrapper.AcquireConnections(vbNull)

'destinationComponentWrapper.ReinitializeMetaData()

'destinationComponentWrapper.ReleaseConnections()

' This will remove the existing path

mp.PathCollection.RemoveAll()

' Create the path.

Dim path As IDTSPath90 = mp.PathCollection.New()

path.AttachPathAndPropagateNotifications(sourceComponentMetaData.OutputCollection(0), destinationComponentMetaData.InputCollection(0))

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Select the columns

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For Each input As IDTSInput90 In sourceComponentMetaData.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

destinationComponentWrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

End Sub

End Class

Saturday, February 25, 2012

new hardware - sqlserver 2000 transfer

new hardware - sqlserver 2000 transfer

new hardware - sqlserver 2000 transfer

new hardware - sqlserver 2000 transfer

New Hardware

New Hardware

New Hardware

New Guy Question: Which came first ... the chicken or the checkpoint file?

New Guy Question: Which came first ... the chicken or the checkpoint file?

New Guy Question 2: SQL on-the-fly at package runtime?

New Guy Question 2: SQL on-the-fly at package runtime?

New Guy Question 2: SQL on-the-fly at package runtime?

New Guy Question 2: SQL on-the-fly at package runtime?

database file locations

Blog Archive

About Me