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 export

I am new to the Integration Services and have a question. I need to export some data to a flat file. I set up a project and have a OLE DB Source object that I wrote a query to grab the data. I then pass that data to a Flat File Destination object. My question is that in the database one of the fields is stored at True or False, but in the flat file I need for it to be -1 or 0. Any help would be appreciated.

Thanks

The easiest way to do this is in your data set. Use a sql CASE statement to read the value and return the appropriate values to the column for TRUE and FALSE.

Hope that helps.

|||That will work perfect, Thank you.
|||

You can also do the same thing in a derived column transform using:

Code Snippet

[YourColumn] ? -1 : 0

assuming 0 is false and -1 is true.

First() to SQL?

Hi,
I'm working with a project translating Access databases to SQL Server.
Can anyone explain the mystic function First() to me?
How can it's function be replaced by SQL?
(I've posted this in the Accessforum also)As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..

http://office.microsoft.com/en-us/assistance/HA010345631033.aspx

In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.|||First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.

In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.

select [YourValue] as FirstValue
from [YourTable]
inner join
(select min([SortKey]) as FirstKey from [YourTable]) Subquery
where [YourTable].[SortKey] = Subquery.FirstKey

If you sortkey is not unique, you will get multiple records in your result.

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.
Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegr oups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent run
s? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First Data Processing Extension Problem

I am trying to write my first data processing extension. The project
is based upon the Event Log example in chapter 13 of Hitchhiker's
Guide to SQL Server 2000 Reporting Services. The assembly has been
copied to the various directories and the various config files have
been modified.
The extension is used in a report project and a shared datasource is
created using the extension. When a report's dataset is created the
Fields window in the IDE is not populated. When the command is run in
the data window of the designer a null pointer error occurs. In the
preview window the error indicates the connection is not valid. This
project is written in C# and the supplied sample is written in VB.NET.
The same error occurs using the VB assemblies.
The "connection not valid" exception is generated from command class'
ExecuteReader method. The connection member is found to be null w/in
the method. In the overloaded c'tor where the connection argument is
assigned to the connection member, the argument is not null and the
member is not null after it has been assigned to. I am baffled as to
what/why/how this occuring. Suggestion?
Equally or more important how does one setup a data processing
extension project for debugging?
thank you
A.G.All issues resolved. Using System.Diagnostics.Debugger.Break I was
able to access the CLR debugger and see what was wrong. First problem
was a typo in a string. Then an indexing error was found and then the
extension 'worked' returning just a single record.
A closer examination of the datareader's Read method revealed a number
of issues involving the iteration of the event log. Changing the
method so it read one record at a time made everything work as
expected. In the sample code the entire event log is iterated when the
Read method is invoked.
Fortunately the exercise in frustration was offset by the learning and
understanding experience.
regards
A.G.
On Tue, 13 Dec 2005 18:37:43 -0500, Registered User
<n4jvp@.ix.netcom.com> wrote:
>I am trying to write my first data processing extension. The project
>is based upon the Event Log example in chapter 13 of Hitchhiker's
>Guide to SQL Server 2000 Reporting Services. The assembly has been
>copied to the various directories and the various config files have
>been modified.
>The extension is used in a report project and a shared datasource is
>created using the extension. When a report's dataset is created the
>Fields window in the IDE is not populated. When the command is run in
>the data window of the designer a null pointer error occurs. In the
>preview window the error indicates the connection is not valid. This
>project is written in C# and the supplied sample is written in VB.NET.
>The same error occurs using the VB assemblies.
>The "connection not valid" exception is generated from command class'
>ExecuteReader method. The connection member is found to be null w/in
>the method. In the overloaded c'tor where the connection argument is
>assigned to the connection member, the argument is not null and the
>member is not null after it has been assigned to. I am baffled as to
>what/why/how this occuring. Suggestion?
>Equally or more important how does one setup a data processing
>extension project for debugging?
>thank you
>A.G.

first analysis service project+deployment failure

I created small analysis service project as according to the MSDN.
and when i test the database connection it says its okay.
but when i try to deploy , it says

'The project could not be deployed to the '(localhost)\SQLEXPRESS' server because of the following connectivity problems : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.'.

what kind of security or other configurations i should do.
what went wrong?

i know someone might have answered this earlier also.
pl help on this.I had kinda the same problem a while ago, when I started the following thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247494&SiteID=1

Hope this'll be helpfull :-)|||

The message "'The project could not be deployed to the '(localhost)\SQLEXPRESS'" means that you are trying to deploy an AS project to a SQLServer EXPRESS edition?

Because SQL Server EXPRESS edition doesn't support Analysis Service.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx