Microsoft OLE DB Provider for Visual FoxPro 9.0

Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ?

thanks kindly

My basic proccess is as follows: The source foxpro database has hundreds of dbf files the SSIS procress is as follows

1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)

use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tabdatareader source--and away you go

Hi! Yes, you can use the free table option with the VFP OLE DB data provider. A typical connection string for the OLE DB data provider is "Provider=VFPOLEDB.1;Data Source=C:\Temp\" -- Cindy Winegarden cindy@.cindywinegarden.com VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx ""Mr Pro Tools"@.discussions.microsoft.com" <"=?UTF-8?B?TXIgUHJvIFRvb2xz?="@.discussions.microsoft.com> wrote in message news:64e94200-765c-4209-afa1-a2d8bb82af1f@.discussions.microsoft.com... Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ? ...

Microsoft OLE DB Provider for Visual FoxPro 9.0

Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ?

thanks kindly

My basic proccess is as follows: The source foxpro database has hundreds of dbf files the SSIS procress is as follows

1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)

use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tabdatareader source--and away you go

Hi!

Yes, you can use the free table option with the VFP OLE DB data provider. A

typical connection string for the OLE DB data provider is

"Provider=VFPOLEDB.1;Data Source=C:\Temp\"

--

Cindy Winegarden

cindy@.cindywinegarden.com

VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx

VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

""Mr Pro Tools"@.discussions.microsoft.com"

<"=?UTF-8?B?TXIgUHJvIFRvb2xz?="@.discussions.microsoft.com> wrote in message

news:64e94200-765c-4209-afa1-a2d8bb82af1f@.discussions.microsoft.com...

Im quite interested to see if any one is using Microsoft OLE DB Provider for

Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free

table directory like you can with the ODBC driver. ? ...

methods of storing the Connection String in package configuration

hi!

I want to store the value of the Connection String in an Common table exisiting for the whole project and retrieve it for the whole package to Run. I want to know if there is a way of doing this.

I do not want to have a config file which is created trhough the package configuration.

In case of Parent package variable the Value of the variable needs to exist which again needs to execute the SQL Task which requires a connection string.

In case of Environment variabIe.I have very less knowledge about the environment variable . Even if we store it? how can we access it and change it in client place.

In case of SQL server . Can we store in an existing table. what is the configuration filter? what is the vlaue for it.

I do not want to execute any command line.............................

Thanks,

jazz

Well, I have not done this but I think you could simply have a parent package variable that uses a SQL Task to load the connection string for the child packages (obviously you need an initial connection string to load the config in the first place so I am assuming you are looking for a connection string to a second database.).

Once you do that you can use a script task in each of the child packages to explicitly set the Dts.Connections("ConnectionName").ConnectionString property manually based on the parent variable. You can use parent package configurations to map a parent variable to the child package so the designer can see the variable used.

Hope this helps...

|||

Can i execute a stored preocedure to get the connection string from the database while installing the pacakge which can set the value of the parent package variables.

Where i can use the Parent Package Varaible in Package Configuration to be accessed by the Child Packages.

is this ok......

Thanks

metadata update

hi, I try to post a new question about metadata refresh...even if i see other thread that work on a similar problem.

I have a ssis package that import an xml huge file (500 mb); These are the main step:

1) generate a XSD file against xml using xsd.exe utility

2) using xml task, make a diff between the old xsd and the new

3) if there are no difference, I start the data flow task that import xml in sql server; otherwise I stop all the task, edit the data flow task, change the xsd reference in advanced editor and then make many "double clik / OK" on every single flow....

The underling idea is that xml file change because some columns are added but these columns are not interesting for my elaboration, so i can ignore this new column and work without mapping it.

What I'm looking for is a way for make, via SSIS, the "double clik / OK" steps....in other words, to update the metadata.

Could anybody suggest me a way? it's a sort of macro, or keyboard recorder...I'm trying to study xml package configuration; is this a good way ?

another way is to give to the end user the task to update metadata; for making this I need to open the package editor (visual studio..:!) in a more confortable environment....For example, is possible to edit the ssis package in ms access? probably i know the answer...

please, help me!

thank tou in advance

alessandro

Can I check my understanding?

You have an XML source file, correct? And its format (i.e. the xsd that describes it) sometimes changes, correct?

You want a way of changing the SSIS package at execution-time in order to handle this eventuality, correct?

If I've got all this correct then I'm afraid you cannot do this. The metadata of a data-flow cannot be changed after design-time.

-Jamie

|||

