Fliegroups and Primary Keys

I have a question with regads to placement of data files/indexes on
multiple filegroups. Here is the current scenario:

I have a database comprised of two filegroups - PRIMARY and INDEX. The
PRIMARY filegroup is comprised of two files, one residing on the R
drive and another residing on the O drive. The INDEX filegroup
consists of a file on the S drive. The transaction logs reside on the
T drive. The box itself has five individual drive slots, not RAID'ed
or mirrored. The tables are created in the PRIMARY filegroup, the
indexes in the INDEX group.

My question is this: is there any inherent benefit to create the
PRIMARY KEYS in a different filegroup? Currently, they are being
created in the PRIMARY group along with the actual data tables.

Thank you in adavance for any help.

Anthony RobinsonAnthony Robinson (ansonee@.yahoo.com) writes:
> This might seem kind of odd, but I'm going to answer my own question:
> no, you can't put a clustered (primary key) index on a different
> filegroup than the data table.
> The leaf level is the data, so moot point at best. If you wanted to move
> the data to another file or drive, then I could see where placing
> indexes in a different filegroup would get you there.
> Sorry to waste everyone's time...amazing what you can think of when
> you're playing Collapse!!!

Nah, the question is not really that meaningless. Not as it was posted.
To wit, there is nothing in this world which says that clustered index
of a table must be on the primary key. Sometimes the PK is the best
bet for the clustered indexes, but there are also many cases where it
is not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

flatfilesource(s) in a loop

I am trying to design a package to import the data of several .tx files into a table in sql server.

1) I created an execute task that truncates the sql server table i.e. truncate table tblContacts

2)
Placed a forrloop container with enumerator: foreach file enumerator
Folder points to the folder that holds the txt files
file: *.*
filename: fully qualified
variablemapping: User::FileName with Index 0

3)
placed a data flow task inside the forloop
this dataflow task has the following dataflow:
FlatFile Source: connection manager is pointing to one of the txt files
OLE DB Destination to place the txt data into tblContact in the database.

The question:
when the package is run, the tblContact gets populated only from the first txt file, i.e. the one which I placed in the flatfilesource connection manager.
How can I allow several files in the flatfilesource, instead of the one I have now...

Thanks

Firstly, in your For Each Loop, you could change filter from *.* to *.tx (not essential)

You need to make the connectionstring variable (ConnectionString is a property of your FlatFile connection Manager )

Click on your FlatFile Connection Manager (this is at the bottom of the BIDS screen).

Go to the properties of the CM, I mean the properties window that appears on the right of your screen.

Expand the Expressions property collection, and drill through to get the Property Expressions editor.

Choose property ConnectionString, and drill through the expression to get the Expression Builder.

Drag your variable (FileName) and drop it into the Expression box. I assume the FileName variable contains the entire file path and name.

Now it should work fine.

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie

Flat Files having Only Column Names in One file and the Rows in the other

Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)

As far as I know, you can't. Why would you? A quick mapping exercise, name the columns etc, it not that much of a job...

You might do it by merging your files using a DOS command. Google merging text files in dos.|||

Hi Crispin,

Thanks for your reply

Unfortunately, We have hundreds of columns for each of the Flat File Sources among 20 in each Package. Naming them will obviously take ages.

Thanks

Subhash Subramanyam

|||I've had the same situation.

If you specify the file with the column names as the source, it'll do it for you. After that, you give it the files with the actual data in. Once columns are named, you do not need to do anything else with it. Unless the column order changes - in which case, you have a problem...

Otherwise, look at merging the files using DOS.

Another option you could try is to use Data Defractor for text files. Might be overkill for text but works non the less...

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql

Flat File to Relational Schema

Hello-

I am a complete newbie with SSIS. I started working with Flat Files today and have made some progress.

I have a task that requires me to pull out data from a | delimeted list with 160+ columns in a row. I am working with Movie and Entertainment data. So each movie has a number of actors associated with it.
For example:
MOVIEID|Zoolander|...|...|...|...|..................|Owen Wilson|Ben Stiller|Will Ferrel|...|...|...............|

