create NorthWind Sample Database

hi
i have the instnwnd.sql with the structure of NorthWind Sample Database. How
can i import it?
The original database was deleted.
thx
Use OSQL and specify the file name using the /i parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database. How
> can i import it?
> The original database was deleted.
> thx
>
|||Use osql or open the file in Query analyzer (isqlw) and run the .sql file
using F5.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database.
How
> can i import it?
> The original database was deleted.
> thx
>
|||Alternately, there is a free (for personal use) tool at our site (MSDE
Manager) that you can use for this and other management options. Hope you
find it useful.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:%23LMH1nBOEHA.268@.TK2MSFTNGP11.phx.gbl...
> Use osql or open the file in Query analyzer (isqlw) and run the .sql file
> using F5.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
> news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> How
>

Create Dynamic table from CSV

Hi,

I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:

Time, Probe1, Probe2, Probe3, Probe4

Whereas the next month I can have something like this

Time, Probe2, Probe3, Probe1, Probe4

The "Time" column will always be on the left side, but the probes may vary in their placement.

I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.

Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?

Any help appreciated!

I think the best approach would be to configure your flat file connection manager to read each row as a single column. This column would then be input to a script transformation component which would parse the line into columns and, based on the column order in the header row, put the values into the appropriate output columns.
|||Here is a code sample for a transformation script to illustrate the idea. The Row.Line is the input column that contains an entire line of a text file. This line is taken and split into an array by the comma delimiter. The first time we see the line, it is a header with column names. A boolean flag is used to detect the header condition and we save the column names in a variable. For every subsequent row, we iterate through the column names and match them to an output column that has been defined for the script, assigning the value parsed from the file into the correct column. Using this method, the order of the columns in the text file is irrelevant.

Code Snippet

Dim Line As String = Row.Line
Dim LineTokens As String() = Line.Split(","c)

If IsHeader Then
HeaderColumns = LineTokens
IsHeader = False
Else
' match input columns to output columns
With Output0Buffer
.AddRow()

For i As Integer = 0 To HeaderColumns.Length - 1
Select Case HeaderColumns(i)
Case "Time"
.Time = LineTokens(i)
Case "Probe1"
.Probe1 = LineTokens(i)
Case "Probe2"
.Probe2 = LineTokens(i)
Case "Probe3"
.Probe3 = LineTokens(i)
Case "Probe4"
.Probe4 = LineTokens(i)
End Select
Next

End With
End If

|||

Thanks a lot for the help...

Looks like it'll solve my problem

Thanks!

|||Just make sure you revist the thread and marks it as answered |||

Ok, Since i'm not very proficient with SSIS...

here's what I have going right now.

Under Data Flow, I have a Flat File Source which uses my Flat File connection manager...there's only 1 column, just like you said.

Then I have it going to the transformation script component.

Under Input Columns, I only have Column 0, and its output alias is the same.

Under Inputs & Outputs, I didn't change anything...I didn't add any output columns since I want this to be done dynamically...

Then for the script,

I put in the script you gave me, and this is where i'm having problems...

I renamed Row.Line to Row.Column0,

but there's still a few undefined...such as IsHeader, HeaderColumns, and OutputBuffer.

Where do I define these?!

Thanks!

|||IsHeader and HeaderColumns are global variables that I didn't show in the example. They are shown below would go right under "Inherits UserComponents" in the script.

Code Snippet

Dim IsHeader As Boolean = True
Dim HeaderColumns() As String

Output0Buffer (don't forget the "0") is built for you to the definition that you supply on "Inputs and Outputs". This object will have the same name as your output (without spaces), but with "Buffer" appended to it. You should have one now, but likely just forgot to add the "0" (the default output is "Output 0" ).

You can't leave your output columns blank as they can't be created dynamically. You said your columns were constant, but could appear in different orders in the source. The script will worry about the order of the input columns, but you still need to define the constant output columns.

|||

Beauty.

It's working now.

Thanks a lot for the help.

Create Dimension Table from Fact Table!!

I have picked an exmple from this forum, to help me explain my current problem...

"I'm looking for a solution to import data from a flat file into an normalized data modell. To explain it a little simpler think about to following:

The Data Souce is a CSV-File with FirstName, LastName and Category. Sample data could be

Dirk; Bauer; sailing
Peter; Bauer; fishing
Marc; Bauer; reading

In my data modell I have defined the 2 tables "Person" and "Category":

Table "Person"
-
[PersonID] [int] IDENTITY(1,1) NOT NULL
[CategoryID] [int] NOT NULL
[FirstName] [nvarchar](50)
[LastName] [nvarchar](50)

Table "Category"
-
[CategoryID] [int] IDENTITY(1,1) NOT NULL
[CategoryName] [nvarchar](50)

Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".

I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.

I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...

Thanks,
Dirk"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74752&SiteID=1

Could someone help me creating the dimension table?

Thanks!!

It's very common to derive dimensions from source data - that is the purpose of a data warehouse after all.

What you need to do is perform the dimension lookup with your source data. For values not found (New dimensions), you can run the records down the error output into a Derived Column derivation to create your new dimension table complete with keys, then merge them back into the flow to update the dimension tables. Your methodology will determine how this is done.

|||How about seperate packages for dimensions and facts. The dimensions get built first from the source data so there will always be a lookup match when building the fact|||That will work as well. Always transform dimensions before facts.

Wes|||

wesd wrote:

That will work as well. Always transform dimensions before facts.

Wes

That's what I would do.

|||Please mark this thread as answered, the op has the information he needs. Email me if you need more information.|||

I will Try!!

Thanks!!!

Create Destination Table Dynamically in a program

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

Hi Pavan,

If you have the schema and the name, can't you execute a CREATE TABLE T-Sql statement?

Andy

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

The most I've done is supply a name dynamically and keep the mappings constant. I dont think you can do mappings dynamically.

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

You can usually do this with a "select ... into destination_table from source_table..." statement. That SQL statement will create the table using the results of the select statement.

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