I am new this SSIS, Please help me.

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Custom Control Flow Item Issue Karunakaran - Thanks..that definitely shed some light on the topic! I thought it was being run on the server via RPC, with status messages being returned back... I do not have any SSIS Service on my machine, so will install it. (I do have SQLServer express, but am not using it). However , just as a test, I removed the 'FileWatcher' control, then re-imported the package to the server, and it is now running (validation's all completed, and it has "Start"ed), and I see my recordcount increasing, so it is working. How is this possible if I don't have SSIS installed Tag: I am new this SSIS, Please help me.
  - 2
    - Move distribution data/log files to another drive I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it Thanks Tag: I am new this SSIS, Please help me.
  - 3
    - Data Source and Connection Manager jh0483 wrote: But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. Have you ever used or at least looked into configurations They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages. Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages. Tag: I am new this SSIS, Please help me.
  - 4
    - Query Execution failed for dataset error / the syntax for " " is incorrect Hi, Am totally new to MDX Queries. I got this error while previewing a report that we had made. It goes like this -- Query execution failed for dataset 'some_dataset'. Parser: the syntax for 'some_character' is incorrect. What was figured out was this error shows up only for those values in a particular dropdown list which are multiword. Eg : My Company Technology and not for those values which a re single word. Eg : Company what could be the possible problem and the solution to that. Tag: I am new this SSIS, Please help me.
  - 5
    - Month's end Hi, I am working on a cube that contains month standings and month totals. This works fine if I select one month. It goes wrong when I do a year to date selection because it sums up the month standings. How can I make a cube that does sum up the month total but only returns the last month standings. With regards, Constantijn Enders Tag: I am new this SSIS, Please help me.
  - 6
    - Backup File Name Dear all, On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day. Thanks Imran Hasware Tag: I am new this SSIS, Please help me.
  - 7
    - CONTAINS and WHERE Clause Combination taking too long Hi, I have a table with 3 columns and 20 million records. first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000). I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column. Now when i try to search SELECT TOP 20 col1, col3 FROM tbl WHERE col1 = '1234' AND CONTAINS(col3,'"market*"') I am facing following problems 1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second. 2- Some time it show criteria is too complex, although i am only requesting a single word in col3. I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information. Please assist. Tag: I am new this SSIS, Please help me.
  - 8
    - stored procedures, compile or not Thanks everyone. After further research I discovered I was opening the age old dynamic sql vs stored procs battle. I think I have the information necessary to proceed. My problem is passing 8 or 10 parameters and the logic necessary if a parameter is null, but that is an issue I can deal with. I just didn't want to go to all the trouble of rewriting a hundred dynamic sql scripts into stored procs if there was no benefit to be derived. Thanks again. Tag: I am new this SSIS, Please help me.
  - 9
    - Add connection error: 40 Hi i just reinstalled windows, and installed visual studio .net 2005 and the Sql server. But when im trying to add connection, select my computer at Server name, type a databasename then pressing OK i get this message: An error has occured while estabilishing a connection to the server. When connection to SQL server 2005, this faliure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Names pipes provider, error: 40 - Could not open a connection to SQL Server) Does anyone know what todo herman Tag: I am new this SSIS, Please help me.
  - 10
    - is it possible to connect to SQL Express without network? I have notebook, client server application and sql express sp2 on this notebook. Also, I know domain user login/password, but I don't know any local user name/password and I don't know any sql server user name/password. Domain user is not a local administrator on this notebook. When notebook connected to the office LAN I am working good with my app-> my sql express as a domain/user. I'd like to work at home too. I can logon to the system (XP SP2) using domain account when notebook has no network connection. (probably OS caches something) Is it possible to work with sql express using domain login when no network connection PS. old version of this software used MSDE 2000 and it was possible(!). Thank you. Tag: I am new this SSIS, Please help me.
  - 11
    - Cannot completely install/remove SQL Server Express I cannot use, remove, or reinstall Microsoft SQL Server 2005. Although Microsoft SQL Server 2005 and Microsoft SQL Server Setup Support Files appear in ControlPanel-ARP, when I attempt to remove Microsoft SQL Server 2005, the workstation component remains. If I try to download and reinstall Microsoft SQL Server Express from the Microsoft website, the download does not install completely as described below. My current situation is much better than the previous incompatibility or configuration problems I had been experiencing between Microsoft Office Small Buisness Accounting 2006, Microsoft Office Outlook 2003, Outlook Business Contact Manager, Microsoft SQL Server 2005 (express edition installed in conjunction with Visual Basic 2005 express), and Microsoft SQL Server Desktop Engine (installed in conjunction with Small Business Accounting 2006). The problems and error messages previously included: 1) A consistent bootup error (Net service configuration error...The specified service does not exist as an installed service); 2) The inability of Visual Basic to connect to an instance of SQL server (An error has occurred while establishing a connection to the server...); 3) a SQL server configuration manager error: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL server configuration manager. Invalid namespace [0x8004100e]; and 4) SQL server management studio express error: Cannot connect to <computername>\SQLEXPRESS... An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1). These problems have been partially solved by: 1) completely removing Small Business Accounting 2006, Outlook Business Contact Manager Update, and MSDE (which appear to be codependent); and 2) removing and reinstalling Visual Basic 2005. However SQL Server still cannot be completely removed or reinstalled and therefore Visual Basic cannot connect to SQL datafiles. When I most recently tried to remove SQL server 2005 using ARP, the installer reported that only the workstation component remains (Version: 9.00.1399.06 Edition: Express Edition Patch level: 9.00.1399.06), but is not removed by the ARP process. When I most recently tried to install SQL server 2005, the Setup Support Files install correctly, the System Configuration Check passes, but setup fails for MSXML6, SQL Server Database Services, and WorkStation components Books Online (detail log SQLSetup0068 file). According to Add/Remove programs, the following applications are installed: Microsoft Visual Basic 2005 Express Edition ENU Microsoft SQL Server Native Client Microsoft SQL Server Setup Support Files (English) Microsoft SQL Server VSS Writer Microsoft SQL Server 2005 Various MSXML versions Any help in un/reinstalling or configuring Microsoft SQL Server 2005 for use with Microsoft Visual Basic 2005 Express Edition would be dearly appreciated. Thanks. Tag: I am new this SSIS, Please help me.
  - 12
    - Transaction log backup error Hello, I have this backup scheduled for "All databases" on server, which is around 300 at this point. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards. I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure Thank you very much Tag: I am new this SSIS, Please help me.
  - 13
    - Email as csv using Database Mail Hi everyone, I'm having problems sending my query results as a csv file. Ideally I would be able to send my results from a sql job every night as a csv but I'm having problems doing it. My code is shown below Code Block EXEC msdb.dbo.sp_send_dbmail @profile_name = 'admin' ,@recipients = m@m.com ,@subject = 'Notification Created Today' ,@attach_query_result_as_file = 1 ,@body_format = 'text' ,@query =' select a1 . c# , cli , fe , type , dateReq , p , ( select convert ( varchar ( 50 ), convert ( money , amount ), 1 )), ( select convert ( varchar ( 50 ), convert ( money , vat ), 1 )),( select convert ( varchar ( 50 ), convert ( money , total ), 1 )), cReq from Disb_1 a1 inner join Details on a1 . c# = Details . c# where datediff ( day , getdate (), dateReq )= 0 and dateReq <= getdate ()' I've tried many different things and been through the parameters of sp_send_dbmail but I just cant find anything. Also, how can I use column names for those money conversions At the moment it just says 'no column name' Thanks, Steve Tag: I am new this SSIS, Please help me.
  - 14
    - convert timestamp to datetime I'm use this statement. CONVERT ( CHAR ( 19 ), GETDATE (), 20 ) Tag: I am new this SSIS, Please help me.
  - 15
    - Unable to make changes to disconnected recordset Its been almost 10 years since I have had to do work with good-old RecordSet objects... I am filling a RecordSet with data returned from a SQL server via a stored procedure. I then set the ActiveConnection property to Nothing in order to disconnect it so I can make changes to it. But when I try to set the value on a given row I get back a "Multiple-step operation generated errors. Check each status value" error message. My understanding is that this is indicative of trying to use the wrong datatype. I have verified that the type is correct (I am dealing with integers) so I am at a loss for what the problem could be. Here is my code: Set rsPackages = CreateObject("ADODB.RecordSet") rsPackages.CursorLocation = adUseClient rsPackages.LockType = adLockBatchOptimistic rsPackages.Open "EXECUTE stp_FetchPackageData", myConn rsPackages.ActiveConnection = Nothing Response.Write("Value: " & rsPackages("TotalCount")) ' returns 0 Response.Write("Data Type : " & rsPackages.Fields("TotalCount").Type) ' returns 3 = adInteger rsPackages("TotalCount") = 1 ' throws multi-step error Oddly enough, when I look at the Attribute property of the field I get back a value of 112. When you break it down I think that value indicates the row value is read-only (Could that be my problem Just a really bad/unhelpful error message ) But if I try to change it I get back a message saying cannot be done since RecordSet is already open. Thanks, Jason Tag: I am new this SSIS, Please help me.
  - 16
    - What is wrong with my codes to connect VB.net to sql server 2005 Express database? My previous 'suggestion' was missing the keyword 'AS' in front of string causing the error. Reviewing your original code: Dim DB_Location = " c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Trial1.mdf " (REMOVE -NOT NEEDED) Dim ConnStr as String = "Server=localhost; DataBase= Trial1; Integrated Security =SSPI" Dim sqlConnection As New SqlConnection(ConnStr) 'Use database Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' " Dim cmd As New SqlCommand(strSQL, SQLConnection ) I suggest ConnStr is more understandable than MyConnection , since it is a connnection string and NOT a connection object. Tag: I am new this SSIS, Please help me.
