How To Do A Simple ETL Task Using SSIS?
Apr 24, 2008
Hi
I am new to SSIS. I wanted to do a daily ETL load from sourceA to targetB database
Please let me know the best method to that i should follow to do the below task
1. Get the Yesterday's date or last months's from-date & to-date dynamically.
2. Pass the from-date and to-date to my source SQL [e.g select emp_name, emp_dept from emp where hiredate between @param1 and @param2]. Acually my source sql has subquery and the parameters has to be passed to the subquery's where clause.
3. Insert the resultset into a staging table in the targetB database.
4. Join this targetB table with another table in sourceC db
5. finally load the result set in to the targetT database table
6. How to configure/schedule this package so that it runs daily, automatically pass the yesterday's date to the package.
I have done this using Oracle PL/SQL but my requirement is to do this in SSIS. I am not sure which control flow or transformation items to be used to do this in an effecient way.
Please guide me.
View 4 Replies
ADVERTISEMENT
Dec 8, 2006
I was creating an SSIS package, seemingly simple. One of the tasks I needed to do involved copying our production database to a seperate DB instance for staging. I wanted an SSIS package that would stage production so we could push and test changes.
My first problem is that I tried to create a simple Transfer Database task. When I tried to run the task, I got an error stating that our Stored Procedure, DtsRun, could not be scripted. It's an encrypted stored procedure.
A little annoyed, I decided that I'd do a transfer SQL objects instead. Now I don't want to hard-code my objects to xfer based on the current schema and edit this SSIS script every time we make a DB change; I just want it to copy ALL objects except for my encrypted proc. I'll just build a list with with another task.
I decided that I'll write a script that generates a list of proc names, and I'll pass that to the Copy SQL Server Objects task. Well apparantly I can't assign a collection value with a script. I have to create ANOTHER package that modifies THIS package because (1) The entire package isn't exposed to the script task and (2) object variables can't be used in expressions.
Am I missing something? So because I use a single encrypted stored procedure, I have to write 2 fairly complex packages to copy my database? That's just stupid. I can understand if SQL can't move the encrypted package while the database is online. I could deal with that. But the extremes I have to go to JUST to copy my database are truly rediculous.
I was really excited about SSIS, but I've been really dissapointed. The performance of the designer on my machine is really poor (I have a Core T2700 with 2GB RAM). Just about every error message I've gotten, both design-time and run-time, I've had to google because they're so obscure. And if you can only store blittable types and strings in variables, that severely limits the functionality of a package (without going to programmatic extremes).
Most of all, I'm really dissapointed in the expression system, If the CLR is loaded into the SSQL/SSIS runtime, then why am I coding it using ANOTHER proprietary coding syntax???? I thought .NET was meant to keep people from having to redesign the wheel???
Microsoft is constantly pushing C#, yet I can only script in VB.NET, which means I now have code-bases in different languages.
Overall, I'm really dissapointed with SSIS. The usefulness of the added functionality in SSIS packages is shrowded by the massive list of nuances.
View 8 Replies
View Related
Apr 10, 2008
Say I have 10 tables that need to be loaded from one database to another. There is minimal transformation from source to target and it's pretty much a column to column mapping.
Would you make 10 separate packages, each one loading a specific table, or 1 package that loads all 10 at the sametime?
My inclination is to make 10 separate packages because there mostly will be foregin key constraints on some of the tables. And if I created 10 packages, I would make a 11th package (I guess some people call this the master package) that sequuences the run order for the 10 individual ones.
In this scenario, say I need to have a configuration file that sets the environment (dev vs prod). Would you make a confiuration file for each of the individual packages (this would be repetative, setting the same name and value 10 times) or is there a way to have a configuration for the master package where you would set the value just once?
View 8 Replies
View Related
Feb 21, 2008
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
View 17 Replies
View Related
Feb 1, 2007
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
View 9 Replies
View Related
Nov 7, 2007
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
View 5 Replies
View Related
Feb 26, 2007
I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?
'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")
with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update
.Subject = "Notification"
.From = "donotreply@test.com"
.TextBody = "TEST"
.Bcc = "someone@test.com"
.Send
end with
'-------------
Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.
Any help would be appreciated.
View 2 Replies
View Related
May 28, 2007
I'd like to transfer some records between the following 2 tables. Surely this should be a no-brainer - what am i missing that is making this so impenetrable?
I am currently: Hoping someone can help me get here: (this is my first time of using SSIS btw).
here is the source table (MS Sql Server 2005, SP 2)
CREATE TABLE [dbo].[imagine_divs](
[div_mnemonic] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_date] [int] NULL,
[div_amount] [float] NULL,
[div_status] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_curr_mnem] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_upd_date] [int] NULL,
[div_payment_date] [int] NULL
) ON [PRIMARY]
here is the target table (Oracle 9i)
CREATE TABLE myschema.imagine_divs
(div_mnemonic NVARCHAR2(11),
div_date NUMBER(*,0),
div_amount NUMBER(20,5),
div_status NVARCHAR2(16),
div_curr_mnem NVARCHAR2(11),
div_upd_date NUMBER(*,0),
div_payment_date NUMBER(*,0))
/
I used the SSIS Import and Export wizard to copy data between the two tables, and attempted to execute it. I use Sql Native Provider on source, and Native Ole DBOracle Provider for OLEDB. however, I get an error:
[Destination - IMAGINE_DIVS [37]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12571: TNSacket writer failure".
I notice that the wizard has created a data flow task with 3 steps: source - imagine_divs, Destination - IMAGINE_DIVS and "data conversion 1".
Data Conversion 1 seems to be taking my source nvarchar columns and converting them to DT_STR with twice the size (for example div_mnemonic become DT_STR, size: 22).
If I change the mappings in the OLE DB Destination Editor, such that only the numeric and date-typed columns are included in the transfer, it works fine.
If I include any string-typed column in the destination editor mappings, I get the TNS Packet Writer error. If I remove the Data Conversion step and connection teh source and destination tasks directly, i get validation errors saying that:
Error 2 Validation error. Data Flow Task: OLE DB Destination [294]: Columns "div_mnemonic" and "DIV_MNEMONIC" cannot convert between unicode and non-unicode string data types. Package4.dtsx 0 0
this is despite the fact that everything is unicode here (right?)
what in the world is going on?
View 4 Replies
View Related
Sep 4, 2007
Has anyone done this? I can't find anything in the documentation
that describes this. The closest I get is to the InnerObject property
of the TaskHost class. There is an example of programming a bulk
insert task. But I can't find anything on programmatically setting
the column mappings (source to dest) of a simple data flow task. Any
help is appreciated!
View 7 Replies
View Related
Feb 26, 2008
I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1
According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?
I need to get a reference to the task host in an SSIS Task component.
Basically the scenario is this:
I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.
What I did is this:
I've implemented IDtsTaskUI and during the initialize section I wrote:
Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
' Store the TaskHost of the task.
Me.taskHostValue = taskHost
Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
myTask.myTaskHost = taskHost
End Sub
My Task is named: CustomTask. I have a public variable in my task as follows:
Public NotInheritable Class CustomTask
Inherits Task
Implements IDTSComponentPersist
Public myTaskHost As TaskHost = Nothing
Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.
Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.
I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI
How do I solve this?
View 10 Replies
View Related
Aug 20, 2006
Hi All, i am just started to studying some material on SQL 2005. A question came across my mind regarding the SSIS 2005.
Can my .net 1.1 web application trigger the SSIS 2005 package? if yes, how? The SSIS 2005 class library is compatible with .net 1.1? Appreaciate if anyone can help to clarify this. :)
View 2 Replies
View Related
May 2, 2006
I know this is a simple question (hopefully).
I am pulling data from an AS400 DB2 mainframe and copying it into SQL Server 2005. For one table the date is stored in three columns (TDAY), (TMONTH) and (TYEAR). I am first using Data Conversion to convert these columns into string format (as opposed to numeric) and then creating a Flat File.
I then want to take this Flat File and copy the columns into SQL server, however I want to "merge" those three columns, create a date and store that into one column in SQL (TDATE). So is this where I need to use a Derived Column?
Thanks for any helpful information.
View 3 Replies
View Related
Mar 27, 2007
I have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
View 3 Replies
View Related
Sep 20, 2007
Hello!
I want to make a very simple package: Export all rows in a table to a flat file.
This package I can create pretty much by only using the wizards.
Now to my problems:
1) I need the output to have this format:
H20070920161522
DS3 Plastpall trippelkrage 40 1
E00000000003
H is a header post, in this case with date and time following.
D is a details post, that is all the rows that was exported.
E is and end post, containing only the number of rows in the file, including H and E posts.
2) I need to set the file name dynamically, preferably using date and time to name the file.
I´ve done this very same thing in T-SQL, like so:
Code Snippet
USE AVK
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
SELECT *
FROM tempProducts
GO
CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
UNION ALL
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1
GO
IF @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
SELECT *
FROM tempProducts
DECLARE @date char(8)
DECLARE @time char(8)
DECLARE @sql VARCHAR(150)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql
--WAITFOR DELAY '0:00:10';
DELETE
FROM tempProducts
COMMIT TRANSACTION
END
DROP VIEW EXPORT_ORDERS
GO
But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell.
Pointers please
View 5 Replies
View Related
Dec 26, 2007
Why isn't there some documentation on how to do this. This should be really simple and it has taken me 2 weeks and I still haven't gotten an answer. Please Help Does anyone know the answner or some place where there is some documentation!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I get the following error when I try to substitute the strings in the databasedetails collection with variables:
Error: Object reference not set to an instance of an object. StackTrace: at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus(Database srcDatabase, DatabaseInfo dbDetail) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSpAttachDetach()
I created the following variables:
strDestinationDB = AirCL2Exp_new3
strDestinationDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.mdf
strDestinationLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.ldf
strSourceDB = AirCL2Exp
strSourceDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Data.mdf
strSourceLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Log.ldf
I then assigned those variable to DatabaseDetails Collection:
DatabaseName = @strSourceDB
DestinationDatabaseName = @strDestinationDB
Inaddtion I also assigned the following to the two DatabaseFiles Collection:
for 0:
DatabaseFileSize = 0
DestinationFilePath = @strDestinationDBPath
FileType = DatabaseFile
SourceFilePath = @strSourceDBPath
SourceSharePath = @strSourceDBPath
for 1:
DatabaseFileSize = 0
DestinationFilePath = @strDestinationLGPath
FileType = LogFile
SourceFilePath = @strSourceLGPath
SourceSharePath = @strSourceLGPath
View 13 Replies
View Related
May 9, 2008
Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.
I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.
Is there a way to tell the FTP task to just send *.txt, or something to that effect?
Thanks in advance for you help!
Thanks,
Mark
View 5 Replies
View Related
Jun 21, 2006
Hi ,
I have a master database. Based on certain criteria's I will be creating one new database. Once I have finished the creation, I have to check the value of a particular column in both database tables.
This is actually a status check. if both are not equal i have to update detail database table column value with master column value.
I thought i can use lookup transform for this. The problem is master database table's status datatype and detail table's status datatype is diff.
Can anyone suggest me any other way for this problem?
thanks in advance.
View 1 Replies
View Related
Mar 26, 2008
In my package i am loading data from a flat file into multiple tables like table1, table2, table3 all tables have a column called cust_num.
But data for this column cust_num is only coming in table1 and i need to load the data from this column to other tables how can i do this.
View 4 Replies
View Related
Sep 6, 2007
I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.
I have some 15 tables being populated using this SPROC.
Here is some helpful description
ENTITYNAME gives me the table i need to work
FIELDNAME gives me the field i have to work on
CHANGEDVALUE gives me the value changed in that field
( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )
while in Cursors i need to get using these above variables other variables like
FLAG
KeyName
Thrugh SQL1 I get the KeyValue
then using this KeyValue check if the data exist update else insert new data.
QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?
/*******************************************************************************************************/
DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage
OPEN Table_cursor
FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FLAG NVARCHAR(50);
SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';
DECLARE @KeyName NVARCHAR(50);
SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR(50)
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = N'Select @KeyValueOUT = '+ @KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @KEYID + ' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '
EXECUTE sp_executesql @SQL1, N'@KeyValueOUT INT OUTPUT', @KeyValue OUTPUT;
DECLARE @WC_TABLE NVARCHAR(100)
SET @WC_TABLE = 'WorkingCopy' + @ENTITY
DECLARE @SQL2 nvarchar (1000);
SET @SQL2 = 'IF EXISTS (SELECT '+ @KeyName +' FROM ' + @WC_TABLE + ' WHERE ' + @KeyName + ' = ' + @KeyValue + ' )' +
' BEGIN UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @ENTITY + ' WHERE ' + @Flag + ' = ' + '''Y''' + ' AND '+ @KeyName + ' = ' + @KeyValue + ';' +
'UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END'
EXECUTE sp_executesql @SQL2
FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE
END
CLOSE Table_cursor
DEALLOCATE Table_cursor
View 1 Replies
View Related
May 21, 2007
Hello,
I m having an FTP Task in SSIS project, from where I have downloaded .csv file.
And also having a Processed Folder in which the processed .csv files are kept.
Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing
Can anyone help me out how to process about.
plz reply as early as possible.
Thanks & Kind regards ....
Aparna
View 1 Replies
View Related
Jun 19, 2008
I have written a very basic Script Task using VS 2005, it just looks through a sqlDataReader and display a message.
I want this script to be triggered by an SQL Agent. I am not sure what to do from this point on. How do I publish or whatever it is called to get it to the SQL server or agent?
This is my very first attempt, no I have no clue what I am doing, will need lots of help and tutorials, thanks for reading.
View 1 Replies
View Related
May 25, 2007
i am trying to generate a dynamic connection string using a variable 'servername'
i know where and how to do it but i am not sure abt the details of the paramenters that are to be passed in the script
and anyone give me a sample script
this is what iam trying to do
Public Sub Main()
'
Dts.Connections("SQL????").ConnectionString = "Data Source????" + Dts.Variables("servername").Value.ToString() + ???windows authentication and what else ????
'
Dts.TaskResult = Dts.Results.Success
End Sub
Thanks all
View 3 Replies
View Related
Nov 19, 2007
Hi there,
I'm still pretty new to SSIS, what I am really battling with is getting a custom task to work.
I have developed a custom task, digitally signed it added it to the Global Assembly Cache, written the UI for it - Pretty much as it is done here: http://technet.microsoft.com/en-us/library/ms136080.aspx
but my problem is that when I drag it from the tool box into the control flow pane, I get this message: "The task user interface specified by type name 'XXXXXXXXXXXXXXXXXXX' could not be loaded.
(Microsoft.DatatransformationServices.Design).
Does anybody have an idea of what I am doing wrong here?
Duane.
View 2 Replies
View Related
Feb 20, 2008
Dim RootFolder As String
RootFolder = DTSGlobalVariables("gRootFolder")
I have an error that RootFolder is not being declared with this ?
but it is , why is .NET such a heap of sh*t ?
View 1 Replies
View Related
Aug 27, 2007
How can i find out the moniker for my own ssis task?
View 3 Replies
View Related
Sep 8, 2006
Please help me i am new to ssis ....I am trying to send an email using mail task and I am sure that ISP works fine with smtp. I am entering all the correct information in the task but it takes a long time to run the task and then comes back with a failure error. can someone help me find an easy way to send an email to group of emails...I even tried to send it to 1 email and it is still not working.
View 10 Replies
View Related
Dec 22, 2006
Hi Everyone,
When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.
Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.
Thanks
View 1 Replies
View Related
Nov 27, 2006
We are unable to retrieve a file from an FTP server using SSIS FTP Task. With the settings indicated below:
IsRemotePathvariable: False
RemotePath: /home/ftpu/test.csv
IsLocalPathVariable: False
LocalPath: working_directory (with Connection Manager entry setup for this)
Operation: Receive files
The following error is produced:
Error: 0xC0029183 at FTP data, FTP Task: File represented by "/home/ftpu/ns_test.csv" does not exist.
Performing a manual ftp works fine, hence suggesting a fault with the SSIS TFP Task. Has anyone else successfully used an FTP task, and if so, any ideas on what's going wrong?
Thanks
Neil
View 10 Replies
View Related
Jan 31, 2008
Has anyone used web service task in SSIS????
I am using a simple web service task, which posts a SOAP request To a web service and is supposed to get a response back...
I have been able to configure the HTTP Connection (which points to the URL of a WSDL file), and also been able to download the WSDL file onto the desktop....
Iam also able to define the Input - Service and Method.
I understand that when I assign the Service and method values I should see a grid asking for the input parameters, which I never get...
Is there a reason why this is happening, as , as per a tutorial that I have read, there is supposed to be a prompt of the parameters.
This is , of course the error I get when i run the package:
SSIS package "Webservice.dtsx" starting.
Error: 0xC002F304 at Web Service Task, Web Service Task: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Exception has been thrown by the target of an invocation..
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Task failed: Web Service Task
Any help highly appreciated!
Thanks
View 1 Replies
View Related
Aug 15, 2006
I have just been using the Import Data wizard in SSMS and when going through the wizard I checked the "Optimize for many tables" checkbox.
The resultant package contains a task called "Transfer Task". I've never heard of this before. Its not listed in the toolbox and its not documented in BOL.
It does some rather strange things as well.
From what I can determine it uses an XML manifest file (stored in c:documents and settings<user>Local settings emp) to construct a SSIS package on the fly which is then executed by an Execute Package Task.
Its SourceDB property is always "smo_Pubs". Its DestinationDB property is always "smo_Pubs_xfred"
There's no UI for it
All very peculiar.
What is it?
Where has it come from?
Did it arrive with SP1 or have I just never noticed it before?
Why is it preferable to a package with lots of data-flows?
Why is it not documented?
When should I use it/not use it?
Questions questions questions...
Thanks
-Jamie
View 11 Replies
View Related
Aug 28, 2007
I want to use script task in order to dynamically filled Table A, B, C from the data Table 1. What script should i used and how can i implement this in SSIS.
Ex:
Table 1
Col1 Col2 Col3
A F1 123
A F2 213
A F5 456
B G1 345
C H2 897
C H5 768
Now i wanna write a script which can dynamically do this:
1) read the each row of Table1
2) Get from Col1 = the name of the table on which it needs to update
3) Get from Col2 the name of the colunm of the table from Col1 on which update has to be made
4) Update that particular colunm with the value in Col3
Results shud be
Table A
F1 F2 F3 F4 F5
123 - - - -
- 213 - - -
- - - - 456
Table B
G1 G2 G3
345 - -
Table C
H1 H2 H3 H4 H5
- - - - 768
View 6 Replies
View Related
Jun 26, 2007
Is it possible to CREATE a database using SSIS and name it using a variable / parameter mapping.
if not, how can I use SSIS to take a username from a database table and create a database with that name?
thanks is advance....b
View 10 Replies
View Related
Apr 24, 2008
I have several packages, all start with a script task component. In this component I use a dll to load some information to the package
All works fine, we made over 80 packages without any problems...
Now we built a master package that call all the other packages (some in a parallel way)
and we're getting a very rare problem, sometime, and in diferente part of the package we get the following error message:
The script threw an exception: Object reference not set to an instance of an object.
When i run the packages alone (all of them) there is no problem
Any Clue?
Cheers
View 8 Replies
View Related
