Solved

Create DTS package with variables

Posted on 2004-08-17
12
273 Views
Last Modified: 2013-11-30
I would like to create a DTS package to export data from SQL database to a text file. The name of the text file will be the value of a text box the users enter on the a web page. When the users click submit button, it will take the value in the text box and run the stored procedure that executes the DTS package. The dealine for my project is coming very soon, please help. Thanks
0
Comment
Question by:CochiseCounty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
12 Comments
 
LVL 7

Expert Comment

by:Lori99
ID: 11823870
You can do this by creating a global variable in your DTS package.  Open the package, go to Package Properties and you will see a Global Variables tab.  Give the variable a name, datatype and default value.  Add a Dynamic Properties task to your DTS package.  Right click on the task and go to Properties.  Click Add.  Expand Connections.. Text File.. OLE DB Properties.  Click on Data Source and then the Property Name.  Click Set.  For Source, choose Global Variable and then select the Global Variable name you set up.  Close all the windows.  

To use the Global Variable when you call the package, you must use the following format for the /A switch.

global variable name:data type identifier=value to use

For example:

dtsrun /NYourPackageName /AYourGlobalVariableName:8=c:\test\testfile.csv

The 8 is the data type identifier for a string.  The other data type identifiers are defined in the SQL Server Books Online under "dtsrun utility".
0
 

Author Comment

by:CochiseCounty
ID: 11824519
Hi Lori99, I am completely new in this subject....Would you please show me step by step how to add Dynamic Property to DTS package? I am using SQL 7.0. Thanks
0
 
LVL 7

Expert Comment

by:Lori99
ID: 11824735
Unfortunately, I don't believe the Dynamic Properties task exists in SQL 7.0 DTS.  I should have asked you before making the assumption you were on SQL 2000.  I don't have a SQL 7.0 installation available to figure out what you need to do and have only done this sort of thing on SQL 2000.  I imagine you would still set up the Global Variable with the file name and pass it to your package, but you will probably need to write some VB Script in the package to use the Global Variable to name your file.  Perhaps someone with experience in SQL 7.0 will be able to provide more information.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Accepted Solution

by:
Jay Toops earned 125 total points
ID: 11824835
IF in fact you WANT to do this I would do the following

1) insert the name of the file into a queue table.
2) invoke the dts package.
3) once the dts package runs delete the entry from the queue table that was just produced.

this is a cumbersome way to do this.


I cant imagine you WANTING to do it though the
I assume you want to send this file to your user? ..

if so I would simply write the data back to the user along the HTTP stream
and change the content type of the stream to "application/ms-download" causing
the browser to pop up a "save as" dialog box.
which the user can then save.

Jay
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 125 total points
ID: 11825073
Personal opinion, if you're doing simple CSV dumps or extracts, I would look at using OSQL or BCP to do it and simply passing your filename as part of the dynamic SQL.  Especially if you're still running 7.0
0
 

Author Comment

by:CochiseCounty
ID: 11825238
I really don't know what I am doing. Let me tell you about my project to see if you can give me some clues...

From the search page, users will enter id and search criteria,
then I have to write those search criteria to the database,
the next step is that I have to export those search criteria to a text file called outbox,
this text file will be used by another program (not created by me) to run a search (from a database (not sql) from a diffrent server),
after searching, the result will be post to another text file called Inbox,
then I have to import this Inbox text file to the SQL database and then display it on the web site.

The web application is on web server called server1 (internet) and the text files must be on server2 (sqlserver, not web server)

I don't have any problem with submit, retreive data from SQL to the web site, the thing I am confusing is the export/import from/to text file. I am thinking of using the id that users enter for the file name, that is the only way for me to tell which Inbox file goes with which Outbox file.


Please tell me which way is the simplest way to work on this project. Thanks



0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12056341
if you don't need to have the file in your sql server database just
read the file from the disk
using the file system object
and on your web page put a <PRE> tag
write all the lines of your file to the screen
then at the end close the </PRE>

Jay
0
 
LVL 34

Expert Comment

by:arbert
ID: 12058536
Weird, I didn't get notification on this question way back when.  Ya, I agree you probably want to work with the user's name to make it unique.

I think I would also look at using XMLservices to do some of this and just use XML files....
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12078234
HI.. could you award points for this?
Id really appreciate it..

Jay
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question