Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

sql

Flat File with Nested Data

I am looking to import data into SQL Server 2005 using SSIS. I want to take data that is contained in a flat file and place it into the various appropriate tables in my system. The flat file contains nested data. For example...

Bob,Smith,555-5555,123~3.33|245~1.99,Active

So I want to build a package that brings in the records as follows

Client Table: First Name, Last Name, Phone, and Status (Bob, Smith, 555-5555, Active)

Order Table: OrderID, Amount (ID 123 @. $3.33 and another row ID 245 @. $1.99). If possible I would also like to tie the orders to the client record that was inserted.

My first question is if SSIS supports nested fields as in my example. Can it break a file by commas, then within a field by other delimiters? If so how do I do this, and if not what is the recommend way to accomplish this sort of task.

My second quesiton is if it can do that, can it tie the Client and Order data on the fly?

Thanks.

Looks like you could get what you want using multicasts, conditional splits, merges/unions, etc...

That's terribly messy source data, and if you can normalize it before bringing it into SSIS, you should do so.|||

The main problem I see you having with correllating a client with an order is not having an ID available. At least you won't have a Client ID until after you insert the client. This limits your ability to insert both within the same Data Flow Task.

One way to solve this is to generate an ID for the client before inserting.

If you had an ID you could then just use the Multicast Shape to send each row to multiple destinations. One destination would be the Client table, and the other would be the Order table. Your Data Flow would then look something like this.

Flat File Source --> Derived Column to add ID --> Multicast --> Client table, Order table

|||

I do not have control of the data feed. It is coming from a provider and therefore is out of my control. I also simplified the data greatly as there are multiple nested fields and nested within nested. So my initial dilema is just how to parse the data. Presently I have looked at two options, but both seem to be non-optimal and trying to figure out a better way to do this.

1. I did this with BizTalk. The problem is BizTalk is horrible on the performance side. I was able to convert the nested files into an XML structure and then send an XML message to a web service using code to import the data. This is just not a good solution.

2. I can import the data once. Then re-run and loop over the columns that can be nested untill all that data is pulled out. This works at a decent speed, but complex, subject to issues with file changes, prone to errors, etc.

So I have not found a way to do this easier... I will look at multicasts, conditional splits, etc but not seeing how i could use them to get the initial file split properly. If you have specific informaiton, links, examples, etc of how i can handle a multi nested flat file please let me know.

Thanks again.

|||

This is easy enough... Bear with me as I try to explain for you:
Bring in your source using the Flat File Source. Delimit on ",". The field names I used were FirstName, LastName, Phone, OrderData, Active. All are strings.
Next, I chose to create a composite key because I'm assuming that first name, last name, and phone number ensures a unique record in your dataset. Creating our own counter on each load may not guarantee uniqueness when inserting into your destination tables. So, throw a derived column transformation onto the data flow. Connect it to your source and create a new column, Key. Its expression is "FirstName+LastName+Phone". I left its datatype as unicode string, although you can cast it back to string if you'd like to fit your destination.
Then, add a multicast to your data flow. Connect it to the derived column transformation.
Next, add a destination for your client table. Hook it into the multicast. Take the FirstName, LastName, Phone, Active, and Key fields.
Add a script component to another output of the multicast transform. Select two fields as input columns, OrderData and Key. Select "Inputs and Outputs". Click on Output 0 and change the property, SynchronousInputID to 0. Next, add three output columns, "order", "key", and "value". Set order to string. Set key to which ever datatype you chose in the derived column above. Set value to numeric (perferrably) and set the precision/scale appropriately. Next up is the script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim arrayOrder As Array
Dim arrayOrderValues As Array
arrayOrder = Row.OrderData.ToString.Split("|"c)
For Each order As String In arrayOrder
arrayOrderValues = order.Split("~"c)
Output0Buffer.AddRow()
' I'm assuming that there is two and only two values for each order - ordernum, amount
' I perform no error checking of the data
Output0Buffer.order = arrayOrderValues.GetValue(0).ToString()
Output0Buffer.value = Decimal.Parse(arrayOrderValues.GetValue(1).ToString())
' Attach the key from the source row to each of the new rows we create from the nested data
Output0Buffer.key = Row.Key
Next
End Sub
End Class

Next, take the script, and hook it up to your second destination, which is for the order table. Take all three fields.
Done.
--Phil

