Dynamic File location for DTS transfer

Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||

netflash99 wrote:

Never heard of SQLDMO, what is that?

SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.

http://www.sqldts.com

Dynamic Excel Sources

Please help....

I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.

My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.

I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.

Thanks,
Dave

You could use a Foreach Enumerator to loop through each file with the same structure and load it to the destination table, by placing the data flow within the loop container.

-Doug

|||

How would I go about getting the file name to change appropriately with each file. I have the foreach creating a variable called FileName. I have a script task picking off the information in the filename (format: <vendor #>_<start date>_<stop date>.xls). I then put vendor#, start date, and stop date into package variables. I guess my main concern is not knowing if I'm using expressions right. I mapped the ExcelFilePath and ServerName to user::FileName. With the file changing, do I have to set anything else to keep errors from occurring?

Thanks,

Dave

|||

Dave,

You shouldn't need to worry about the ServerName property for Excel. As for ExcelFilePath, I would want to make sure that the ConnectionString property was updated immediately and automatically when I provide a new value (outside the UI) for ExcelFilePath. Otherwise you could use an expression on ConnectionString, instead of simply mapping ExcelFilePath to User::FileName.

Updating the ConnectionString for Excel by using an expression has been discussed here previously. The expression shown in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1 should work for you when you insert your FileName variable at the proper place.

-Doug

Dynamic database creation

Hi All,

I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed

However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.

I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).

Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.

Thanks & regards,

Paul

Hi Paul,

As the simplest solution you can run the entire DB creation script through code, for e.g.:

using (SqlConnection cn = new SqlConnection("connectionString from config file"))
{
StringBuilder sql = new StringBuilder();
sql.Append(" use master; create database " + txtDatabase.Text);

SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cn.Open();
cmd.ExecuteNonQuery();
}

Hope this helps,

Vivek

|||

Hi Vivek,

Thanks for your reply. I haven't seen / used sql.Append before, so that definitely could be a way around the problem. I had hoped that there would be a way of running the .sql script, so that if I make any changes to the database layout then I can simply issue a new script, but it wouldm't be the end of the world if I have to issue a new vb file instead. I'll let you know how I get on over the next few days!

Thanks again,

Paul

Dynamic Columns in Report