Thank you Jamie; you perfectly catch my question, and your answer is similat to other that I have seen on this forum. What I don't really understand is why there is this "limitation" (if i can say).

SSIS are many light year better of DTS (and not only...), but the thing I'm trying to do seem to be so easy....it's only a break in the execution and a sort of macro! At this point I don't exclude to trying a mouse recorder solution....

thank you very much for you answer

Alessandro

Metadata Issues

I am experiencing something really bazaar. The package I created usign SQL2K5 Sept. CTP Builds and functions just fine in one domain. When I deployed to another machine & new domain, we get Metadata errors within dataflow that was modified. We had encountered this earlier but by rebuilding the package it has solved the problem. This time, rebuild is not helping. How can metadata for data flow of a package get corrupted from one environment to another. Also, what's the purpose of building a package when the package is in XML (Text). I don't see any binary code for script tasks.

What exactly is the message? My favourite at present when moving between environments is the encyrption error for passwords (or any sensitive propertry), since the default protection uses the user key, so obviously changing domains changes accounts and therefore the decyption fails. Setting don't save sensitive information and use configurations to apply passwords again helps somewhat.|||Thanks for your reply Darren. I have already cross the hurdle of Package Protection level issue. Current settings are "Don't save sensitive" info. I am curious to see if they modify this default setting in RTM rel.
|||Using the RTM release now available to MSDN subscribers, the default is User Key still.|||Great!!. By the way, what does a Build functionality do for a package when script tasks within either control flow or data flow are set not to compile on save. If I don't build the package, it still works so why build it?
|||

Scripts can be compiled or not. If compiled then they run faster since there is no compilation overhead each time the package is run. I wasn't aware that this was linked to the package build, I assumed it happened within the script editor itself and VSA. Only downside of pre-compiled scripts is they are processor specific, so x86 to x64 deployments for example will fail.

|||You'd be better of ignoring me and just reading Books Online. The package Save induces the compilation.

From ScriptTask.PreCompile

Property Value

true if the VSA script is compiled when the package is saved; otherwise, false. The default value is true.

Remarks

VSA scripts must be compiled before the package runs. The Script task includes the option to pre-compile script into binary code when the package is saved. When script is precompiled, the language engine is not loaded at run time and the package runs more quickly; however, compiled binary files consume significant disk space. Script that is not precompiled is compiled at run time, which slows package execution but consumes less disk space. When storing large packages is not a problem, you should pre-compile your VSA scripts.

|||

DarrenSQLIS wrote:

Only downside of pre-compiled scripts is they are processor specific, so x86 to x64 deployments for example will fail.

Not really. The script is precompiled to MSIL (.NET intemediary language) which is platform-independent. It will be JIT-ed to process-specific code at runtime.

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

Meta Data

Hi,

I want to get meta data information for DTS packages, I don't see anything when I click on Meta Data Services Package. Also, I get an error when I click on Meta Data "An error occurred while trying to access the database information.
The msdb database could not be opened."

Need help.. Thanx

SQLBob

Hi, SQLBob,

I was having the same problem. On the assumption that you are running under Win2003 SP1 look for hot fix 912812 on the operating system. Remove it and Meta Data goes back to working.

Uncle Davy

|||

You should post this question in the Integration Services forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

|||

Steven,

Are you saying that the original question belongs in the Integration area, or the response as a general post?

Sorry I'm new to this forum stuff.

Uncle Davy

|||

I was actually referring to the original post.

I meant to say that DTS was renamed SQL Server Integration Services in SQL Server 2005. The SSIS crew that would know the answer to your DTS questions watch the SSIS forum, so if you post your DTS question there, you will probably get an answer faster.

Meta Data

Hi,

I want to get meta data information for DTS packages, I don't see anything when I click on Meta Data Services Package. Also, I get an error when I click on Meta Data "An error occurred while trying to access the database information.
The msdb database could not be opened."

Need help.. Thanx

SQLBob

Hi, SQLBob,

I was having the same problem. On the assumption that you are running under Win2003 SP1 look for hot fix 912812 on the operating system. Remove it and Meta Data goes back to working.

Uncle Davy

|||

You should post this question in the Integration Services forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

|||

Steven,

Are you saying that the original question belongs in the Integration area, or the response as a general post?

Sorry I'm new to this forum stuff.

Uncle Davy

|||

I was actually referring to the original post.

I meant to say that DTS was renamed SQL Server Integration Services in SQL Server 2005. The SSIS crew that would know the answer to your DTS questions watch the SSIS forum, so if you post your DTS question there, you will probably get an answer faster.