how to initialize package variables?

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

jpsR1 wrote:

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

The best way would be to do it in a script task. There's lots of material around to help you do this. Search for "ReadWriteVariables".

-Jamie

|||

Thanks Jamie. I did find some info but it helped when i searched for "ReadWriteVariables".

this is what I used. I had found a similar sample elsewhere and had tried it with no luck. The difference being, I originally had not put the prefix "User::" in front of the variable.

Phil / Mike, thanks for the help...

Now I'm actually confused (seems to happen alot since I delved into SQL 2005!). I actually placed the "User::" tag in front of my variables and it worked. Oh well, I have enough to play with for now (I especially like the functions for the read / write capabilities). I'm really just trying to start refreshing my scripting skills (or obviously lack thereof)...

You actually can put the "User::" in on the Script Task Editor and it works...

If I define the variables "User::varFTPDestPath" and "User::varFTPFileName" as ReadOnly and "User::varFTPDestPathFileName" as ReadWrite in the Script Task Editor, the following works:

Public Sub Main()

Dim strFTPDestPath, strFTPFileName As String
strFTPDestPath = Dts.Variables("User::varFTPSourcePath").Value.ToString
strFTPFileName = Dts.Variables("User::varFTPFileName").Value.ToString
Dts.Variables("User::varSourcePathFileName").Value = strFTPDestPath + strFTPFileName
MsgBox("varSourcePathFileName = " + Dts.Variables("User::varSourcePathFileName").Value.ToString)
Dts.TaskResult = Dts.Results.Success

End Sub

verbatim:

Dts.Variables("User::rcSourceFile").Value = 0

Dts.Variables("User::rcCleanInserts").Value = 0

thanks again!!!

sql

how to indicate if error exisits

I have created a package.Is there an option if you can know if the package had executed successfully or with the error...how do i need to set?how are you planning to execute the package? I think SS Agent has alert/notifications you could use...|||ok with sql server agent how do i include email address on error in my package..And what i am intrested is how send email task works...it shows errors witht this task.|||

Well, my sugestion was simpler than that. I was thinking to let the SS agent to sen an email when the job fails; so you don't have to modify the packages. The only thing is that the SS agent may not give a very detail reason of the failure.

You are right, you could use event handlers and send email task to do this, but it requires to modify the packages. In both cases, you can find information in these forums.

How to include SQL Express in Installation Package and change some option?

Hi, there,
I'm working on a windows application project which uses SQL Express as
database.
I found I can include SQL Server 2005 Express Edition in the installation
project as Prerequests,
that's cool.
But I need do more things, I want to change the "Authentication Mode" from
"Windows Authentication mode"
(which is default) to "SQL Server Authentication mode" and assign a "default
password" to user "sa", and
I want to attach a database file which will be include on the installation
disc.
So what shall I do? Write some script in the installation project?
Does SQL Express provide any program interface to do that?
It seems there is no help content for this in the MSDN and I did some search
job and got nothing.
Help needed. Thank you.
Best wishes!
I could write a chapter on this but here it is in a nutshell.
You need to use the Unattended Install tenplate.ini file along with the
LOGName and Datasource.xml.
Here is a fantastic article.
http://www.devx.com/dbzone/Article/31648
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi, there,
> I'm working on a windows application project which uses SQL Express as
> database.
> I found I can include SQL Server 2005 Express Edition in the installation
> project as Prerequests,
> that's cool.
> But I need do more things, I want to change the "Authentication Mode" from
> "Windows Authentication mode"
> (which is default) to "SQL Server Authentication mode" and assign a
> "default password" to user "sa", and
> I want to attach a database file which will be include on the installation
> disc.
> So what shall I do? Write some script in the installation project?
> Does SQL Express provide any program interface to do that?
> It seems there is no help content for this in the MSDN and I did some
> search job and got nothing.
> Help needed. Thank you.
> Best wishes!
>
|||Hmm.....
Thank you.
I have read it, and there is really something interesting.
But there is another thing I can not do with this article.
I want to open Name Pipe and TCP connection of the SQL EXPRESS during the
setup,
but there is no such setting in the template.ini file.
Any other help?
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uFytSE%235GHA.3592@.TK2MSFTNGP05.phx.gbl...
>I could write a chapter on this but here it is in a nutshell.
> You need to use the Unattended Install tenplate.ini file along with the
> LOGName and Datasource.xml.
> Here is a fantastic article.
> http://www.devx.com/dbzone/Article/31648
>
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Philip.Arbin" <philip@.newsgroups.nospam> wrote in message
> news:OvWXUO85GHA.508@.TK2MSFTNGP06.phx.gbl...
>
|||Philip,
All you need to do is edit the package.xml file - you can specify the
command line parameters you want to use for installing SQLExpress. The file
is located in
C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
The default setup is
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1'
You might want to change it to something like
Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
DISABLENETWORKPROTOCOLS=0'
See the readme file for SQLExpress for the command line options. That will
get SQLExpress installed the way you want it. Use a custom action dll to
install your db, attach it, set up the user accounts you want, etc.
Russ Stevens
|||Ah haaaaaa!
This is right the solution I am looking for!
Powerful!
Thank you very much!
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:OkMqQxy8GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Philip,
> All you need to do is edit the package.xml file - you can specify the
> command line parameters you want to use for installing SQLExpress. The
> file is located in
> C:\Program Files\Microsoft Visual Studio
> 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en
> The default setup is
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1'
> You might want to change it to something like
> Arguments='-q /norebootchk /qn reboot=ReallySuppress addlocal=all
> instancename=SQLEXPRESS SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=yoursapw
> DISABLENETWORKPROTOCOLS=0'
> See the readme file for SQLExpress for the command line options. That will
> get SQLExpress installed the way you want it. Use a custom action dll to
> install your db, attach it, set up the user accounts you want, etc.
> Russ Stevens
>

How to include MSDE installation in my apps installation package

Hi all,
Anybody knows how to include MSDE in my apps installation package? I've
downloaded MSDE Deployment Package from
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
d98-bb0b-2c9d6414071f&DisplayLang=en.
but unfortunatelly:
1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
Edition, and;
2) it's a beta product, and Microsoft doesn't approve its use in the
production environment nor use it to deploy any applications.
I'm also considering alternatives to use InnoSetup. Will the solution be
easier if I use Inno?
Looking forward to hearing from all of you!
TIA,
Willianto
Try going to the tools menu in Installshield and go to "Check for updates."
I don't have limited edition of Installshield but this should be there. In
the updates you will find MSDE in there. I believe this will then show up
in your redistributables area and can then add it to your install.
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Anybody knows how to include MSDE in my apps installation package? I've
> downloaded MSDE Deployment Package from
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> d98-bb0b-2c9d6414071f&DisplayLang=en.
> but unfortunatelly:
> 1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
> Edition, and;
> 2) it's a beta product, and Microsoft doesn't approve its use in the
> production environment nor use it to deploy any applications.
> I'm also considering alternatives to use InnoSetup. Will the solution be
> easier if I use Inno?
> Looking forward to hearing from all of you!
> TIA,
> Willianto
>
|||Altman:

> Try going to the tools menu in Installshield and go to "Check for
updates."
Hmmm... is there anyway to still have the MSDE in the checklist without
having to update the ISE?
What about InnoSetup?
Regards,
Willianto
"Altman" <NotGiven@.SickOfSpam.com> wrote in message
news:ut8I9WDNEHA.3016@.tk2msftngp13.phx.gbl...
> I don't have limited edition of Installshield but this should be
there. In
> the updates you will find MSDE in there. I believe this will then
show up[vbcol=seagreen]
> in your redistributables area and can then add it to your install.
> "Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
> news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
I've[vbcol=seagreen]
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4[vbcol=seagreen]
solution be
>
|||I don't think it comes with it, it is a free update to installshield, I
believe it is just a merge module. Otherwise check out this site
http://msdn.microsoft.com/library/de...eddingmsde.asp
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:uwlMliENEHA.3476@.TK2MSFTNGP09.phx.gbl...
> Altman:
> updates."
> Hmmm... is there anyway to still have the MSDE in the checklist without
> having to update the ISE?
> What about InnoSetup?
> Regards,
> Willianto
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ut8I9WDNEHA.3016@.tk2msftngp13.phx.gbl...
> there. In
> show up
> I've
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> solution be
>
|||Hi Altman:
Thanks for still being with me
> I don't think it comes with it, it is a free update to installshield,
I
> believe it is just a merge module.
I've found the page where InstallShield put the msm:
http://support.installshield.com/kb/...icleid=Q106295
But it looks like, the msms are for InstallShield Developer 7.0SP3,
8.xx, and DevStudio 9.0. Since I connect to the internet with DUN, I
cannot afford to 'just try todownload and see whether it works with ISE
LT or not'. Can anybody give me a clue whether it works or not?

> Otherwise check out this site
>
http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/sql_embeddingmsde.asp
Ok. I've read the article. It is said that I can create my own MSI
package. Okay. That's acceptable. And for that I'm going to need
Microsoft Platform SDK. Okay, I'd check it... and... YIKES!!!!
342,000,000 bytes of download! More than 300 megs! And I connect thru
DUN! Do you have any alternatives?
Willianto
[vbcol=seagreen]
> "Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
> news:uwlMliENEHA.3476@.TK2MSFTNGP09.phx.gbl...
without[vbcol=seagreen]
message[vbcol=seagreen]
package?[vbcol=seagreen]
http://www.microsoft.com/downloads/d...e9a7403-c4ba-4[vbcol=seagreen]
FoxPro[vbcol=seagreen]
the
>
|||You can save yourself a lot of wasted time and research by running the
setup.exe that comes with MSDE. You can pass command line arguments to
configure it. The merge modules are buggy. This what MS recommends.
HTH Bill
"Willianto" <willianto@.remove-me.telkom-and-me.net> wrote in message
news:%2337B8J6MEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Anybody knows how to include MSDE in my apps installation package? I've
> downloaded MSDE Deployment Package from
> http://www.microsoft.com/downloads/d...e9a7403-c4ba-4
> d98-bb0b-2c9d6414071f&DisplayLang=en.
> but unfortunatelly:
> 1) It's for Visual Studio; meanwhile I use InstallShield LE FoxPro
> Edition, and;
> 2) it's a beta product, and Microsoft doesn't approve its use in the
> production environment nor use it to deploy any applications.
> I'm also considering alternatives to use InnoSetup. Will the solution be
> easier if I use Inno?
> Looking forward to hearing from all of you!
> TIA,
> Willianto
>
|||Bill:
> You can save yourself a lot of wasted time and research by running the
> setup.exe that comes with MSDE. You can pass command line arguments to
> configure it. The merge modules are buggy. This what MS recommends.

Looks like that's the only way to go...
Thank you. And thanks to you too, Altman.
I've been expecting too much from M$ freebies
Regards,
Willianto

How to import several excel files using one dts package

It's very simple import, without any data modifications; from several
excel files to one table.
I tried the wisard and it gives me selection for only one file.
I am not used to create DTS packages and schedule jobs, so I would need
some help.

Thank you
InnaHi Inna,

Perhaps this article will be of help:
http://www.devx.com/getHelpOn/10MinuteSolution/18088

The article at the link above shows how to import Excel data into SQL
Server from an indeterminate number of Excel workbooks.

If the # of Excel files you have is small, you can do the following in
the DTS Designer:

1) Drag 1 Excel connection object per file into the DTS "canvas" (the
main area of the DTS Designer) and define the connections as
appropriate

2) Drag a connection object for your SQL Server and define it as
appropriate. One thing to note is that you should set the DB to connect
to the same as the DB you want to import the data into

3) Define a "Transform Data Task" (see your Tasks menu in the DTS
Designer) between each Excel connection and the SQL Server connection.

4) A line will appear between each Excel connection and the SQL Server
connection. Right click on each line and select "Properties" from the
menu that appears. Alter the properties of the task (e.g. column
mappings) as appropriate.

5) Execute the task

Hope that helps a bit.

How to import a bunch of files?