I would like to take all the actors out of the middle of this long line of columns and place them in an Actors table with the movieID. However, when I look at Flat File Source, all I see is my X number of columns in the one row for all the actors (actor1-20).

Is there a way to make a new flat file that references the other flat file and can split up a certain amount of columns by rows inside of the middle of an existing row?

I hope that makes sence. Basically I would like to make a relational database schema out of ONE Flat File.

Thanks for your consideration,
ScottA good way to handle a problem like this would be to create a Flat File Reader. Input the records into a Multicast. Select the columns that you want and insert them into a Flat File destination. Alternatively, you can insert them directly into a table depending on your desired methodology.

Let me know if you need more help. I'll be happy to set up a sample job for you explaining this functionality.

Wes D|||

Check out the Unpivot Transformation. http://msdn2.microsoft.com/en-us/library/ms141723.aspx

Donald

|||Donald,

Thanks for the tip! That's what I am looking for. Although I need it to go to a different table, rather than normalizing it inside the same DataSet.

Is there anyway to branch off into a different DataSet?

And if so, is there anyway to give the primary key of the original DataRow to it as a join condition?|||

Have a look at the Multicast component.

Donald

Flat File into multiple SQL 2005 Tables.

Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes,

we have 16 .txt files, some with over 350 columns.

That info from each individual file needs importing to multiple sql tables.

need to look at sql table1 does record exist? if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns...

then look at sql table2 and repeat for next n columns....

So I was wondering is it going to be better to write this as a dtsx package? if so can you point me to an example

or should I just write the code as part of a code behind page that scrapes the info and does a standard update/insert procedure?

Any help would be welcome.

thanks

Sounds easy with a Flat File source, a lookup transformation (to see if you need to update or insert), OLE DB Destination and an OLE DB Command transformation...|||

Cheer Phil,

Do you know of any page that has an example to follow?

|||

Jamie has an example of hw to use the lookup transform to chek if the row exist:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Flat File Data Source with variable number of delimited columns

I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).

Is it possible to use a single flat file data source that will work with all of the different width files?
No.

The only thing you can do is read in each line as one big record and then maybe use substrings or something to pick apart the files.

Flat file CSV problem

Hi all,

I hope someone can help with a problem i'm having.
I want to process a large number of CSV files into various tables in an SQL database.
The CSV file contains entries on a row by row basis relating to specific events (indicated by an eventID in column 0).
Eventually i think i want to be using a conditional split to process each row seperately depending on the eventID but before i get this far i am having a problem with the source data.

Each event can have varying amounts of columns filled in in the CSV file. And each CSV file can have multiple event types in it.

The flat file manager seems to merge a number of different rows into one within the preview pane. It seems to ignore the end of row delimiter of CrLf.

Can anyone please help me to sort this so that each row is on its own and will allow me to pass the structured data set to a conditional split task?

Many thanks in advance,

GrantOk,

I seem to have managed to set all the data for each row into one component and have a script to extract the EventID to one output column and the remaining parameters to a second column. I need to perform a check on the EventID by passing it and another variable into a stored procedure. How do i go about doing this in the data flow section? is it possible or do i have to look at using the control flow section for this?

Thanks,

Grant|||

Grant,

What kind of check does that procedure performed? Keep in mind that any operation you define in the dataflow will be performed in row by row basis; so a call to a procedure in a data flow will be executed as many times as rows you have in the file. Since you already succeed on separating the eventID from the rest I would try to use a conditional split transformation based on the EventID value and then to performed specific transformations/checks to every data pipeline.

Rafael Salas

|||Hi Rafael,

Thanks for the response. The stored procedure i was talking about returns a value based on the event ID. Having thought about what you said i can set this value manually after the conditional split has been carried out and i know what the event ID is. Does that sound more plausible?

I am trying to rewrite a windows application that processes these files, initially this used a stored procedure to process each row of data. The main stored procedure calls other sub procedures within it. The problem was that the stored procedure itself was getting very unwieldly with a large number of if and nested if statement which meant that the addition of new events was complex and time consuming. Using the SSIS package a believe i can make this a much easier process to manage.