- Next
  - 1
    - No Parameter Pane when Reporting in 2005 against a 2000 AS Cube I have this strange issue. VS2005 SSRS2005 SSAS2000 cube based on data from SQL 2005 database When i connect to the SSAS cube and create a dataset in a SSRS 2005 report i do not get a parameter pane. Also, if i right click on a dimension & select "Add to filter" i get the following error message "Object reference not set to an instance of an object" It's not my installation of SQL 2005, as I can connect to an SSAS2005 cube and get the filter/parameter pane and do not get the error message. Parameters work fine. Have i come across a known issue when reporting on SQL 2005 versus a SQL 2000 AS Cube... or can someone shed some light on the issue for me.... If someone can tell me how to display screenshots I can add them to a post Thanks Will Tag: I am new this SSIS, Please help me.
  - 2
    - Rolling back transactions for multiple Stored PRocs I have a quick question. I need to execute some stored procedures in certain steps... all performing some inserts/updates What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP) so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails... how can i roll back to the initial state in ASP.NET. would appreciate any info. or redirection to the the location where i can look up some info on that. AJ Tag: I am new this SSIS, Please help me.
  - 3
    - Framework 2.0 changes - TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host. Hi Fredrik, It appears that Sqlclient grabs a connection from the pool that has already been closed because of a network error and reports an error when it tries to write to the socket. I can't understand why the 1.1 framework would be less susceptible to these network errors than the 2.0 framework. Would you be able to take a client-side trace that captures the error and send it to me along with the server's error log You can email me the files at ilsung@microsoft.com . The instructions for setting up the trace are here: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnadonet/html/tracingdataaccess.asp . Also, if you know of a way for us to repro this, that would be great. Thanks, Il-Sung Lee Program Manager, SQL Server Protocols Microsoft Corp. This posting is provided "AS IS" with no warranties, and confers no rights. Tag: I am new this SSIS, Please help me.
  - 4
    - Problem With Common Table Expression (CTE) I constructed the following but I get an error results trying to run it. What am I doing wrong Error: Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 2 has been exhausted before statement completion. WITH AllPNBom ( [Parent Part Number] , [Component Part Number] , ItemId , ParentId , Part_Class , HLevel ) AS ( -- Anchor member definition SELECT [Parent Part Number] , [Component Part Number] , ItemId , ParentId , Part_Class , 0 AS HLevel FROM AllBoms WHERE [Parent Part Number] = '305-0345-001' UNION ALL -- Recursive member definition SELECT e . [Parent Part Number] , e . [Component Part Number] , e . ItemId , e . ParentId , e . Part_Class , apb . HLevel + 1 as HLevel FROM AllBoms e INNER JOIN AllPNBom apb ON e . ParentId = apb . ItemID ) -- Statement that executes the CTE SELECT * from AllPNBom order by HLevel , [Component Part Number] Option ( maxrecursion 2 ) Tag: I am new this SSIS, Please help me.
  - 5
    - Upgrading memory in a SQL 2005 2 node active/passive cluster Could someone give me an ideal of what it takes to upgrade the memory in my cluster. Is it as easy has upgrading the passive node, switching the nodes, then upgrading the other server. And do I need to re-configure the windows cluster. And will both server need to be down at the same time at any point. Or is their more to the process Thanks for any assistance. btw, I currently have 16mb of RAM in each node. Tag: I am new this SSIS, Please help me.
  - 6
    - Differences between 2000 and 2005 This function works fine in 2005 Code Snippet IF OBJECT_ID (N'dbo.fn_getdate', N'FN') IS NOT NULL DROP FUNCTION dbo.fn_getdate; GO CREATE FUNCTION dbo.fn_getdate () RETURNS datetime AS BEGIN DECLARE @newdate datetime SELECT @newdate= CASE WHEN dt.date IS NULL THEN GETDATE() ELSE dt.date END FROM TestTable dt RETURN(@newdate) END; GO SELECT dbo.fn_getdate() Post your code. What exactly is the error you are getting Tag: I am new this SSIS, Please help me.
  - 7
    - passing datetime variables into a bcp statement Thank you for your help, works great now. Tag: I am new this SSIS, Please help me.
  - 8
    - Hiding/Showing columns based on the columns present in the dataset I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report. Can I do that Any reply showing me the right way is appricited. -Thanks, Digs Tag: I am new this SSIS, Please help me.
  - 9
    - Security exception when calling custom code from a report. Update: Still not working and yes, i have added AllowPartiallyTrustedCallers() to the assembly, and yes i have tried Assert'ing for the permissions. Still at a standstill here, help! Tag: I am new this SSIS, Please help me.
  - 10
    - 0xC020801C Connection error can someone please help me with this. Cause im really stuck now. Here's my code snippet. My database do also require a password but i'm not sure on how will i insert it in the connection string. Thanks alot in advance. cnOLEDB.Name = "MyOLEDBConnection" cnOLEDB.ConnectionString = "Data Source=AV_SRV;Initial Catalog=AdventureWorks;Provider=SQLNCLI.1;Integrated Security=SSPI;" Console.WriteLine( "Creating the MyFlatFileConnection" ) Dim cnFile As ConnectionManager = _ myPackage.Connections.Add( "FLATFILE" ) cnFile.Properties( "Name" ).SetValue(cnFile, "MyFlatFileConnection" ) cnFile.Properties( "ConnectionString" ).SetValue _ (cnFile, "c:\temp\MySSISFileExport.csv" ) cnFile.Properties( "Format" ).SetValue(cnFile, "Delimited" ) cnFile.Properties( "ColumnNamesInFirstDataRow" ) _ .SetValue(cnFile, False ) cnFile.Properties( "DataRowsToSkip" ).SetValue(cnFile, 0) cnFile.Properties( "RowDelimiter" ).SetValue(cnFile, vbCrLf) cnFile.Properties( "TextQualifier" ).SetValue(cnFile, """" ) 'Add a Data Flow Task Console.WriteLine( "Adding a Data Flow Task" ) Dim taskDF As TaskHost = _ TryCast (myPackage.Executables.Add( "DTS.Pipeline" ), TaskHost) taskDF.Name = "DataFlow" Dim DTP As MainPipe DTP = TryCast (taskDF.InnerObject, MainPipe) ' Add the OLE DB Source Console.WriteLine( "Adding an OLEDB Source" ) Dim DFSource As IDTSComponentMetaData90 DFSource = DTP.ComponentMetaDataCollection.New() DFSource.ComponentClassID = "DTSAdapter.OLEDBSource" DFSource.Name = "OLEDBSource" ' Connect, populate the Input collections and disconnect Dim SourceInst As CManagedComponentWrapper = _ DFSource.Instantiate() SourceInst.ProvideComponentProperties() DFSource.RuntimeConnectionCollection(0).ConnectionManager _ = DtsConvert.ToConnectionManager90 _ (myPackage.Connections( "MyOLEDBConnection" )) DFSource.RuntimeConnectionCollection(0).ConnectionManagerID _ = myPackage.Connections( "MyOLEDBConnection" ).ID SourceInst.SetComponentProperty( "OpenRowset" , "[Sales].[Customer]" ) SourceInst.SetComponentProperty( "AccessMode" , 0) SourceInst.AcquireConnections( Nothing )....... -kix Tag: I am new this SSIS, Please help me.
  - 11
    - offline OLAP Cube +MDX I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months. I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables . When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help. Here I am getting Multiple Selections for page field as well .Is there any way to disable them The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well. I am relly looking looking forward to you great guys. Regards Tag: I am new this SSIS, Please help me.
  - 12
    - Help with validation query to show "duplicates" How about: Code Snippet SELECT CustID , ( Year ( Chargedate ) * 100 ) + Month ( ChargeDate ) as YearMo , count (*) as Tally FROM Charges WHERE ProcedureCode = 1234 GROUP BY CustID , ( Year ( Chargedate ) * 100 ) + Month ( ChargeDate ) HAVING count (*)> 1 Tag: I am new this SSIS, Please help me.
  - 13
    - results of stored proc to temp table I am writing a stored proc that needs to place the results of other stored procs into temp tables. I tried: insert INTO #temp_data EXEC ekg..sp_junk but received this error: An INSERT EXEC statement cannot be nested. Tag: I am new this SSIS, Please help me.
  - 14
    - compact sqlce database Thank you. I will give it a try. Tag: I am new this SSIS, Please help me.
  - 15
    - pivot transform help I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row. I need to go from this... PKcol1 PKcol2 PKcol3 col4 col5 col6 col7 A 2007 1 Y N N N A 2007 2 Y Y N N A 2007 3 N N N Y into this.... A 2007 1 col4 Y A 2007 1 col5 N A 2007 1 col6 N A 2007 1 col7 N A 2007 2 col4 Y A 2007 2 col5 Y A 2007 2 col6 N A 2007 2 col7 N A 2007 3 col4 N A 2007 3 col5 N A 2007 3 col6 N A 2007 3 col7 Y Can I do this using the pivot transform Any suggestions Tag: I am new this SSIS, Please help me.
  - 16
    - Problems after installing SP2 This should be enough. Edward. -- This posting is provided "AS IS" with no warranties, and confers no rights. Tag: I am new this SSIS, Please help me.

Sridhar Babu

Hi,

I am working on .Net tech with SQL Server 2000.

I want to update my technical skills to DW.

I mean, i planning to learn SSIS and i am new this topic.

Please help me........

thanks

babu

Re: I am new this SSIS, Please help me.

DarrenSQLIS

Two options-

Pay for Books and/or Training

or

Teach yourself, perhaps get hold of SQL Server 2005, perhaps try the free documentation and tutorials Have you you even looked on the MS site for this

http://msdn2.microsoft.com/en-us/library/ms167031.aspx

Re: I am new this SSIS, Please help me.

Sridhar Babu

thanks for reply. I am asking is there any free documents and tutorials are available in any free sites.

thanks

Babu

Re: I am new this SSIS, Please help me.

jwelch

There are tutorials in SQL Server Books Online.

Also, try the followink searches:

http://search.live.com/results.aspx q=ssis+tutorials&mkt=en-us&FORM=OPNSCH

http://search.live.com/results.aspx q=ssis+examples&form=QBRE

Re: I am new this SSIS, Please help me.

Subhash

Sridhar,

Apart from msdn and latest downloadable samples from microsoft site, There are lots of online resources.

1) www.sqlis.com by Darren Green and Allan Mitchell

2) Download Free Samples at Wrox official site

2) Blogs by Jamie Thomson,Rafael Salas, Brian Knight, Donald Farmer, Kirk Haselden, Scott Barret, John welch, Ashwin Sharma, Phil Brammer, Michael Entin, Alberto Ferari

Many Thanks

Subhash Subramanyam

Re: I am new this SSIS, Please help me.

misha_m83

I suggest you download tutorial from www.microsoft.com/downloads/details.aspx FamilyID=B1145E7A-A4E3-4D14-B1E7-D1D823B6A447&displaylang=en.
It's great for the begining.