Solved

SQL 2005 express with SSIS

Posted on 2009-05-14
6
529 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 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now