I have two tables with structure something like
User Attributes
AttributeID
AttributeName
Description
IsRequired
ChoiceArray
OptionType
DefaultValue
MaxVal
MinVal
Mask
DecimalPlaces
isMultiLine
DateFormat
CompanyID
UDFTypeID
EntityID
isVisible
Values of the tables can be
User notes
Published comments
Reject reasons
Whereas transaction Entity can be some other entity like purchase order, etc
and this information will saved for particular record as per customersâ'
needs. There can be thousand of customer with their own fields ranging from
0-20. The values of these user defined fields are stored in another tables
with relationship to user defined field and business transactionID. For
example for purchase orders values tables would be
UDFValueID
POID
Value
AttributeID
Values of the tales can be
1 1 Some Values 1
2 1 Some Values 2
3 1 Some Values 3
I need to develop a report that could be able to display the information in
format like
User notes Published comments Reject reasons etc. etc.
Some Values some value some reasons
I know that using pivot I can show the rows in columns, but I really donâ't
know how I can do that same with reporting services.
My question is how I can create/Add columns in reports on runtime or
dynamically. As per above requirement.A matrix table will allow dynamic columns.
"nettellect" wrote:
> I have two tables with structure something like
>
> User Attributes
> AttributeID
> AttributeName
> Description
> IsRequired
> ChoiceArray
> OptionType
> DefaultValue
> MaxVal
> MinVal
> Mask
> DecimalPlaces
> isMultiLine
> DateFormat
> CompanyID
> UDFTypeID
> EntityID
> isVisible
> Values of the tables can be
> User notes
> Published comments
> Reject reasons
> Whereas transaction Entity can be some other entity like purchase order, etc
> and this information will saved for particular record as per customersâ'
> needs. There can be thousand of customer with their own fields ranging from
> 0-20. The values of these user defined fields are stored in another tables
> with relationship to user defined field and business transactionID. For
> example for purchase orders values tables would be
> UDFValueID
> POID
> Value
> AttributeID
> Values of the tales can be
> 1 1 Some Values 1
> 2 1 Some Values 2
> 3 1 Some Values 3
> I need to develop a report that could be able to display the information in
> format like
> User notes Published comments Reject reasons etc. etc.
> Some Values some value some reasons
> I know that using pivot I can show the rows in columns, but I really donâ't
> know how I can do that same with reporting services.
> My question is how I can create/Add columns in reports on runtime or
> dynamically. As per above requirement.
>|||is there any sample or wlkthrough that could help me in developing this
"Jimbo" wrote:
> A matrix table will allow dynamic columns.
>
>
> "nettellect" wrote:
> > I have two tables with structure something like
> >
> >
> > User Attributes
> >
> > AttributeID
> > AttributeName
> > Description
> > IsRequired
> > ChoiceArray
> > OptionType
> > DefaultValue
> > MaxVal
> > MinVal
> > Mask
> > DecimalPlaces
> > isMultiLine
> > DateFormat
> > CompanyID
> > UDFTypeID
> > EntityID
> > isVisible
> >
> > Values of the tables can be
> >
> > User notes
> > Published comments
> > Reject reasons
> >
> > Whereas transaction Entity can be some other entity like purchase order, etc
> > and this information will saved for particular record as per customersâ'
> > needs. There can be thousand of customer with their own fields ranging from
> > 0-20. The values of these user defined fields are stored in another tables
> > with relationship to user defined field and business transactionID. For
> > example for purchase orders values tables would be
> >
> > UDFValueID
> > POID
> > Value
> > AttributeID
> >
> > Values of the tales can be
> > 1 1 Some Values 1
> > 2 1 Some Values 2
> > 3 1 Some Values 3
> >
> > I need to develop a report that could be able to display the information in
> > format like
> >
> > User notes Published comments Reject reasons etc. etc.
> > Some Values some value some reasons
> >
> > I know that using pivot I can show the rows in columns, but I really donâ't
> > know how I can do that same with reporting services.
> >
> > My question is how I can create/Add columns in reports on runtime or
> > dynamically. As per above requirement.
> >|||Design a dataset with a structure like:
RowValue
ColumnValue
CellValue
Create the matrix table and place those fields in the appropraite sections.
The matrix table will create columns to the number of unique column values
The matrix table will create rows to the number of unique row values
The intersection of row/column will be populated with your cell value
(usually a sum(cellvalue) )
If this doesn't help ask the google god.
"nettellect" wrote:
> is there any sample or wlkthrough that could help me in developing this
> "Jimbo" wrote:
> > A matrix table will allow dynamic columns.
> >
> >
> >
> >
> >
> > "nettellect" wrote:
> >
> > > I have two tables with structure something like
> > >
> > >
> > > User Attributes
> > >
> > > AttributeID
> > > AttributeName
> > > Description
> > > IsRequired
> > > ChoiceArray
> > > OptionType
> > > DefaultValue
> > > MaxVal
> > > MinVal
> > > Mask
> > > DecimalPlaces
> > > isMultiLine
> > > DateFormat
> > > CompanyID
> > > UDFTypeID
> > > EntityID
> > > isVisible
> > >
> > > Values of the tables can be
> > >
> > > User notes
> > > Published comments
> > > Reject reasons
> > >
> > > Whereas transaction Entity can be some other entity like purchase order, etc
> > > and this information will saved for particular record as per customersâ'
> > > needs. There can be thousand of customer with their own fields ranging from
> > > 0-20. The values of these user defined fields are stored in another tables
> > > with relationship to user defined field and business transactionID. For
> > > example for purchase orders values tables would be
> > >
> > > UDFValueID
> > > POID
> > > Value
> > > AttributeID
> > >
> > > Values of the tales can be
> > > 1 1 Some Values 1
> > > 2 1 Some Values 2
> > > 3 1 Some Values 3
> > >
> > > I need to develop a report that could be able to display the information in
> > > format like
> > >
> > > User notes Published comments Reject reasons etc. etc.
> > > Some Values some value some reasons
> > >
> > > I know that using pivot I can show the rows in columns, but I really donâ't
> > > know how I can do that same with reporting services.
> > >
> > > My question is how I can create/Add columns in reports on runtime or
> > > dynamically. As per above requirement.
> > >