|||I get where you are going with this and it may be my best solution. I think you answered my one thought, which is that with the given components available in SSIS it is not doable, it must be done with .NET or other code in the script component. I will try your sample code and work with it a little and see how it works. As i stated there is nested within nested, so I assume I can then repeat what you did and multicast a nested field to another .NET script that further processes the record?|||My code splits everything out...

An example source file I used:
Bob,Smith,555-5555,123~3.33|245~1.99,Active
Phil,Brammer,535-3333,347~2.14|671~5.14,Inactive
Test,Name,321-3211,347~2.14|671~5.14|127~1.26|876~4.20,Active

The output from what I documented above:
CLIENT
FirstName LastName Phone Active Key
Bob Smith 555-5555 Active BobSmith555-5555
Phil Brammer 535-3333 Inactive PhilBrammer535-3333
Test Name 321-3211 Active TestName321-3211

ORDER
order key value
123 BobSmith555-5555 3.330
245 BobSmith555-5555 1.990
347 PhilBrammer535-3333 2.140
671 PhilBrammer535-3333 5.140
347 TestName321-3211 2.140
671 TestName321-3211 5.140
127 TestName321-3211 1.260
876 TestName321-3211 4.200|||

I think this will be a solution... atleast for now. I am working with your sample code and testing some throughput... and working with the real data to see if I see problem, but it looks promising. Thank you for your help and suggestions.

One quick question is you mention to set "SynchronousInputID to 0." What is this and why? I am sure I could look it up and find info, but thought it might be easier coming straight from you. Thanks again.

|||Allows you to add rows to the data flow. This sets it to asynchronous.|||This all is working well. Thank you for your help... I think that this will server well to break the flat file feed and get it into the database. The last issue that I think I will face is I will not be using a composite key as you do to tie the records. The table is using an Identity column, so records that are inserted will get a new unique value. Is there an easy way to get the id out and tie it to the other inserts?|||Well, I hate identity columns, so...

You'll have to do a lookup task to lookup a record in the dataflow with it's associated record in the table. HOWEVER, you'll likely have to split the single dataflow I provided into two separate dataflows first.sql

flat file to xml

Hey, all. Is it possible to read a flat file in and convert it to xml in SSIS? Xml is not listed as one of the destination types... OR, is there some easy way to take a fixed width flat file and convert it to xml?

Thanks for any insight!

Jim Work

SSIS does not include an XML destinaiton component. The explanation that I've heard is that this is because XML is too rich and complex a format to easily map to. At least in the first release.

You should be able to do this using a Script Component as a destination, if you have a known XML format to which you should be writing.

|||"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim
|||

It's my pleasure to help. It's always great to see how people are using SSIS, and always better to learn from others' pain than from my own.

Take a look at this page: http://msdn2.microsoft.com/en-us/sql/aa336314.aspx

There are sample component source code projects and tutorials/documentation on creating custom SSIS components available here for download.

|||

Jim Work wrote:

"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim

I've run into this a few times recently myself, so I created an example and posted it to my blog. Hope it helps.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

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 source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file source and ConnectionString property

Hi,

i have inherited a SSIS project that was left unfinished by a previous developer. One thing i notice with it is that all the flat file sources in the connection manager have hardcoded paths for the ConnectionString property. I would like to change this so that at least the path, and if possible the file name, are dynamic - i.e. they are determined either by parameters passed into the package when it is run or they are contained within a config file.

Is this possible? Can anyone supply a link to an article or tutorial specifically covering this?

Many thanks

Hi Sluggy

It's simple.

1) Create a Variable called SourceFolder of Package Scope

2) Choose the Value Property of SourceFolder Variable while Setting Package Configuration.

Now it's ready for use in expression.

3) In the Expression builder for Flat File Connection Manager, choose ConnectionString property for the flat file and use the following expression: [User:ourceFolder] + "\FileName.txt"

Thanks

Subhash Subramanyam

Flat File Prob in SSIS

