is this possible? ...not an SQL master

Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
RickAn example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||HI,

try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)

and use the same formula for AheadPM column

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.

many thanks,
Rick Sperling

Originally posted by dmmac
An example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

is this possible in SSIS?

I got a OLE DB source pointing to 1 table

and 1 flat file destination.

currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.

anyone can help me?

Unfortunately not because the metadata i.e. the columns involved in the transform need to be the same. So you can't have 1 data flow in a loop that is reconfigured for different tables and destinations.

If the source is SQL you could use BCP to produce the flat files but this then really isn't SSIS. Although you could run the bcp from within SSIS.

|||

i was wondering if there can be a conditional loop in between the OLE DB source and the flat file.....

like if the table name is A then go to File destination A

if table name is B then go to File destination B etc....

hope someone understands what i m saying.

|||

You can direct rows to 1 of multiple destinations based on characteristics of the row. This is done usnig the Conditional Split transform. You should look into that and see whether it will do what you require.

I don't know why you used the word "loop". There is no notion of looping in a data-flow.

-Jamie

|||

no tats not what i want....

I trying to backup many tables into many text files using 1 SSIS package project. Is that possible?

trying to reduce the no. of SIS packages file i need to maintain.

|||

Why not have 1 package containing many data-flows?

-Jamie

|||

I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.


anyone can help me by posting a screenshot of how this can be done in SSIS....the data flows diagram i m not very sure...cos i just started using SSIS in SQL Server 2005.

any guides to SSIS will also be appreciated. Thanks!

i tried using 1 ole db source + file A

and 2 ole db source + file B separated in the Diagram but when i execute it , it doesnt run :(

|||

Ah ok. You can't do this, as Simon explained earlier!

-Jamie

|||

ok Jamie, lets look at it the other manner

I got a table on database server -> export to text file -> import to my local database....

I m doing this task several times...

can this be run consecutively in a data flow diagram? i tried but its not working.....cos of concurrency issues i guess.

can some expert enlighten me?

|||

You can run them all in the same data-flow (in which case there will be as many source and destination adapters as there are tables you are moving data from) or concurrently in seperate data-flows.

-Jamie

|||Be aware that even if you have 20 sources and 20 destinations they may not all run at once. SSIS has a process that determines the threads to use and the amount of concurrency. If running on a 1 proc machine you will get very different results that running on a 4 way machine.|||hmm so simon...what do u recommend?|||

Brohans,

In this scenario its really hard to make a recommendation. These are your options where you have N tables that you have to move data from:

1) Have 1 data-flow that contains N source adapters going to N destination adapters

2) Have N data-flows, 1 for each table. Run them all in the same package

3) Have N packages

Its generally accepted that option #1 will be quicker when N is fairly small (e.g. 4 or 5 tables. I wouldn't like to speculate as to what will be quicker when (e.g.) N>25, I would guess at option #2 but that's only a guess. Option #3 probably isn't a goer. The amount of hardware will come into play here whatever you do. Perhaps this will help: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx

To be honest, the only person that can answer this is yourself. Test and measure,Test and measure, Test and measure...

And let us know how it goes cos this could be really interesting.

-Jamie

|||

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

SSIS is really a pain ...arghhhhhhh

|||

brohans wrote:

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

If you want to execute things in a defined order then put everything in seperate data-flows and make sure they execute in that defined order using precedence constaints. There is no need to have more than 1 package.

brohans wrote:

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

The file path can be made dynamic through the use of expressions. For example, put an expression on the connection string of the flat file connection manager.

brohans wrote:

SSIS is really a pain ...arghhhhhhh

Why is it a pain? So far you haven't got a requirement that cannot be achieved.

Is it SSIS that is a pain or the fact that you're still learning how to use it? Its a hugely powerful tool but because of that there is a learning curve - I am confident you'll like it when you know how to fully leverage it. Like any technology it takes time to learn it properly.

-Jamie

Is there any way of Migrating reports from one server to another programmatically thru VB.NET?

Hi,
I wanna know, is there any way of transfering/migrating reports from source server to destination server along with model references programmatically using VB.NET?
If there is a way, can anyone please give me a VB.NET Sample code to achieve my goal?

I will be grateful if anyone help me in this regard!!

Thanks in Advance
Shaun

This isn't exactly what you asked for, but it'll get you started. There are two samples -- One to publish the report and fix up it's data source, and another to grab the model definition and publish it to a server.

In the first sample, you'll need to change the part where the report defintion is loaded from a file to use a call to GetReportDefinition() (http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportdefinition.aspx)

Later on in the first sample, the the Data Source reference should point to your model instead of the data source in the code itself.

In the second sample, the code reads a model and then republishes it to the *same* server. Create another instance of the web reference to the web service which points to the "other" machine, and use IT to publish to the new destination server.

