ssis

Execute stored procedure (SQL 2005) and place results into a CSV file on a FTP location

I want to get the results of a stored procedure and place them into a CSV file onto a FTP location. The catch though is that I cannot create a local/temporary file that I can then FTP over. The approach I was taking was to use an SSIS package to create a temporary file and then have a FTP Task within the pack to FTP the file over, but ...

Accessing Excel data source running SSIS package on 64 bit server

Hello, I have an SSIS package that exports data to a couple of Excel files for transfer to a third party. To get this to run as a scheduled job on a 64 bit server I understand that I need to set the step up as a CmdExec type and call the 32 bit version of DTExec. But I don't seem be able to get the command right to pass in the connectio...

Good Starting Places for SQL Server Alerts/Notifications?

Just recently started having issues with a SQL Server Agent Job that contains a SSIS package to extract production data and summarize it into a separate reporting database. I think that some of the Alerts/Notifications settings I tried playing with caused the problem as the job had been running to completion unattended for the previous...

SSIS convert image blob

I have a .net webforms front end that allows admin users to upload two .xls files for offline processing. As these files will be used for validation (and aggregation) I store these in an image field in a table. My ultimate goal is to create a SSIS package that will process these files offline. Does anyone know how to use SSIS to read...

SSIS: Adding a constant column value when doing a CSV to SQL conversion

I am reading in CSV file and translating it to a SQl Table. The kicker is that one of the columns in the table is a type ID that needs to be set to a constant (in this case 2). I am not sure how to do this. ...

Can you start a scheduled Windows task from SQL Server Integration Services ?

Hello, Does anyone know if you can and how to start off a scheduled Windows task on a Remote Server from within a SQL Server Integration Services (SSIS) package? Cheers Nigel ...

How do I get SQL Server Integration Services to use a different database connection for Package Configuration

Hello, I have an SSIS Package that sets some variable data from a SQL Server Package Configuration Table. (Selecting the "Specify configuration setings directly" option) This works well when I'm using the Database connection that I specified when developing the package. However when I run it on a server (64 bit) in the testing environm...

How Do I Check Job Status From SSIS Control Flow?

Here's my scenario - I have an SSIS job that depends on another prior SSIS job to run. I need to be able to check the first job's status before I kick off the second one. It's not feasible to add the 2nd job into the workflow of the first one, as it is already way too complex. I want to be able to check the first job's status (Failed, Su...

SQLServer Native Client Error "Connection Busy With Results From Another Command" in SSIS

I'm getting a "Connection Busy With Results From Another Command" error from a SQLServer Native Client driver when a SSIS package is running. Only when talking to SQLServer 2000. A different part that talks to SQLServer 2005 seems to always run fine. Any thoughts? ...

SSIS copy directory from FTP location to a shared network location

I have the need to copy the entire contents of a directory on a FTP location onto a shared networked location. FTP Task has you specify the exact file name (not a directory) and File System Task does not allow accessing a FTP location. EDIT: I ended up writing a script task. ...

How to insert a row into a dataset via SSIS

I'm trying to create an SSIS package that takes data from an XML data source and for each row inserts another row with some preset values. Any ideas? I'm thinking I could use a DataReader source to generate the preset values by doing the following: SELECT 'foo' as 'attribute1', 'bar' as 'attribute2' The question is, how would I inse...

Integration Services: Multiple-step OLE DB operation generated errors

I'm attempting to make a DTS package to transfer data between two databases on the same server and I'm getting the following errors. Iv read that the Multiple-step OLE DB operation generated error can occur when you are transferring between different database types and there is loss of precision, but this is not that case here. How do I ...

SSIS Oracle Parameter Mapping

When trying to enter a SQL query with parameters using the Oracle OLE DB provider I get the following error: Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command i...

What are the options for extracting data out of Hyperion 7.3 with SSIS

We need to get data out of some Hyperion cubes (databases) using SSIS. Are there any connection managers available for this? Has anyone done this? ...

Using SSIS to refresh a training database from a production database

Hey All, I have a particular system on out network where we need to maintain a training installation. The system uses SQL Server 2000 as its database engine and I need to set up a system for refreshing the data in the training database with the data from the production database on a regular basis. I want to use SSIS as we have SQL 200...

Import *variable record length* CSV file using SSIS

Has anyone been able to get a variable record length text file (CSV) into SQL Server via SSIS? I have tried time and again to get a CSV file into a SQL Server table, using SSIS, where the input file has varying record lengths. For this question, the two different record lengths are 63 and 326 bytes. All record lengths will be importe...

SSIS Package Puzzle

I have an SSIS package that copies the data in a table from one SQL Server 2005 to another SQL Server 2005. I do this with a "Data Flow" task. In the package config file I expose the destination table name. Problem is when I change the destination table name in the config file (via notepad) I get the following error "vs_needsnewmetada...

SSIS: How to progmatically direct work flow in dtsx package

I have a SSIS package, which depending on a boolean variable, should either go to a Script Task or an Email task.(Note: the paths are coming from a Script Task) I recall in the old dts designer there was a way to do this via code. What is the proper way to accomplish this in SSIS? ...

The best way for a SSIS ftp task to not fail when there are no files to copy

I'm using SQL Server 2005, and creating ftp tasks within SSIS. Sometimes there will be files to ftp over, sometimes not. If there are not, I don't want the task nor the package to fail. I've changed the arrow going from the ftp task to the next to "completion", so the package runs through. I've changed the allowed number of errors ...

Potential Pitfalls of inserting millions of records into SQL Server 2005 from flat file

I am about to start on a journey writing a windows forms application that will open a txt file that is pipe delimited and about 230 mb in size. This app will then insert this data into a sql server 2005 database (obviously this needs to happen swiftly). I am using c# 3.0 and .net 3.5 for this project. I am not asking for the app, just ...