Top Solutions

Hi all

In SSIS, I am trying to parse together a string (hard-coded) and an double (column) as another column, with this expression:

"Debits are " +  [debits] +", and credits are" +  [credits] +", difference is " +  [debits] - [credits]
...

Hi
I have an SSIS script task that builds up a variable that is a SQL server statement. I need ot be able to execute that statement in the script task.

Here is the code I have

        Dim oCnn As New Data.SqlClient.SqlConnection
       ...

I had some DTS packages that I had used the migration tool in SQL 2005 to convert into DTSX.  However I am not able to see the any of the DTSX files in my SQL 2005 Server.  So what I am trying to do is take the DTSX files and import them into a...

Hello,

I have a simple SSIS master package ,whose main task is to run other packages through the execute package tasks. The problem Iam facing is this. It runs fine when I run the package via BIDS but I get the following errors when I set up a...

Hi,

I ran the job and to execute a SSIS dts package in sql 2005 server, I got the following error message:

Thanks for your help!

Date            1/25/2008 4:52:00 PM
Log            Job History (Survey Returned Weekly)

Step ID            1
Server            GSBHDB3P
Job...

I am trying to install Pervasive PSQL 10.10 client drivers on to a windows 2008 x64 server. The install completes with no issues, however there are no ODBC drivers installed. Also the Pervasive "Control Center" isn't installed either. I tried...

I have a SQL Server 2000 DTS package that pushes data to the AS400.  The package runs perfectly when run manually.  When the package is run as a scheduled task on the server, it fails and logs the following error message:

[IBM][iSeries Access...

How to generate SQL script to DROP all triggers
How to generate SQL script to CREATE all triggers

Hi,

I was changing some settings in my sql server 2005 Configuration manager. Settings like Enabling TCP/IP, so change of settings asked me to restart the sql server. BUT when i have stopped and then gaian trying to start it its giving me the...

Hello,

Could someone please help me here, new to SSIS and ETL stuff, I have raw data being staged to a SQL table, which I then want to move to our production table and update/insert the record set based on a unique key.

The staging table...

Hi,

I am using BCP to export SQL table to CSV and it works great. but it doesn't include the header - Column name. I need to include it ;-(

Below is my T-SQL.

Thanks!

Hi,

Can i make SQL server 2000 work on Winodws server 2008 STD ?

Thanks

I am attempting, in vbscript for a DTS package, to create connection and command objects and assign parameters to the command and execute the command.  Iin the Immediate window, debugging, I can see the properties for the command object being...

I have created a SSIS package that works just fine from my desktop. I am now testing it by deploying it to the server. The task was imported into the "File System" store. I connected to the SQL Server, from my desktop, with SSMS. I right clicked...

Concerning MS SQL 2008-
I've used the Copy Database Wizard in SQL Server Management Studio to move a database.  It creates a DTS package on the server.  I don't see how to edit the DTS package from within Server Management Studio.

I can...

I am having a hard time with this one.  I'm in SSIS and have set up ODBC drivers and it's not working.  I'm getting this error in connection manager:

"Test connection failed because of an error in initializing provider.  Oracle client and...

I have one textfile containg data in utf-8 format. When I make a new package in the designer i choose codepage 65001 (utf-8), and in the preview window all the characters looks correctly. When I import the file into SQL server and look at the...

Hi,

I am working on MS-SQL 2005,
Like in MS-SQL we can directly import the Excel file into the table of database using wizard

Is there is anyway in MS-SQL-2005, where we can import the EXCEL file directly using stored procedure. My Excel...

I have written an application which programatically imports data from an Access database into a SQL Server database using DTS. The program runs correctly on my own computer but when I run it from a computer that does not have SQL Server...

I am in need of some assistance.

• I currently use MS Access thru an ODBC driver (Cache) to get to customers database to retrieve data

• I need to run a report for several years worth of data that will bust the 2GB MS Access limit
• I’d...

There are standard functions to get year, quarter, month, etc out of date field ( DATEPART functions). In Norway though we use often trimesters (i.e. 4 month periodes, 3 in one year). Does anyone have an idea/function to extract that out of date?

I am trying to bring in data from Lotus Notes db into SQL Server db. In SQL Server, There is a field xyz [varchar](8000) NULL. After declaring the DataReader Source as Notes in SSIS, I used Data Transformation to first convert unicode into...

I am a total noob to SSIS...I am creating a new package that takes data from an OLE DB source and overwrites the data in another OLE DB source.  I set up my connections and dataflow tasks, but how do I truncate the destination table and import...

I need to update a variable as a counter and assign that value to a field in my data flow.  I get an error stating that the arguments for LockOneForWrite are invalid.

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
  ...

I am loading data into SQL Server using DTS.  That seems to be working fine.  Many of the values are in scientific notation, ie. 1.00E-005, -6.63E-011, etc.   I set the decimal datatype with precision 28 and scale 14 to handle storing the values...

We have a DTS package that is running on a SQL 7 server fine.

When we wish to open the DTS package to look into what it is doing (or edit it!), parts of the package error.

The main package window opens fine, however, when we click on any of...

My issue is having installed SQL Server 2005 integration services (both x64 and 32bit) I an encountering a very strange problem when I try to execute a package.

What occurs is the package is executed, whether it be via BIDS, SQL server or...

Hi Experts I have designed an SSIS Package Which reads data from a Flat file and loads it into MS Sql database for this I have taken a For each loop to find the Flat file from the folder and attached a DataFlow to it and in that Data flow I have...

We are running SQL Server 2005 on a Windows 2003 OS.   I am trying to find out
if a product exists that would let us backup a database while excluding the
data in certain table.  The table is a large audit trail table that is not needed by our...

I am new to SSIS. I hope this is simple.

I have a flat file source that I am loading into a RecordsetDestination object.  I want to use the first field in the record set as the value to perform an Execute SQL SELECT statement to query records...

I am trying to load a set of values from a MS SQL Table into an array - but everytime I use code I have found on the net to do this I get the following error "Cannot find table 0.". I am even more confused because apparently Table 0 does exist,...

Hello Experts,

I have developed an SSIS package on my production machine and ran it with no problems.  
However, when I schedule the package to run (and set ssis logging on), I find that it fails with the following error:

"Version of...

hi
When I run a openrowset in T-SQL I recieve this error:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"

I am trying to open a text file in T-SQL.

Please help me. I am in a tight schedule...

Hi Experts,

DTS today failed with error below.  It has been working fine until today.  Can someone please advise to resolve this error.

Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services...

On a Windows 2003 server there are mutliple errors in the event log as seen below. If I open component services under administrative tools then expand Computers, the My Computer icon has a red down arrow on it, indicating that DCOM is stopped I...

I have a SQL Server 2005 database hosted with a third party. I want to create a copy of the whole database, including schema, data, stored procedures - everything. This copy is to be used locally for development and testing purposes.

The...

how to pass the numeric(12,0) data type to variable in SSIS? what kind of variable data type should I choose?
if i select int32, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to...

Im trying to Connect to Oracle Instance from my ActiveX Script Task using ADODB Connection. Created a System DSN with the Oracle Client Driver installed on my server (Windows 2000 & SQL Server 2000).

The system DSN is fine, tested it several...

Hey,

I'm a complete noob in Visual Studio so this might be a silly question...

I have to debug SSIS and I have the SSIS package which I'm trying to open in VS2005; however, if I run the .sln file I get an error that the dtproj is not...

Hello Experts,

I have a report in SSRS that im trying to setup as a subscription  If i put my email address in the TO fields
then at the time of subscription im getting an error like "The e-mail address of one or more recipients is not valid...

Is it possible to extract plain ASCII text from a SQL Server IMAGE field?  

I have the following code in a T-SQL script and it works well to put asterisks in to hide the code number.

SUBSTRING(columnA, 1, 1) + REPLICATE('*', Length(columnA) - 5) + SUBSTRING(columnA, Length(columnA) - 3, 4)

I have to update the DTS...

what is the simplest way to send email through a webserver (has windows 2000 server installed  and running Ms Outlook as email client) that has Ms Sql server 2000 through sql server by specifying receiver and sender email ? Thanks in advance

Hi guys,

i am creating a temporary table and then I need to add columns dynamicaly depending on 2 date ranges that I specify. is this possible?

say example, i want to give the query '20080805' and '20080810'
the query will add 5 columns,...

Hi,

I have a stored procedure on SQL Server 2000 that I would like to run from Excel.

The sp doesn't return any data to the excel spreadsheet it just updates tables on the SQL Server which i then linked to via pivot tables in Excel.

What...

Dual xeon 2.4
4.0 gig of memory.
Plenty of free space.

I am receiving several error messages what do you think that they mean?

Event Type:      Error
Event Source:      qsa
Event Category:      None
Event ID:      100
Date:            7/2/2007
Time:            1:00:25 AM...

I have a ms sql 2005 SSIS package with an execute SQL task running a stored procedure. Initially the sp had no parms and I got the package working correctly.

I have now changed the sp to accept two parameters

"ALTER PROCEDURE...

Hi Expert,

I am trying to insert the IIS log into a SQL Server for data analysis.  I have read from websites that the log parse would allow inserting the log into DB.  But as I tried it myself, I found 2 problems.  

The first problem is...

Hi,
I am trying to export data from SQL Server 2000 in to an Excel spreadsheet using DTS. Everything gets exported except the datatype of the columns. i.e a Decimal(18,3) column in sql gets exported to excel as 'General'. But a datetime column...

G'Day,
  We have a client running Windows Server 2003 with SQL Server 2000.  When they try to access the MSDTC service from a web server (on another server), they get various errors.  Testing with the dtctester tool gives the following results...

Hello -
I am attempting to migrate some DTS packages from SQL 2000 to SQL 2005. I have used the wizard and migrated them to a dtsx file and I am attempting to use xp_cmdshell  when i run the following command-

EXEC master..xp_cmdshell...

I have a DTS package of SQL server 2000 which has two "Execute SQL Task", which gets maximum ID (call it maxID1) from table1 in mysql and maximum ID (call it maxID2) from table 2 in sql server.  Those 2 as global variables passes into a data flow...

Hi Exports,

I am transforing data oledb source to excel sheet destination

oledb source-- Derived colu-- excelsheet
I am getting error in Excel sheet destination  only can u any body help me
TITLE: Package Validation Error...

I am using script component in the SSIS and vb script
I am getting this error.

The value is too large to fit in the column data area of the buffer

Any help is really appreciated.
Thanks

Hello experts

I created an SSIS package on my IS Server logged in as Server/Administrator.

I have a proxy account set up with Server/Administrator credentials that is used to run the package. Server/Administrator account has sysadmin...

Hi

I'm trying to get a SSIS package to run from a sql server agent job but it won't becase I heep getting the below error. I can run the package manually but it won't run from a job and I get the same error when I try to execute it from a DOS...

Hi,
I have many SSIS packages set up on SQL SERVER 2005.
I am using Visual Studio 2005 Premier Partner Edition SP1.

I have a MYSQL db which i connect my SQL SERVER to using different linked servers.

I first create a View on MSSQL and...

Hi Experts,

I am junior Sql DBA. I have migrated a few Databases from sql 2000 Sp4 on windows 2000 server to sql 2008 on windows 2003 server. I had no issues during migration, when i ran the upgrade advisor i found some issues with DTS...

i run two sql 2005 machines with replication between, and when i setup a subscription it says in the job history:

Executed as user: domain\adminacct. The replication agent encountered an error and is set to restart within the job step retry...

Hello,

Lots of questions the past couple days trying to do a LOT of new stuff with SSIS so thanks all for help so far.

I created 3 packages that I am currently running manually and they work great.  I run each one manually/in order and I...

I'm trying to schedule a SSIS job to run nightly using Timberline as a datasource.  Here's my issue:

The Timberline ODBC driver has a constraint which will only allow the use of drive letters when pointing to its source.  The driver will not...

Rookie mistake.

Normally I save my import packages to the file system.
However, I have saved a few to the database and now, don't know how to find them within SQL SErver 2008 R2.

Where can I find them using the Management Studio?

I would like to create code that would enable me execute an AS400 job from an SSIS package. I want to write this code in VB.Net or C#

I currently have DTS processes in place which pull data from as400 tables to SQL 2000 (sp4)tables via Client Access ODBC connectivity.  First,  I run a truncate job, prior to the DTS package execution, which clears the tables on the SQL server.  ...

Hi experts, I have scheduled a package and it ran fine for couple of days, since past few days it is giving me the below error, the same job ran fine on two other diff servers, please help. when I look up online about the code there was a...

I'm trying to create a stored procedure in MS Sql Server 2008 R2 and I'm getting an error:


Msg 170, Level 15, State 1, Procedure usp_getDocsLocalReg, Line 18
Line 18: Incorrect syntax near 'ASC'.

On the sql below. I just started using...

I have a massive text file with consultant data for my company that I have to synchronize with records in SQL 2005.  The text file uses ~ as the column delimiter and carriage returns for the row delimiter.  There are no header columns in the text...

I have a process that is running in SSIS that takes an ODBC connection and saves the data to a Flat File Destination file.

The file is generated just fine but I need to add a "static/hard coded" Header AND a Footer to the file.

I should...

IF THEN Main = DTSTaskExecResult_Success
ELSE Main = DTSTaskExecResult_Failure

on failure .. it get an task reported failure on execution error ..

i have 3 activex script .. 1 main, 1 that need to start on succes .. and 1 that need to...

I am trying to rotate 186 columns into rows. For eg, what I have is like this:

product    date1    date2     date3
A              100      100          200
B              200      200         150

And I want to make it look like this:

A...

Hello Experts.

I have an SSIS project that has an Execute Task, which calls a stored procedure.  
The stored procedure has a number of elements to it and takes a long time to complete.  

The procedure completes with no errors and I can...

Is there a way to import a tab delimited text file into a database through a stored procedure?

Hi,

This is what i am trying to achieve..

Create a CSV file delimited with pipes (|) without header definition, name the file according to a business rule and save it in a remote location..

I have heard a little about SSIS .. but nothing...

Never encountered this before.
Have a table (commercial application so cannot change datatypes).

Column ("document number") is data type FLOAT
Document number never exceeds 7 digits (at least yet) and there are never any decimal places in...

I found the web page Running a Package Programmatically on the Local Computer that explains how to call an SSIS package from C#.  So far so good.

I need to reference Microsoft.SqlServer.Dts.Runtime in order to use the SSIS classes.  However,...

I am trying to build an SSIS package that accesses Active Directory to check for AMAAccountNames.

In SSMS I can run this statement (using a linked server)...
SELECT * from openquery (ADSI, 'select sAMAccountName from ''LDAP://...'' ')
It...

Hi Experts,

I have a flat file source like this.
"ES";"ITEMGROUP";"299919088880";"Reusable Other";"Standard";""
I've created in SSIS a flat file connection in connection manager with this text qualifier ".
In the preview the data looks...

Hi,

We are trying to do data conversion using SSIS convert from table A to table B.  There is multiple columns in table A need to do a LOOKUP on table C.  To do this, we use a lot of components like ‘condition split’, LOOKUP etc.  So far so...

Hi,

I am running sql server 2005, all the database backups are fine. I have around 20 automated jobs running in the sql server agent. Does anybody know how to back these up? At the moment the only way I can see is to copy each query into a...

when I am using this syntax:
select * FROM         T_MAIN_REQUESTS
WHERE     DR_sys_dt2 = '2008-01-03 00:00:00.000'
RESULT:1000 records
when I use this syntax
select * FROM         T_MAIN_REQUESTS
WHERE      DR_sys_dt2 = getdate()
RESULT:0...

Hello, can someone please suggest to me how I can keep my ftp connection from timing out after a large file download?  

I am using SSIS and I have tried multiple ways of ftp'ing the files in my directory to my local hard drive, but the tasks...

HI

I have a package that runs well on my local machine. But when i create the DTSConfig and Deploymentmanifest file and run it (even on my own machine) , i get the above error .

It also said there maybe error messages posted before this...

I have an Execute Process task within a SSIS Package, where I am executing gpg.exe.

My StandardInputVariable is User::Passphrase (which contains my Passphrase)

My Arguments string is as follows:
--passphrase-fd 0 --output...

i have a server that runs sql 2005 and the logfile has grown ridiculously high, which means the logfile is now 80gb.

i have a dev server with sql 2005 on which the logfile is 10gb.

i want to shrink and i was told to simply run a SHRINK and...

I have a fairly simple star.  7 dimensions that are easily set up.  Fact table with various dates and counts.  It contains customer ID  Customer is a dimension with primary key customer ID.

There is a demographic table.  I have denormalized it...

I have a string that I have read from a text file (DT_WSTR) and I need to change to cast it to a currency (DT_CY) in SSIS. I tried the usual (DT_CY)[Cost] but that doesn't work and the error just says the cast failed?

Any suggestions?

We currently have a database with company information (prospects, customers, etc.). We are more and more commonly receiving lists of companies to import into this table so we are in the process developing a way to weed out companies that are...

Hi All,
Is there a way to use the Fuzzy lookup in Standard Edition of SQL Server.
We require this functionality in our SSIS, but we have a constarint of having Standard edition of SQL Sever on our deployment environment.
So is there an...

Hi, can I have some sample code to execute a SSIS package (stored on the file system, not in database) using DTEXEC utility programmatically using C# OR VB.NET and also some sample code to kill that process if runs unexpectedly for a long time?....

I have been given a connection string which I am told will allow me to connect to a SharePoint list. I wish to connect using DTS in SQL and work with data in the sharepoint list. Can you advise how to set this up? I do not have much experience of...

I have a DTS PAckage that exports TANDEM Data to EXCEL Files. So I have to Work around converting Datetime etc Fields and finally get this into EXCEL. but my main problem starts here is the Column Widths. Users have asked me a thousand times if...

Im using the SQL Server Import and Export Wizard that comes with SSIS.  The data source is ibm's 'IBM OLE DB Provider for DB2'.  I believe the MappingFiles that it should be using is 'IBMDB2ToMSSql.XML (see attached 'text' file; the...

how can i send files from the source system to host using sftp

the solution i wanted in either with stored procedure
or ssis

Can I copy all the users in one database to another (on the same server)?  I swear I've done this before but I cannot find my notes.

Hi,

I have stored procedure that called the dtsrun.exe but the command-line does not overwrite my global variables.

declare @cmd varchar(50)

set @cmd = 'DTSRun /S "(local)" /U "user" /P "pass" /N "test"  /A "one":"3"="1" /A "two":"3"="2"...

I have task that were made on SQL 2005(with server 2003) and worked fine, but when im trying to deploy and execute task on SQL 2008(with 2008 server) im getting following ...

Description: Failed to load task "OLAP Synchronizer", type...

why can sql server only display 100 result sets for

sp_msforeachtable 'select ''?'';select * from ? order by 1'

Hi,

We are using SQL Server 2005, and I like to know how can I deploy a SSIS.dtsx package on the server and schedule the job to run the package?

Thanks,