I am having a problem importing a flat file in SSIS. The problem is that
there are 3 columns of data but not all columns are filled in. If the last
column is null then an error occurs and it seems like the CrLF does not work
.
The data seems to wrap. Note that the data below shows that there is not a
comma (delimiter) at the end of the line on the Cats line item.
Example data
Item Yr 2005, Yr2004
Dogs, 100 ,125
Cats, 200
Fish, 100 ,50
Does anyone know how to get around a null value at the end of a line when a
comma delimiter is missing?
Thanks in advance
Stewart Rogers
DataSort Software, L.C.The Cats line item should look like: -
Cats, 200,
The missing comma indicates that ther are only 2 columns in the row when all
the rest have 3. The provider of the data should ensure that NULL's are
represented correctly
Paul
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:90E20435-CDC5-4DB2-A4BB-F554CC6041FC@.microsoft.com...
>I am having a problem importing a flat file in SSIS. The problem is that
> there are 3 columns of data but not all columns are filled in. If the
> last
> column is null then an error occurs and it seems like the CrLF does not
> work.
> The data seems to wrap. Note that the data below shows that there is not a
> comma (delimiter) at the end of the line on the Cats line item.
>
> Example data
> Item Yr 2005, Yr2004
> Dogs, 100 ,125
> Cats, 200
> Fish, 100 ,50
>
> Does anyone know how to get around a null value at the end of a line when
> a
> comma delimiter is missing?
> Thanks in advance
> --
> Stewart Rogers
> DataSort Software, L.C.|||Thanks for the reply Paul
Thanks for the reply Paul
The sample data in not the real data. I am recieving a file that has up to
92 fields. It is a multi record type file in which some records are expected
to have 92 fields and other records are expected to have less information.
The record types are identified by a key. For Example
A Records have 92 fields
B records have 12 fields
Thus the B records do not contain 80 trailing commas.
Any thoughts on this
Thanks,
Stewart Rogers
DataSort Software, L.C.
"Paul Smith" wrote:

> The Cats line item should look like: -
> Cats, 200,
> The missing comma indicates that ther are only 2 columns in the row when a
ll
> the rest have 3. The provider of the data should ensure that NULL's are
> represented correctly
> Paul
> "Datasort" <Datasort@.discussions.microsoft.com> wrote in message
> news:90E20435-CDC5-4DB2-A4BB-F554CC6041FC@.microsoft.com...
>
>|||Stewart,
You might give the Jet driver a try. Last I checked, it was
better at importing ragged input. See
http://groups.google.com/groups/search?q=skass+ragged
Steve Kass
Drew University
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:EA9A41D3-6DE5-4D94-94E5-8AA26F00AC3A@.microsoft.com...
> Thanks for the reply Paul
> Thanks for the reply Paul
> The sample data in not the real data. I am recieving a file that has up
> to
> 92 fields. It is a multi record type file in which some records are
> expected
> to have 92 fields and other records are expected to have less
> information.
> The record types are identified by a key. For Example
> A Records have 92 fields
> B records have 12 fields
> Thus the B records do not contain 80 trailing commas.
> Any thoughts on this
> Thanks,
> Stewart Rogers
> DataSort Software, L.C.
>
> "Paul Smith" wrote:
>|||Care to elaborate on the Jet driver solution you're suggesting?
Isn't the Jet driver for Access files?|||Jet is the back end database for MS Access. It is a really great driver in
that it talks to everything ... and I mean everything. It is not as fast as
others but the flexibility is unparallel.
I have MS Access installed on my machine so I am not sure that jet ships
with SQL 2005. I have not been able to connect to the jet driver via SSIS
connection manager and I do not see jet available. I tried ODBC as well as
others and was not able to do a “Jet” connect.
One solution is to create a linked server using the Jet database. See the
following URL to understand how to create a linked server.
http://msdn2.microsoft.com/en-us/library/ms175866.aspx \. Near the bottom i
s
some code to show how to connect a text file. Note that text files have a #
sign to replace the dot in the file name. Example: MyFile.Txt is referenced
as MyFile#Txt. The url also talks about a Schema.ini file but I was able to
read the data without the file. In my case the file names change every day.
So writing a Schema.ini every day was out of the picture for me.
The driver is dynamic in that if a new text file shows up then you will be
able to access the driver.
Another solution to my problem was to create a script task that added commas
to the lines that did not have enough commas. Then I added a flat file
source in the data flow section.
Stewart Rogers
DataSort Software, L.C.
"dianos" wrote:

