Solved

SQL 2005 express with SSIS

Posted on 2009-05-14
6
531 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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