Solved

SQL 2005 express with SSIS

Posted on 2009-05-14
6
533 Views
Last Modified: 2012-05-07
I would like to somehow be able to create an SSIS package with my Express version.  I have a good amount of data that i would like to upload.  Is there anyway i can do this w/o upgrading to the full version?  

0
Comment
Question by:ansonindustries
[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
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391667
>>Is there anyway i can do this w/o upgrading to the full version?  <<
I am afraid not.  Even the SQL Server Advance Services does not appear to support any variation of SSIS.  See here:
Features Supported by the Editions of SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms143761.aspx
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 24392132
Developer Edition ($49 USD) is Enterprise Edition one seat not to be used for production.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24393995
If you just want to upload data you can use BCP or BULK INSERT. You can then use stored procedures to process the data.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:ansonindustries
ID: 24395261
i knew it was to good to be true.  lol  

well nmcdermaid, are there any good examples that i can use for BULK INSERT and the procedure you are referring to.  This is what my table looks like:

1. projdataid, (int, not null)
2. projectid,  (int, null)
3. costzone,  (varchar(10), null)
4. costcat,  (int, null)
5. costcode, (varchar(5), null)
6. quantity, (decimal(8,2), null)
7. qtyunit, (varchar(5), null)
8. totalhours, (decimal(8,2), null)
9. changeorder,(varchar(1), null)
10. complete, (varchar(1), null)
11. uploadid, (varchar(21), null)
12. created_by, (int,null)
13. created_date, (varchar(21), null)
14. created_ip (varchar(21), null)

1. is it important to have have values placed in the "BULK INSERT" command for each column heading above?  my goal is to let the projectdataid auto number itself since i would not know the next sequence number in the talbe unless i manually look it up.

We have a web based app that the user can manually choose values from options 3-8 above.  the rest of them are computer gen.  I would like to create an upload file that will perform the same type purpose my BULK statment should look something like this

BULK INSERT FROM c:\upload.txt  
( , , 9800, 944, 000, 10, LF, 40, Y,  , ,  ,  ,)

I would like to leave the other columns blank.  is this possible?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24404277
BULK INSERT is for inserting data into a database from a flat file. If you have people entering data from a web app then you should be entering data directly into the database. You shoudn't be going via a flat file at all, unless there is some other design parameter which I am unaware of.
i.e.
INSERT INTO YourTable (costzone,costcat,..,..)
VALUES ('X','Y',....)
As fields 9-14 are nullable, then yes, if you leave them out of the insert statement then no problem, they'll be blank.
To create an 'autonumber', make the projdataid an IDENTITY field. You don't need a sequence number table. You can use the SCOPE_IDENTITY() function to check the identity that was just created if you need it.
Assuming that there is some reason for needing to import a flat file, then you use a format file to tell BULK INSERT to ignore certain fields in the import table (i.e. 9-14)
If you can post your table creation script and a sample of your flat file, it will be quicker for me to show an example bulk insert statement, and example format file.
Here is an example anyway (straight from doco)
BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml')
 If you need to do any processing after the file is uploaded then you can use a stored procedure as well.
0
 

Author Closing Comment

by:ansonindustries
ID: 31581675
that was the easiest route.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 73
Get row count of current SQL query 8 63
Analysis of table use 7 62
SSRS: Why is Visual Studio stripping these properties? 2 39
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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