Hot answers tagged

8

I don't think there is a better option than scripting your job. You can modify the script as required and reapply it to any number of SQL instances (always backup your system databases before apply scripts).


6

After continuing to dig around I finally found an article in MSDN that covers installing advanced services without installing SQL Server. That got me pointed in the right direction: The Crux of my mistake is that I thought of this as 'adding a feature to an existing instance of SQL Server 2008.' Therein lies my flaw. In the install process there is an ...


4

Yes, it is very much possible. You can define connections to a myriad of external datasources from within an SSIS package. SQL Server, Oracle, Excel, flat files, etc etc. Just remembered one gotcha when dealing connecting to Excel via SSIS in a 64-bit environment. You need the 32-bit client tools installed on your 64-bit SQL box in order to interface ...


3

If the packages are stored in MSDB then no, the only date/time stamp is the creation date. Modified date is not tracked. You would need to delete the package prior to redeploying in order to track a modified date, which is essentially just forcing the create date to be your redeploy date. In SQL 2005, the metadata for packages stored in MSDB is stored in ...


3

disable or remove the foreign key constraints before the data import & then enable or add them after the import eg. remove/add --drop alter table t1 DROP CONSTRAINT fk_1 --add alter table t1 WITH CHECK add constraint fk_1 foreign key (fk) references t2 (i) disable/enable --disable alter table t1 NOCHECK CONSTRAINT fk_1 --enable alter table t1 WITH ...


3

SSIS is on the SQL 2005 installation disk. To install it, just pop the disk in and start the installation wizard. You can then install SSIS as a package, and just make sure you don't tick any of the database engines, or else you'll get another SQL instance installed!


3

Help came from another forum: MSDN forum with correct answer "The SQL Server uses the SID's of the User, not the name. On the other Server the same User has another SID. I suggest to change the Job Owner to "sa". That should work. Later you can change the Job Owner back to CBMIWEB\johna."


3

You cannot run SSIS package on a release that is below the developed level of the package, so it is not possible to deploy from SQL Server 2012 to SQL Server 2008 since SSIS packages are not backwards compatible. This the SSIS engine issue. See more here: Interoperability and Coexistence (Integration Services) From the documentation: On an instance of ...


2

I used the "Script Job as" and selected "Create To" and "New Query Editor Window". Once it was in the query editor window, I changed the name of the job and ran the code. It created an exact copy of the initial job on the same server. Success!


2

You may find the SQL Server Integration Services Task, Copy Job to be of use. http://msdn.microsoft.com/en-us/library/ms137568(SQL.90).aspx


2

Try the 'ragged right' option mentioned here : http://weblogs.asp.net/guystarbuck/archive/2008/01/31/ssis-flat-file-export-quot-fixed-width-quot-vs-quot-ragged-right-quot.aspx


2

you can use freeTDS (or jTDS if you use that thing called java) to give you access to your sql server from Unix/AIX. From FreeTDS you can authenticate against SQL Server & run any T-SQL you like. You can "trigger" the SSIS job by calling the sproc sp_start_job which resides in MSDB to kick off the job by name or job_id.


2

There is an a destination for Access database, as long as the Access database already exist. You would add your SQL Server 2005 and the Access database to the connection manager. Then chose the OLE DB source and destination within your data flow. It will allow you to chose the Access connection as your destination. Now if you are wanting to create the ...


2

The projects themselves are not stored in MSDB, only the compiled packages. You have to have an existing SSIS / BI project in your file system / source control / etc in order to open/edit or deploy. If you don't have the original project you can import an existing package into a new project. Create a new Integration Services project In the Solution ...


2

Yes, you can transfer data between 2005 and 2008. The big gotcha you need to look out for is that the SSIS packages themselves can't be moved between the two. If you create the package in 2005, it needs to run in 2005. You can convert the package to 2008, but then it needs to run in 2008.


2

The problem turned out to be very specific to the way we execute all packages at our site. We use a middle tier service to execute packages and it passes in a few variables as it does so, one of those variables being a connection string to the database the package talks to. The problem was simply that when the package opened the VERY FIRST thing it did was ...


2

Assuming these "external URLs" are actually resolving to local addresses, and again assuming that you don't have any DNS issues, no, this really shouldn't make a difference at all. As for high network activity, what exactly are you seeing? EDIT I'm taking a wild stab here (as I know very little of SSIS and the internal workings of MS SQL in general), but ...


2

If both instances are on the same system, the only performance "hit" you'll see when using the fully-qualified domain name is for the DNS lookup. Assuming your DNS server (or hosts file) remains accessible, the performance impact of this will be negligible. IP will use the system's loopback interface when accessing those FQDNs, so the external network won't ...


2

You need to export the package back to a file to be able to edit it. There isn't a way to edit the files which are stored in the MSDB database. If memory serves you should be able to right click on the package in Management Studio and export the package. Then add it to the project and you can then edit it.


2

After good suggestions made by bilinkc, and without knowing where bottleneck is I would try another few things. As You already noted, You should work on parallelism, not processing more data (months) in the same dataflow. You have already made transformations run in parallel, but source and destination (as well as aggregation) is not running in parallel! So ...


2

Solution to your problem is to use different subnet for the second interface than the interface used to connect the application server. So that both the communications i.e source to application server and source to database server will be isolated from each other.


2

I had this same issue. After some research and convincing the server I didn't have control over to add a Windows Authenticated account to there location and some insight from the below URL, I got it working: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3f51de7b-52c2-471e-811b-d056f707f5bd/what-is-the-difference-between-job-owner-run-as-and-log-...


2

As I understand it, a copied file is considered to be a new entity, and therefore its owner will be the user or process that created it. Interestingly, a moved file will retain all of its attributes. If moving (rather than copying) is an option, that should work for you. If not, you'll have to find a way to change the attributes on the newly copied file. ...


1

It seems that this is just not possible. After wasting about 10 hours or so of our "Microsoft Premier" support hours, the only answer that keeps coming up is "execute dtexec remotely on the machine that has SSIS". So I'm going to post this as the answer in hopes that some SQL MVP will show up and prove me wrong. As far as how to do that, I'll probably take ...


1

To find out edition of SSRS, go to: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Log on the server which has SSRS Open the Summary.txt file. Look for the log file for Product : Microsoft SQL Server 2005 Reporting Services. In the SSRS log file, search for keyword "edition"


1

You could forget about SSIS and create a linked server and use stored procedures in a job step instead. SSIS can be overkill and time consuming for most SQL to SQL transfers.


1

SQL Server 2005 Express (and Workgroup) Edition BI Development Studio only includes the Report Designer.


1

Did you command window authenticate to the FTP server with the same username/UID as WinSCP used? Were you working in the same server directory? Almost certainly you will find a difference in one or both of those items, and that difference will help show where the problem lies.


1

I'm not sure what the AUTO statement does, but you are probably aware of the Maintenance Cleanup task. Does it not do what you need? I like powershell for things like this as well. (get-item -path "d:\perflogs\*" | where {$_.creationTime -lt ((get-date) - (new-timespan -days 30))} | where {$_.extension -eq ".blg"}) | remove-item


1

I dont know much about importing different collation types but have you tried exporting the table into say a .csv file then once that is complete import it into the destineation table. Not the most efficient way to do it but may be a work around.



Only top voted, non community-wiki answers of a minimum length are eligible