> Care to elaborate on the Jet driver solution you're suggesting?
> Isn't the Jet driver for Access files?
>|||Thanks Jet won't be an option for me then. And adding commas would be
only a temp solution...
So far in my searching I found this topic on MS forums where MS admits
they've done this on purpose... and that they most likely will have it
working again in the next version.
http://forums.microsoft.com/MSDN/Sh...=67416&SiteID=1
As a temp development solution I have a DTS importing data from comma
delimited files, then a SSIS grabs the data from sql2000 and does it's
stuff...
There is also a free CSV reader in c# here
http://www.codeproject.com/cs/datab...926#xx1410704xx
I haven't found anything SSIS friendly nor easy to use yet. It looks
like I'll have to write it myself :)

Flat File loading issue

While loading the flat file into sql server2005 using SSIS , i am getting error like:

[Source - 20070801PensionPayments_dat [1]] Error: The "output column "Status" (246)" failed because truncation occurred, and the truncation
row disposition on "output column "Status" (246)" specifies failure on truncation. A truncation error occurred on the specified object of
the specified component.

In the EM import wizard, i can see last column [][](blank space similar box type). because of that i unable to load data into table. The delimiter is fixed width.

Please help me - Thanks in Advance.

You need to make the column big enough to capture the data in the source file, before trying to get rid of spaces.

Adjust your flat file source to give that column enough length to capture the data.

Then you can use a derived column to remove spaces from that field.|||

The Source file table name is :

Columns type Remarks MemberFirstName nchar(20) Firstname MemberLastname nchar(20) Lastname MemberPrefix nchar(4) <blank> MemberSuffix nchar(4) Suffix Status nchar(1) <blank>

Sample data like , only first name has value remaining columns blanks using of nchar data type.

While extrating the data , i am using Fixed width --> delimiter,even though last column giving problem

Ex:

MARIANN|||Your flat file mapping is not correct. There is more data coming in via the file than the source is set to handle. Fix that first.

Flat File Insert

Hi! I'm a newbie to SSIS and I'm trying to insert a flat file into a table.

The flat file contains some bad data so I want to insert the data into a text column.

I'm getting the following error below... It seems like SSIS cannot find the row delimiter, but I just want to load the file into a text column as a single row. I am able to insert the data to a text column in SQL 2000 DTS. Is there a way to do this in SQL 2005?

Please help... and Thanks in advance!!

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.

The component returned a failure code when the pipeline engine called PrimeOutput().

The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[Flat File Source [1]] Error: An error occurred while processing file \\sss.sss on data row 1.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.

How do you know it is a problem with the delimiter?

Could you provide more information about the structure of the file and how you are configuring the flat file source component?

|||

PLog

