Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2005 express with SSIS

Posted on 2009-05-14
6
Medium Priority
?
539 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
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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

971 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