Is it still possible to call a stored procedure in the dataflow task once the conditional split has been performed? i realise that there may be other stored procedures required once i have my data row?

I have just attempted to run some SQL code on a per row basis and have discovered that i cannot seem to user variables or parameters in the OLE DB Command task. How would i go about either returning a value dependant on if the current eventID exists in another table in the database or indeed; how to insert data into a specific table if it doesn't exist.
After i do that then i can get on to processing the event data to the table where this is stored. I hope that makes sense.

Many thanks,

Grant|||

Wow a lot of questions!

First to all let me clarify that there is nothing wrong with having OLE DB Command tasks in your Dataflow; it is just that as personal pratice I try to use bulk operations against the DB when possible.

If what you are trying to use thr stored procedures for is to check if a row exists in a table; you can use the lookup transformation in your dataflow; then use the error output as your insert pipeline and the output as the 'already exists' kind of pipeline (or just not use it if you want to ignore them). The lookup can be also returned other columns from your lookup table if that is what you need

Rafael Salas

|||I tend to ask a lot of them yes :)

I looked into the lookup transformation which i can see how i would use the error output etc. I was then using an OLEDB destinbation to insert rows to the table. The problem with the OLEDB destination is that i cannot loop back to the lookup. With an oledb command would i script the insert command and then be able to loop back to the lookup transformation.
E.g.

Should this be ok to enter as a SQL Command:

if (select count(*) from SerialPartRev Where SerialPartRev.SerialNo = ? and SerialPartRev.PartNo = ?) = 0
begin
Insert into SerialPartRev (SerialNo, PartNo, RevisionNo)
Values(?,?,?)
end

The reason i want to do this is so that the SerialPartRev table is update automatically. I require to do the same thing with a Username table. The big problem is how to check again for the entry before processing further. As i have found looping back to the lookup isn't possible due to it only accepting one input path.

I'm quite happy to accept that i am doing this wrong and that maybe someone could suggest another process for implementing this.

Cheers,

Grant|||Rafael,

I have been reading you're previous post again and whilst i understand what you are saying about using the output and error output in the pipeline depending on if the row exists but one question still remains. Firstly i have to say that there will be a couple of instances where i have to check if data from the columns in the flat file exists in SQL tables. Regardless of if i have to insert the row manually or if it already exists after both of these operations the output still has to go to the one conditional split task. Effectively it splits the path in two and then rejoins after carrying out the necessary functionality. Is this possible without the use of scripting? I believe i can achieve this via a script although it will mean exposing a password in plain taxt so that the database connection string will work properly.

Thank you,

Grant|||Actually, forget it. I have managed to call the stored procedures form the OLE DB commands. I have no idea what i was doing wrong previously but it seems to be working now.

Thanks for you're help on this matter it was most appreciated. I'm sure i'll have more questions in time.

Cheers,

Grant

Flat File Connection with quote encapsulated fields

I’m trying to evaluate SSIS for managing the import of files that have quote encapsulated fields, but not all fields are encapsulated. The fields are encapsulated to allow for embedded commas.

For example:

AAA,"BBB",CCC,"DDD","EEE","FFF",GGG,"HHHH",IIII

AAA,"BBB",CCC,"DDD","EE,E","FFF",,"HH,H",

When importing this file I have to alter the delimiter from column AAA to deal with the quote from column BBB like this:

{,}"

And BBB would look like

“{,}

Now I can go through each column for our files doing this by hand, but I wonder if there is some way to note that some fields are encapsulated. Thus it would be more wizard like. Or the ability to alter the Advanced fields while on the Preview or Columns to reduce the jumping across pages.

Also is the gramma for delimiters described anywhere?

Simeon

You should use the "Text Qualifier" property of the Flat File Connection Manager. In this case, you would specify " to be the Text Qualifier. Best part is if some columns are qualified, and some are not qualified, it will still handle them both.

Let me know if you have any problems using the Flat File Connection Manager.

thanks
Ranjeeta