It sounds like you probably need to set up the flat file parsing to expect a single column and use a delimiter for that column that is not in your data (so it doesn't match anything in the data). You set the flat file parsing in the flat file source adapter UI. Let us know if that answers your question.

thanks,

denise

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

Flat File Connection Manager HeaderRowsToSkip not modifiable at runtime?

I have been working with flat file connection managers for a while now, and I have found it very useful to set properties on all types of SSIS objects using expressions that are set to SSIS package variables.

We have recently moved towards using a centralized application configuration infrastructure, so we are now loading up all application configuration information from our own system -- it's actually based off of the 2.0 ConfigurationManager.

I have developed a custom control flow task that will retrieve all of the SSIS variable information from our own application configuration system and set all of the SSIS variables as the first step in a package.

When I have been using this to set the SSIS Variables which are used in expressions for the HeaderRowsToSkip and DataRowsToSkip (there may be others, but I have not tested them yet) properties of flat file connection managers, these values are not being used -- instead it is using the values for the variables that are defined in the package itself (i.e. the default values that I set up in BIDS when developing the package). The ConnectionString property on the flat file connection manager does not exhibit this behavior.

Can anyone provide any insight on this? Is this a bug in SSIS, or is there something that I am missing here?

Thanks in advance.

Sounds like a great big stonking bug to me. Could you log it at Microsoft Connect?

-Jamie

|||certainly will do -- by the way, love your blog -- it's one of my favorite resources for SSIS development issues.|||

Glad to hear it. I really appreciate the feedback.

-Jamie

flat file connection bug

What happens is that the flat file connection non of the columns can be altered

you can set them in other ssis packages but not in the one that you want to use and when it comes to changes the flat file complains that not a correct file is set, even though there is one set,

and when it comes to altering a flat file source it complains that it can not find the connection and the database destination can not find the meta data BUT

when it is run it works perfectly,

so what i have had to resort to is making the part of the dts package in another package and then copying it accross

Do you have delay validation on the connection or source, or are you working off line.

Some of the components don't handle these states very elegantly.

|||

I do not know what you mean buy delay validation, but i am working on the server, connected to the server on the same mechine. the connection string is set to a variable that is set in a file loop, but when i remove, the variable and try it again it stil does not work...

|||

on the top menu you must check that the ssis drop down is not set to offline

flat file connection bug

What happens is that the flat file connection non of the columns can be altered

you can set them in other ssis packages but not in the one that you want to use and when it comes to changes the flat file complains that not a correct file is set, even though there is one set,

and when it comes to altering a flat file source it complains that it can not find the connection and the database destination can not find the meta data BUT

when it is run it works perfectly,

so what i have had to resort to is making the part of the dts package in another package and then copying it accross

Do you have delay validation on the connection or source, or are you working off line.

Some of the components don't handle these states very elegantly.

|||

I do not know what you mean buy delay validation, but i am working on the server, connected to the server on the same mechine. the connection string is set to a variable that is set in a file loop, but when i remove, the variable and try it again it stil does not work...

|||

on the top menu you must check that the ssis drop down is not set to offline

Flat file (CSV) source format

I have a SSIS package loading a lot of CSV file, which first line is the column head. Some file are ordered differently. However, package still try to load the file use predefined column order (it seems it doesn't check the head of each file see if it matchs the predefined column order).

Any way to force the package the check each file's head? or I had to manually check it using VB.Net script?

No, the Flat File connection manager cannot adapt to dynamic file formats. You can workaround with script. These threads should be helpful.

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

Fixed width output problem

I'm sending the results of an SSIS data flow to an fixed-width flat file output, but instead of getting separate rows of data, like so:

row1data...
row2data...
row3data...
etc...

I get:

row1data...row2data...row3data...etc...

Is there some setting I'm missing in either the flat file output or the file connection to turn this on?

The fixed width format does not include the row delimiters.

You can use the Ragged right format if your last column always has a fixed format or you do not care for it to be fixed.

If you do have a requirement for the fixed length of the last column (like you always want integer values to take 10 characters in the file), you can create your Flat File Connection manager by clicking "New..." on the Flat File Destination UI and choose the "Fixed width with row delimiters" option. That will actually create the ragged right file with a dummy row delimiter column.

HTH.

|||Thank you. That's what I needed.|||Thanks so much for this. I wanted SSIS to mimic the fixed width behavior of DTS or SQL Server 2000 export wizard. This was exactly what I was looking for.

fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"

fix missing value

Can SSIS detect the missing value and replace it with some reasonable value (e.g., maximum, median, average, etc.)?
thanks

You haven't given enough information.
Where is the missing value?

|||Sorry that I was not clear. The missing value exists in a table. For example, one column of one row does not have any data. This can cause problem when I try to use this table for some task, e.g., train a data mining model. So I want to find a way to detect these places and fill them with some reasonable value.
Thanks|||If you just want to fix these values in the table then just issue an T-SQL UPDATE statement:
UPDATE MyTable
SET MyField = ISNULL(MyField, <some_value>)
or
UPDATE MyTable
SET MyField = <some_value>
WHERE MyField IS NULL
This can be issued from an SSIS Execute SQL Task if you so desire.

If you are moving this data to somewhere else using SSIS and you want to eliminate the missing values as the data is being moved then include a Derived Column Transform in your data-flow which can be used to replace a missing value using an expression.
The expression builder is fairly intuitive but if you have any trouble then reply here and people will be able to help out.

-Jamie|||
Thanks.

But if there is a way that SQL Server can automatically figure out a reasonable value used to fill in the missing value? I mean I do not need to specify a value to replace the missing value, but SQL Server will figure out one

Thanks|||

Yanbing Hu wrote:

Thanks.

But if there is a way that SQL Server can automatically figure out a reasonable value used to fill in the missing value? I mean I do not need to specify a value to replace the missing value, but SQL Server will figure out one

Thanks

Well surely that depends on your criteria for deciding what the missing value is. Can you give an example?

-Jamie|||You might be refering to the default value that can be specified per column in the CREATE TABLE statement itself.