I have a large number of text files that I want to import into a database. I
have created a DTS package that knows how to import and transform the data
so that it can be imported but that seems to be hard coded to a particular
file. Doing this by hand 18 thousand times is not something I want to do. Is
there a command line interface that I can call from either DOS or WSH to
import all of this data? I'm thinking that something like
for %%f in (*.txt) do <import %%f>
would be great. Anyone have a suggestion on how this could be done?
Richard Lewis Haggard
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
For SQL2005 you can use the ForEach Loop file enumerator
Looping over files with the Foreach Loop
http://www.sqlis.com/default.aspx?55
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>I have a large number of text files that I want to import into a database.
>I have created a DTS package that knows how to import and transform the
>data so that it can be imported but that seems to be hard coded to a
>particular file. Doing this by hand 18 thousand times is not something I
>want to do. Is there a command line interface that I can call from either
>DOS or WSH to import all of this data? I'm thinking that something like
> for %%f in (*.txt) do <import %%f>
> would be great. Anyone have a suggestion on how this could be done?
> --
> Richard Lewis Haggard
>
|||I ended up doing it with a dtsrun package called from a pair of batch files.
In this case, c:\StockWizPro98\OUTPUT is the destination directory that
receives periodic updates and data drops. I decided to make a temp directory
that will have a single file in it and to create a DTS package that knows
how to import data from it. The
first batch file iterates through all of the input files, copies and renames
them to what the DTS package is looking for and calls the package to import
the data. Simple.
1.bat
pushd c:\StockWizPro98\OUTPUT
for %%f in (*.txt) do call 2 %%f
popd
2.bat
pushd c:\StockWizPro98\OUTPUT\temp
copy /y ..\%1 temp.txt
dtsrun /s (local) /n StockImport /e
popd
Richard Lewis Haggard
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23aBh2ff%23FHA.740@.TK2MSFTNGP12.phx.gbl...
> Looping, Importing and Archiving
> http://www.sqldts.com/default.aspx?246
> For SQL2005 you can use the ForEach Loop file enumerator
> Looping over files with the Foreach Loop
> http://www.sqlis.com/default.aspx?55
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
> news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>

How to impletement this query in SSIS package

Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?

update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1

Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps).|||

Hi Phil,

if I can't not write SQL script to impletement this, I have to use condition split (for column01<>-1 ) -> Sort->Merge Join(Join dbo.columnlist table )->Condition Split (column from dbo.columnlist is null), derived column (update column01 with -1) -> Union All.

it looks so complicated.

do you know is there any component that we can write script to update the input column set? I know we can use VBScript component to do this , but I'm wondering if we can use SQL Script.

|||I think you should perform a lookup transformation against "select column from dbo.columnlist".

Using the lookup error output, you can add a derived column that sets column01 to -1. Then, use a union all to join the normal lookup output with its error output.

Don't worry about the "and column01 <> -1" statement. It is worthless.|||

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

I tried with 'enable memeory restriction' and modify the SQL Statement , replacing the '=' with '<>', but it prompts error when I debug, saying 'no match rows', but actually I do have match rows.

|||

Jeff_LIU wrote:

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow).

How to implement incremental load in fact tables.

Hi all,

i have a fact table which loads through package,when

i m trying to load this table by running the package,i m truncating the

fact table and loading the fresh data,instaed of this without

truncating the fact table i have to implement the incremental logic in

this.

For this i can use SCD or Conditional split,but problem

here is i have many source tables to load this fact table,so its

very difficult to trace the changes in different source tables.

can any one help me out in this?You should stage all of your source tables along with the current load date. That way, when you select from your source staging tables, you can retrieve the data based on that date field, thereby only picking up the new/changed records.

How to I run a DTS package from a stored procedure? thank

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

Hi,

you have to call it via dtsrun on the command prompt, ousing xp_cmdshell.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens Suessmeyer,

could you be more specific? can you give me a example?

thanks

|||Sure, look the the DTSRUN syntax, you can start the dtsrun either with a GUID naming the package which is stored in SQL Server or by a structured storage file, using the XP_CMDSHELL 'DTSRUN SomePackage' will get you the package run.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Another method would be to use the system OLE automation SPs and use the DTS object model to invoke the package. Please search the web for several examples. Btw, this question is more suited for the SQL Server Integration Services newsgroup so I will move the thread there so someone there can point you to appropriate resources/links.|||

Please read through the article on 'Data Transformation Services (DTS)' @. http://www.databasejournal.com/features/mssql/article.php/1459181
The example in this article demostrates the use of OLE stored procedures and its benefits.

Btw - This forum majorly deals with SQL Server 2005 - Integration Services.

For DTS (SQL Server 2000) related questions post @. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US

Thanks,
Loonysan

|||I would keep away from using OLE stored procedures - it is a convinient way to do it, but you are running inside SQL Server process. Thus any problems with DTS package may affect the server. Running the package outside using DTSRUN utility is better, as the SQL Server and package run in isolated processes and don't affect each other.

If you mistyped DTS, but was really asking about SSIS (this is SSIS forum after all) - the recommended way is to create Agent Job with the package step, don't assing any schedule to this job, and then start this job from your SQL stored procedure by calling Agent's SP. This provides the isolation as with DTSRUN, but additionally you may specify user context for the SSIS package - so the package does not have to run under the same user as SQL Server.