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

Fix dependencies during or after renaming

The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.

Is there some tool or technique to rename a table and automatically detect and
properly drop/rebuild dependent triggers, views, stored procedures,
relationships, and anything else I'm not thinking of?

Oh yeah, and there are extended properties to deal with as well, but I guess
that's intractible unless we know what client build them (e.g. Access ADP),
and what it expects to be recorded there, but I suppose it would be nice to
just dump them rather than leave them there possibly corrupted from a client's
persective.Steve Jorgensen wrote:

> The previous post regarding dependencies reminded me of another bugger I come
> up with from time to time. SQL Server allows us to rename objects via a
> system stored procedure (I usually use an ADP front-end that presumably calls
> the same procedure), but after the rename, I find singnificant strangeness.
> For instance, triggers are still connected to the tables, but the trigger text
> still refers to the old table name. Views and stored procedures dependent on
> the table are broken, and are not fixed by even by restarting the server,
> though I thought the cached plans were supposed to be dumped on shut-down,
> then recreated on demand later.
> Is there some tool or technique to rename a table and automatically detect and
> properly drop/rebuild dependent triggers, views, stored procedures,
> relationships, and anything else I'm not thinking of?
> Oh yeah, and there are extended properties to deal with as well, but I guess
> that's intractible unless we know what client build them (e.g. Access ADP),
> and what it expects to be recorded there, but I suppose it would be nice to
> just dump them rather than leave them there possibly corrupted from a client's
> persective.

One thing I know about views, if you rename a view, you best double
click it in EM and rename it in the create statement as well otherwise
it will get renamed back to the old name if ever you script it.
(checks his 4 views, nope, that's not why that happened).|||On Fri, 23 Jan 2004 11:01:27 GMT, Steve Jorgensen <nospam@.nospam.nospam>
wrote:

>The previous post regarding dependencies reminded me of another bugger I come
>up with from time to time. SQL Server allows us to rename objects via a
>system stored procedure (I usually use an ADP front-end that presumably calls
>the same procedure), but after the rename, I find singnificant strangeness.
>For instance, triggers are still connected to the tables, but the trigger text
>still refers to the old table name. Views and stored procedures dependent on
>the table are broken, and are not fixed by even by restarting the server,
>though I thought the cached plans were supposed to be dumped on shut-down,
>then recreated on demand later.

Oh yeah, I guess the views and procedures don't get the table names updated in
the code, so that one's obvious (though I'd still like an automated way to fix
it). What -was- confusing about that was why renaming a -column- causes
problems for stored procedures and views, even after a restart, even if the
procedure or view is uing an asterisk in the select and not directly
referencing the column in question.|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> Oh yeah, I guess the views and procedures don't get the table names
> updated in the code, so that one's obvious (though I'd still like an
> automated way to fix it). What -was- confusing about that was why
> renaming a -column- causes problems for stored procedures and views,
> even after a restart, even if the procedure or view is uing an asterisk
> in the select and not directly referencing the column in question.

Hm, I wrote this procedure:

create proc test_sp as select * from test

Which I subsequently executed. And I found that test had two columns
'mystring' and 'col'.

Then I ran:

sp_rename 'test.mystring', hisstring, 'column'

Without restarting the server, I successfully ran the procedure, and
the column was called 'hisstring'. I then created:

create view test_view as select * from test

And then:

sp_rename 'test.hisstring', herstring, 'column'

I was able to select from the view after this. However in the view
the column was still named 'hisstring'. This is because SQL Server
stores the name of the columns in the view in the system tables. This
can be remedied with:

exec sp_refreshview test_view

Finally, I would like to point out that I, and many with me, find it
to be bad coding practice to use SELECT * in production code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> The previous post regarding dependencies reminded me of another bugger I
> come up with from time to time. SQL Server allows us to rename objects
> via a system stored procedure (I usually use an ADP front-end that
> presumably calls the same procedure), but after the rename, I find
> singnificant strangeness.
> For instance, triggers are still connected to the tables, but the
> trigger text still refers to the old table name. Views and stored
> procedures dependent on the table are broken, and are not fixed by even
> by restarting the server, though I thought the cached plans were
> supposed to be dumped on shut-down, then recreated on demand later.
> Is there some tool or technique to rename a table and automatically
> detect and properly drop/rebuild dependent triggers, views, stored
> procedures, relationships, and anything else I'm not thinking of?

The major enterprise data-modelling tools offers such features.