private void button1_Click(object sender, EventArgs e)
{

SampleApp.localhost.ReportingService2005 rs = new SampleApp.localhost.ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

try
{
byte[] reportDefinition;
Warning[] warnings;

FileStream stream = File.OpenRead(@."C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\Company Sales.rdl");
reportDefinition = new Byte[stream.Length];
stream.Read(reportDefinition, 0, (int)stream.Length);
stream.Close();

// Create a report which utilizes a pre-existing shared data source

// Optionally, set properties on the report by replacing the null in the last parameter of
// CreateReport with an array of properties

localhost.Property[] reportProps = new Property;
reportProps[0] = new Property();
reportProps[0].Name = "PageHeight";
reportProps[0].Value = "279.4";
reportProps[1] = new Property();
reportProps[1].Name = "PageWidth";
reportProps[1].Value = "215.9";
reportProps[2] = new Property();
reportProps[2].Name = "TopMargin";
reportProps[2].Value = "12.7";
reportProps[3] = new Property();
reportProps[3].Name = "BottomMargin";
reportProps[3].Value = "12.7";
reportProps[4] = new Property();
reportProps[4].Name = "LeftMargin";
reportProps[4].Value = "12.7";
reportProps[5] = new Property();
reportProps[5].Name = "RightMargin";
reportProps[5].Value = "12.7";

warnings = rs.CreateReport("Company Sales", "/AdventureWorks Sample Reports", true, reportDefinition, reportProps);

if (warnings!=null)
{
Console.WriteLine("Warnings occured when creating report");
foreach (Warning warning in warnings)
Console.WriteLine("\t" + warning.Code + ": " + warning.Message);
}

try
{
// Report was created, now fix up datasource reference to make sure report points at correct ds
DataSourceReference reference = new DataSourceReference();
DataSource[] dsarray = new DataSource[1];
DataSource ds = new DataSource();
reference.Reference = "/Data Sources/AdventureWorks";
ds.Item = (DataSourceReference)reference;
ds.Name = "AdventureWorks";
dsarray[0] = ds;
rs.SetItemDataSources("/AdventureWorks Sample Reports/Company Sales", dsarray);
}
catch (SoapException ex)
{
Console.WriteLine("Error Code: " + ex.Detail["ErrorCode"].InnerXml);
Console.WriteLine("Message: " + ex.Detail["Message"].InnerXml);
}

}

....and here's how to get the model:

private void button2_Click(object sender, EventArgs e)
{
localhost.ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//To hold SMDL as byte array
Byte[] modelDef;

//Work on pre-published Model

//... if it's not already there. See CreateDataSource.cs for more inforamtion

//Get definition of model, save in byte array
modelDef = rs.GetModelDefinition("/Models/Adventure Works");

//Publish Model

try
{
rs.CreateModel("CopyOfAdventureWorksModel", "/Models", modelDef, null);
}

catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}
}

|||Russell, thank you very much for your patience and response!!
I will get back to you at the earliest, coz I need to give this code to my client!!!
Will reply to you after he gets back to me!!

Is there any way of keeping all of a solution in one script project?

We do not have a source code control plug in for Visual Studio 2005 and I am trying to establish an efficient way of controlling versions of a solution I have created in BIDS, which includes SSAS objects plus an SSIS package.

As there are so many individual files for the dimensions, cubes, database, etc., it does not seem feasible to have them all checked in to our source control application and I was thinking of creating an Analysis Services Scripts project via SQL Server Mgt Studio where I could simply script out the SSAS database and the package and use these via the Deployment Wizard to deploy.

However -

a) according to BOL, an Analysis Services Scripts project has Connections, Scripts and Miscellaneous folders - when I create one, it has Queries instead of Scripts and when I script out the database I cannot see the xmla file in the project. Am I getting hold of the wrong end of the stick here?!

b) An SSIS package cannoy, I think, be scripted as such; when you deploy it creates the dtsx file and the SSIS DeploymentManifest file that installs the package, so is the best idea to keep these two files in source control?

Any advice gratefully received! Thank you

Rachel

I am a little bit surprised that your source control system has a limitation on number of files that affects you. In typical project you might have couple of dozen files, let's say up to a hundrend in a big solution - but modern source control systems can handle hundrends of thousands of files. Is there something here that I miss ?|||

In Visual Source Safe, part of Visual Studio, you do not check in and out individual files, only objects like dimensions and cubes.

Are you trying to build your own version control system? Visual Source Safe should be enough for projects with 1-5 developers?

Regards

Thomas Ivarsson

|||

Sorry, I was not making myself clear; we use MKS Source Integrity (a rather old version that does not integrate with VS) and there is no limit to the number of files it can deal with, but when I started checking in all the dim, cube, dsv, database, ds, dwproj, sln files etc it became apparent that it would be difficult to manage in that you would need to know which files would be affected by a change before you made it - I ended up checking everything out every time (a waste of time and effort!).

So I thought one script file for the database plus whatever the relevant files for the SSIS package are might be a better way forward, but would appreciate advice.

|||If only we had VSS! (see my response to Mosha above)|||So...any further advice from anyone?

is there any way AMO app can get information from data source without reprompting user for user

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

is there any way AMO app can get information from data source without reprompting user for u

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

is there any open source db tools for MSSQL ?

hi, good day, is there any good open source database tools available for mssql ? thank youThere is a good designer tool called DBDesigner4,you can check this out link (http://fabforce.net/downloads.php)