If you have your code under version-control - and this is what you should -
you could run some search/replace tool, provided that the old column
name is significant enough.

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

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

First/Last Day of Month (current and previous three months)

Hi,
I want to set a parameter to use the first and last days of the month for
two parameters' defaults. I then want to set the first day of the previous
two months as the "available values" for on of the parameters and the last
day of the previous two months for the other parameter's "available values".
Can anyone tell me why this isn't working (I want to get last day of month):
=DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
I have tried using the Today funcion instead of Now with no joy. I started
doing this with DATEADD and DATEDIFF, as is commonly found as the solution
for this on tons of tech articles. However, I have learnt that although
T-SQL can work out what you want to do with DATEADD (which expects DateTime)
and DATEDIFF(which returns an Integer), reporting services cannot. So I
found the above expression that doesn't use DATEDIFF, but I still can't get
it to work. The expression builder doesn't seem to find a problem with the
syntax (no green or red wavy lines), but when I run the report I get the
following:
An error occurred duing local report processing. Error during processing of
'SDate' report parameter.
Please tell me someone has an answer for me. This should be so simple. I
bet I am going to kick myself when (if!!) the answer comes through.
TIA,
JarrydHi,
Well I just did this in T-SQL as part of the procedure that feeds the
report. Created a virtual table and added a second dataset to the report.
That seems to work. One thing I find odd - the "calendar" button can't be
used to set the date properly. We are in the UK and so the calendar
generates a UK style date, but Reportiong Services won't have it; you have
to manually enter it in USA format!! How do you solve it? Please don't
tell me you have to programtically grab the variable and cast it in USA
style. That's just silly. But if so, then where do you do it? I am
assuming you have to go to Dataset>Parameters and jimmy the value field's
value (def: Parameters!My_Param.Value) to grab the value and reorder the
days and months but I can't get it to work. The only other place I know of
is the Report>Report Parameters form, but that doesn't look too promising.
TIA,
Jarryd
"Jarryd" <noemail@.nodomain.com> wrote in message
news:u4I1mEMqHHA.2372@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to set a parameter to use the first and last days of the month for
> two parameters' defaults. I then want to set the first day of the
> previous two months as the "available values" for on of the parameters and
> the last day of the previous two months for the other parameter's
> "available values".
> Can anyone tell me why this isn't working (I want to get last day of
> month):
> =DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
> I have tried using the Today funcion instead of Now with no joy. I
> started doing this with DATEADD and DATEDIFF, as is commonly found as the
> solution for this on tons of tech articles. However, I have learnt that
> although T-SQL can work out what you want to do with DATEADD (which
> expects DateTime) and DATEDIFF(which returns an Integer), reporting
> services cannot. So I found the above expression that doesn't use
> DATEDIFF, but I still can't get it to work. The expression builder
> doesn't seem to find a problem with the syntax (no green or red wavy
> lines), but when I run the report I get the following:
> An error occurred duing local report processing. Error during processing
> of 'SDate' report parameter.
> Please tell me someone has an answer for me. This should be so simple. I
> bet I am going to kick myself when (if!!) the answer comes through.
> TIA,
> Jarryd
>

first day of previous month date parameter

Hi,
Hope somebody can help.
I am looking for the code to place in the default value of a parameter wich
will give me the previous months first days date (i.e. last month would be
01/03/2005).
I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
so something similar to this would be great.
Thanks in advance.
Paul=DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
Month(DateAdd(DateInterval.Month, -1, Now)), 1)
Charles Kangai, MCDBA, MCT
"pcalv" wrote:
> Hi,
> Hope somebody can help.
> I am looking for the code to place in the default value of a parameter wich
> will give me the previous months first days date (i.e. last month would be
> 01/03/2005).
> I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> so something similar to this would be great.
> Thanks in advance.
> Paul|||That worked great, thanks for your help.
Paul
"Charles Kangai" wrote:
> =DateSerial(Year(DateAdd(DateInterval.Month, -1, Now)),
> Month(DateAdd(DateInterval.Month, -1, Now)), 1)
> Charles Kangai, MCDBA, MCT
> "pcalv" wrote:
> > Hi,
> >
> > Hope somebody can help.
> >
> > I am looking for the code to place in the default value of a parameter wich
> > will give me the previous months first days date (i.e. last month would be
> > 01/03/2005).
> >
> > I already have code for the last day: =DateSerial(Year(Now), Month(Now), 0)
> > so something similar to this would be great.
> >
> > Thanks in advance.
> >
> > Paul