SQL Server Integration Services Forum

Different sheetname for each loop container

Sun, 22 Jul 2012 10:59:33 Z

I followed the steps given in .http://sql-bi-dev.blogspot.in/2010/06/foreach-loop-container-in-ssis.html

to read data. But there is a change in sheetname. How do I handle when am using the steps given in the link and what and where should I change?

Error in upload XLS file through SSIS

Mon, 23 Jul 2012 08:47:35 Z

First i create Data Flow Task, in that i create Excel Source & configure particular xl file in it. Preview is file. But when i connecting this OLEDB Destination & define connection with Excel source .... Preview query result is showing Nothing & while executing its giving error

[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error in Excel Destination

Fri, 20 Jul 2012 14:37:27 Z

[Excel Destination [1663]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [1663]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (1663) failed the pre-execute phase and returned error code 0xC0202025.

[DTS.Pipeline] Information: "component "Excel Destination" (1663)" wrote 0 rows.

Task Data Flow Task 1 failed

Interview Questions

Thu, 10 Sep 2009 16:04:02 Z

Hi
Could any one of you guys help me with Interview questions (with Answers) in SSIS?..

Thanks in advance..

Error: Incompatible transaction context was specified for a retained connection.

Mon, 16 Jul 2012 13:18:20 Z

Hi All,

Problem Scenario:

Implemented One package with a single sequence container, inside that there are 5-6 DFT and Data is read from multiple excel sheets and then after all validations inserted into database. So DFT are implemented somewaht like this -

DFT1 - To check row Count from Source Excel and store same into variable

DFT2- Data Validation based on Lookup operations and in same DFT if data is valid it is dumped into one CSV file at end. which is also updating RowEnd variable count.

Precednce Constarint: If RowSource count == Row END validated count then to DFT 3

DFT3- I am simply reading same buffer CSV file and inserting it into SQL server(Employees table).

NEXT UPON success I have one execute SQL task which reads same table where I have dumped the data and inserts into different table after fetching from Employees table...

After this likewsie to above I am reading some more excel sheets and inserting into 2 more tables with above DFT logic.

My package contains Isolation level as "Read Commited"(Although I tried with snapshot & Read uncomitted, Serilizable also). All my DFT are under one sequence container....which by Transaction property set to - Support....When I change Transaction property to "Required" my package fails with this error -

Error: Incompatible transaction context was specified for a retained connection.
This connection has been established under a different transaction context. Retained connections can be used under exactly one transaction context.

Just one more info Retain Connection property is set to True for Sequence Container and package also.

For now I have implemented Explicit Transaction by specifying Execute SQL task (BEGIN TRAN) and in case of any errors "Rollback Transaction"...this seems to be working perfectly also, but I want to use DTSC service for same. Yes just for FYI my DTC service is up and running for Netowrk connections with allowed for any inbound connections on server, User credentials used for SSIS package are sysadmin role mapped.

I am not able to understand why package is continously getting failed when I set Transaction property to Required?

Any help will be appreciated -

SSIS 2012 Script Task Error

Mon, 23 Jul 2012 08:06:52 Z

Hi Guys,

What causes the below error? It does not come up every time I run the script task in debug mode, but when it does appear it's annoying.

Could I be missing an update?

MCTS, MCITP: BI Technical Consultant| Karabina, South Africa Blog: www.selectsifiso.net

How to get three first lines from multi text files to database

Mon, 23 Jul 2012 05:02:33 Z

Hi,

I have about 500 text files. I need to import data from three first lines in those text file into database. How can I do this?

Thanks

OLE Db Source Object Truncating CHR(0)

Sun, 22 Jul 2012 19:50:36 Z

Hi I am experiencing a truncation of characters following the incidence of CHR(0) in my encrypted data colulm.

I am attempting to iterate through the rows of the resultset in an SSIS Script Component. The OLE DB Source produces a column with the value:

a¤ˆÀµ=b8Ä+¤0Ž¢>o

The ascii values are:

a-97

¤-164

ˆ-136

À-192

µ-181

=-61

b-98

8-56

Ä-196

+-43

¤-164

Ž-142

¢-162

>-62

The SSIS Script component sees this as a¤ˆÀµ=b8Ä+¤ - truncating the trailing characters after the ¤ character.

I am unsure if the OLE DB Source column is truncated when exposed in the InputBuffer to the SSIS Script Component or if the truncation is occurring as a behavior of the OLE DB Source itself.

I have tried changing the code page and switching over to Unicode with same results.

I am unable to apply any Replace methods because the CHR(0) is part of the encoded data that I need to decode.

Do I need to send the data as a varbinary and do something else?

Advance thanks to any efforts in responding.

Issue with data type when importing rows from Flat File to SQL Server

Fri, 20 Jul 2012 18:53:25 Z

Package fails when importing data from flat to SQL Server

Number of columns in Flat File

ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost

Table structure in SQL Server

    [ProductID] [int] NULL,
   [Name] [nvarchar](50) NULL,
   [ProductNumber] [nvarchar](25) NULL,
   [MakeFlag] [bit] NULL,
   [FinishedGoodsFlag] [bit] NULL,
   [Color] [nvarchar](15) NULL,
   [SafetyStockLevel] [smallint] NULL

Used derived column to convert the data types

MakeFlag1 <add as new column> MakeFlag == "False" ? "0" : "1" Unicode string [DT_WSTR] 1

FinishedGoodsFlag1 <add as new column> FinishedGoodsFlag == "False" ? "0" : "1" Unicode string [DT_WSTR] 1

SafetyStockLevel1 <add as new column> (DT_I2)SafetyStockLevel two-byte signed integer [DT_I2]

Mapped the columns correctly with the newly created derived columns in OLE DB destination.I am getting error message in the case of 'SafetyStockLevel' . As far i know everything has been done properly here what could be the issue.Please correct me if i am going wrong any where. Please find the error message as below

Error: 0xC0049064 at Data Flow Task, Derived Column [58]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [58]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (58)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "SafetyStockLevel1" (83)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (58) failed with error code 0xC0209029 while processing input "Derived Column Input" (59). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure

Smash126

How can I add non additive measure to destination database

Sun, 22 Jul 2012 19:24:58 Z

I have 3 non additive measure that were calculated in derived column transformation.Now, I did the aggregate transofrmation to sum up my additive measure as the the last 3 I didnt put them in the aggregate but when I was trying to insert my measure to fact table those last 3 didn't show up ! How to insert them in the fact table should I use another transofrmation and what is it ?

Excle file does not found in source folder

Sun, 22 Jul 2012 17:04:45 Z

Hi All,

I have a package which contains approx 10 data flow task to read Excel file and populate a SQL table. Excel File source task will read input file from the INPUT folder and process them and at the end it will delete from the folders. So, sometimes there will not be any input files present (if support team does not populate new input files) in the INPUT folder and my package throws error that source file does not exist.
My aim is to don’t process the Data Flow task when file not present and go to next Data flow task. But still try trying to execute the Dataflow task, so getting an error.
I wanted to make package should NOT run such situation. Since we do not have the File in Source path.
Thanks in advance.
Vipin jha

Thankx & regards, Vipin jha MCP

different excel sheet name in different workbooks

Thu, 19 Jul 2012 19:01:16 Z

I am having two folders - C:\2012\apr\4 excel files ; say file1,file2,fil3,file4 & C:\2012\june\ 4 excel files say file1,file2,file3,file4. I am reading these files using for each file...The problem is out of 4 excel files, two sheets name are same it means file1-sheet1,file2-sheet2,file3-sheet1,file4-sheet2 and same situation in other folder also. I created two variables query1, query2 storing select statements to fetch data.

I followed the steps as mentioned in the site...http://sql-bi-dev.blogspot.in/2010/06/foreach-loop-container-in-ssis.html to get data.

My requirement - when I read these files, it shouldn't throw an error because of different sheetname. How do I handle this? I am not using any code.

SP executed around 6 mins in Execute SQL Task but still executing from past 30 mins in SSMS.

Thu, 19 Jul 2012 21:15:05 Z

I have huge SP which is executed around 6 mins in Execute SQL Task in my SSIS package. However, It's still executing from past 30 mins in SSMS query window. Any thoughts? Please

retrieve the address url from the hyperlink property

Tue, 17 Jul 2012 05:36:08 Z

Hi,

I have a scenario:
In the html page (eg: http://website.com/home.html), there is a hyperlink. I right click the hyperlink to see the properties and that where i see the address (url) to the file (eg: http://website.com/sample.zip").

how can i use the webclient or http method in script task if i dont know the file path.

(or)

how can i retrieve the address url from the hyperlink property using vb.net.

I appreciate your help.

Thanks,

event 12291 SQLISPackage100

Sun, 22 Jul 2012 12:19:36 Z

one maintenance job (rebuild index task on user databases) is failing with event 12291. all other maintenance jobs like backup are working fine. the sql server agents runs under a domain admin account. the problem is not related to a specific database. it crashes on any db chosen. we have deleted and recreated to maintenance plan and it still doesn't work

the job history:

ep ID 1
Server STZ-SQL1
Job Name MaintenancePlan.weekly_reb_indexes
Step Name weekly_reb_indexes
Duration 00:27:43
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: IBA\iba_admin. ...sion 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 00:25:00 Progress: 2012-07-22 00:25:01.17 Source: {B00EA0E0-FFD0-48C4-B704-8CAE5645858D} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2012-07-22 00:29:21.35 Source: Rebuild Index Task Executing query "USE [adalib] ".: 0% complete End Progress Progress: 2012-07-22 00:29:22.03 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_ALT_1] ON [dbo].[A10] REBUILD PAR...".: 0% complete End Progress Progress: 2012-07-22 00:29:22.03 Source: Rebuild Index Task Executing query "USE [adalib] ".: 0% complete End Progress Progress: 2012-07-22 00:29:24.01 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_ALT_2] ON [dbo].[A10] REBUILD PAR...".: 0% complete End Progress Progress: 2012-07-22 00:29:24.01 Source: Rebuild Index Task Executing query "USE [adalib] ".: 0% complete End Progress Progress: 2012-07-22 00:29:25.02 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_ALT_3] ON [dbo].[A10] REBUILD PAR...".: 1% complete End Progress Progress: 2012-07-22 00:29:25.02 Source: Rebuild Index Task Executing query "USE [adalib] ".: 1% complete End Progress Progress: 2012-07-22 00:29:26.56 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_ALT_4] ON [dbo].[A10] REBUILD PAR...".: 1% complete End Progress Progress: 2012-07-22 00:29:26.56 Source: Rebuild Index Task Executing query "USE [adalib] ".: 1% complete End Progress Progress: 2012-07-22 00:29:28.23 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_ALT_5] ON [dbo].[A10] REBUILD PAR...".: 1% complete End Progress Progress: 2012-07-22 00:29:28.23 Source: Rebuild Index Task Executing query "USE [adalib] ".: 1% complete End Progress Progress: 2012-07-22 00:29:29.22 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_F_ID] ON [dbo].[A10] REBUILD PART...".: 2% complete End Progress Progress: 2012-07-22 00:29:29.22 Source: Rebuild Index Task Executing query "USE [adalib] ".: 2% complete End Progress Progress: 2012-07-22 00:29:30.23 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00001] ON [dbo].[A10] REBUILD P...".: 2% complete End Progress Progress: 2012-07-22 00:29:30.23 Source: Rebuild Index Task Executing query "USE [adalib] ".: 2% complete End Progress Progress: 2012-07-22 00:29:31.20 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00002] ON [dbo].[A10] REBUILD P...".: 2% complete End Progress Progress: 2012-07-22 00:29:31.20 Source: Rebuild Index Task Executing query "USE [adalib] ".: 3% complete End Progress Progress: 2012-07-22 00:29:32.07 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00003] ON [dbo].[A10] REBUILD P...".: 3% complete End Progress Progress: 2012-07-22 00:29:32.07 Source: Rebuild Index Task Executing query "USE [adalib] ".: 3% complete End Progress Progress: 2012-07-22 00:29:33.23 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00004] ON [dbo].[A10] REBUILD P...".: 3% complete End Progress Progress: 2012-07-22 00:29:33.23 Source: Rebuild Index Task Executing query "USE [adalib] ".: 3% complete End Progress Progress: 2012-07-22 00:29:34.42 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00005] ON [dbo].[A10] REBUILD P...".: 3% complete End Progress Progress: 2012-07-22 00:29:34.42 Source: Rebuild Index Task Executing query "USE [adalib] ".: 4% complete End Progress Progress: 2012-07-22 00:29:35.58 Source: Rebuild Index Task Executing query "ALTER INDEX [A10_FL00006] ON [dbo].[A10] REBUILD P...".: 4% complete End Pro... The package execution fa... The step failed.

???

disabling or enabling lock escalation for large insert and updates

Sun, 22 Jul 2012 01:35:02 Z

Hi,

What's better, enabling or disabling the lock escalation in the tables?

I have a large table to load (30Gb), during the loading there is no read access to my tables. so nobody will be locked.

what the best lock escalation option?

also what are the best option for the indexes used in the destination tables?

I read some articles about this, but there is no clear path as they talk about reducing the lock duration while I want to reduce the loading time and the memory usage.

thanks.

SSIS vs SQL Server

Sat, 21 Jul 2012 18:25:05 Z

I have a typical questions, what is the main advantage of using SSIS rather SQL Server. What ever we do with SSIS, we also can achieve with stored procedures.

I want ignore truncation error and redirect rows

Tue, 10 Jul 2012 20:43:01 Z

I am having Excel file that contains a column say 'H' having more than 255 chars. I put ignore error, ignore truncation so that I can import data into Database. When I do this, data truncating and importing into DB only 255 char.

My requirement is now, how do I find out those rows that were truncated and inserted into DB.

File does not find in Error SSIS

Sun, 22 Jul 2012 07:52:50 Z

Dear Sir,

I am facing below problem for last 1 week, i have not found any proper solution for same.suppose I am having 3 excel connection ,each excel have different data flow task. My ETL Process load daily by daily. after loading data from excel to sql table I am deleting the file to avoid duplicate entry next day. in some dataflowtask will not get excel file some day due to that my package is failing

My aim is , if DFT1 does not find file then it should go to next DFT2 without failing

Please suggest the best way with example as this is very important for me

Regards,

Vipin jha

Thankx & regards, Vipin jha MCP

SSIS package not running with job

Sat, 21 Jul 2012 21:27:17 Z

Hi,

I'm using SQL Server 2008 R2 SP1 on Windows 7 OS.
I'm running an SSIS package on my local machine and the package is located in C drive.

I created a SSIS package with 32 bit version of Excel as source and destinations in different DFTs.
For this to run properly, I changed the Run64BitRuntime to False.
The package is running fine. But when I create a job to run this package it failed.
I created a Credential, Proxy and windows user login with access to the folders where excel files are present.
I changed the job owner to the new login which I created and job step RunAs to the proxy account.

This time I got a different error as below:

Executed as user: USHYDDJANDHYAL7\SQLAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:51:23 AM Info: 2012-07-22 01:51:24.32 Code: 0x4004300A Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: Validation phase is beginning. End Info Progress: 2012-07-22 01:51:24.32 Source: DFT_Excel To SQL -- Dates Validating: 0% complete End Progress Error: 2012-07-22 01:51:24.40 Code: 0xC0202009 Source: SSIS_Pkg_RU Connection manager "Excel Connection Manager 1" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error Error: 2012-07-22 01:51:24.40 Code: 0xC020801C Source: DFT_Excel To SQL -- Dates Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-07-22 01:51:24.40 Code: 0xC0047017 Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2012-07-22 01:51:24.40 Source: DFT_Excel To SQL -- Dates Validating: 50% complete End Progress Error: 2012-07-22 01:51:24.40 Code: 0xC004700C Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-07-22 01:51:24.40 Code: 0xC0024107 Source: DFT_Excel To SQL -- Dates Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:51:23 AM Finished: 1:51:24 AM Elapsed: 1.31 seconds. Process Exit Code 1. The step failed.

When I run the package from the SSMS --> Stored Packages --> MSDB, this is executing perfectly and when I run it from CMD, it is running successfully. But when I run the job, it is failing.

I gave the required permissions to the login like Sysadmin server role, and in user mapping, for msdb database, checked the SQL Agent related db roles and db_owner as well.
In the job step, I selected CMD and gave the fully qualified path of the 32 bit of DTExec.exe and the remaining parameters like /SQL, Server name, package name etc.
I even changed the Protection level to DoNotSaveSensitive but still no luck.

I'm not understanding why the package is failing through job but not through BIDS, CMD, SSMS.

Could anyone please help me out with this issue?

Regards,
djandy

ssis 2008r2 dataflow freeze

Sat, 21 Jul 2012 14:19:40 Z

Hi,

today my dts package freeze during the loading of my data.

I have multiple dataflows in my package, and the freeze occurs in the dataflow 5 or 6, never on the first dataflows.

the 5 is pretty simple, I get data from SQL server to fill a cache transform.

but the freeze could occurs here, even if the execution normaly takes 5 to 10 sec.

and if the freeze is not in the DF 5, its in the 6 which is the biggest one.

yesterday everything works fine!!!

I try to execute inside VS and using DTExec, same behavior.

on the SQL Server side, I see that the network IO wait task is at 1000ms and no more activity.

so why this could occurs?

why today and not yesterday? (I try this 10 times and always the same behavior occurs at different point in time)

why never on my first dataflows and only the DF 5 or 6?

sys_guid using Execute Sql Task

Fri, 20 Jul 2012 14:53:59 Z

Hi Everyone,

i have column name : DATA_U(target) which is an unqiue constraint column , here

my requirement is i want to assign a unique value i.e, SYS_GUID() value to this column

for this i have took a Global variable with string Datatype,

in Execute Sql Task:

sql query is:

SELECT RAWTOHEX (SYS_GUID())as DATA_U FROM DUAL

this i am assign to global variable .now i am using this variable inside derived column, assign to that column.

But above mentioned query generates value which is same for every row., first record will enter , next value is same,this column is unique so it will not accept same value into this.

error message is :

[Oracle Destination [487]] Error: OCI error encouneterd. ORA-00001: unique constraint (SYSSS.SYS_C00295113) violated

i can make use of script component :
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/86c375c2-98fa-4fc3-b9be-2e11aa1aacdd

but i want to make use of Execute sql Task, with variable, then this variable can be used in different dataflow task, because when ever i am using script component it is taking some time to load. is this possible to make use of Execute sql Task.

Please Suggest me.

karteek_miryala

SSIS real time / simply extract and Load into DW

Sun, 22 Jul 2012 03:31:36 Z

Ok, so here is a simple question. I want to extract data from a SQL DB, and load it as is into my SQL server 2008 r2 database. Even though I will be running this every 30 minutes via a job on proxy account, but what is the best way to do it. When new to BIDS, I first simply used wizard to drop and recreate. Then due to performance hits on windows server 2008 r2, i changed drop and recreate to truncate.

So, to keep things very simple what is the best way to simply extract all the tables from sql and load it into my Data warehouse which is also on sql server 2008R2. I don;t make almost fast as real time. Can you guys give me some steps to do. All I am currently doing which i want to continue to do is take the raw data and integrate it as is into my Data warehouse.

thanks

SSIS Execute SQL Task Parameter passing

Sat, 21 Jul 2012 18:37:03 Z

My Execute SQL Task has OLE DB Connection to master table as initial catalog;

SQL statement is CREATE DATABASE ?;

BypassPrepare->true

Parameter Maping, Variable Name->User::InitialCatalog, Direction->Input, Data Type->NVARCHAR, Parameter Name->0, ParameterSize->20;

Variable User::InitialCatalog is of type string with initial value set as Project3;

So, it should be executed CREATE DATABASE Project3;

I get an error [Execute SQL Task] Error: Executing the query "CREATE DATABASE ?;" failed with the following error: "Incorrect syntax near '@P1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If i try to execute CREATE DATABASE Project3 without parameters, it works.

As i understand it just change CREATE DATABASE ?; with name of parameter CREATE DATABASE @P1; but not with value of parameter CREATE DATABASE Project3; as it should be.

Please help.

Fail Package If any of the Task fails

Sat, 21 Jul 2012 00:08:10 Z

Hi, i have an SSIS package having two Sequence Containers connected to Execute sql task in Parallel.

I have an expression in between execute sql and both sequence containers if @Test = 1. @Test gets value from execute sql task.

I want to fail the package if any of the two sequence container fails. So i set FailpackageOnFailure to TRUE for both containers but even though one of them fails, the package wont fail and other one still runs as i have an expression for both.

Any ideal how do i solve this?

This is how the package looks like:

Strange---Script Task Question

Fri, 20 Jul 2012 16:42:35 Z

I am trying to post the file to ftp site, file is posted as empty whenever i run the package. but when i run the task individually the file is posted in the ftp site, could you please tell what could be the error. i am using same code to post another file in the same location it works perfect....

i am using following code

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "XXXXX")
cm.Properties("ServerUserName").SetValue(cm, "XXX")
cm.Properties("ServerPassword").SetValue(cm, "XXXX")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
Dim files(2) As String
files(0) = "F:\NNExtract\Staging\PDI_NN_Program_" + Format(Now(), "yyyyMMdd") + ".xls"
'ftp the file
ftp.SendFiles(files, "/veeva/VeevaShipments/NewFolder", True, True)
ftp.Close()
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

Excel sheet to database

Sat, 21 Jul 2012 11:11:12 Z

I create a package in SSIS,in that package i want to load from excel to sql server db..in that excel sheet i am having column called order in that i am having

3.1

3.1.1

3.1.2

3.1.2.1

like this order is in Excel sheet..My table contain that Order as string ..But when i try to load that excel sheet to database it takes only 3,3.1 values

i think that problem is in Excel .if i change that columns string its takes only

3.1.1

3.1.2

3.1.2.1 these value to load

if i gave number format in Excel

it takes

3.1 these value only..How to solve this issue in SSIS

Excel Destination .

Mon, 16 Jul 2012 09:41:54 Z

Hi All,

I have requirement is as below

1. I MDX query which pulls 108736 record in all my requirements is if the records are more than 65K then record should go to next sheet and so on

2. I have excel template with some header and Columns heading and the data insertion shoulod start from row 6

Thanks

Hope this will help you !!!
Sanjeewan

Attach MS Access Report to Send Email?

Thu, 19 Jul 2012 17:42:25 Z

MS SQL 2008

Prior to recently converting our MS Access 2003 BE to SQL 2008 I ran a scheduled task from my local PC to run a MS Access FE module that would run select Reports and send them as email attachments.

This is a very painful process for my local PC as it is opening the MS Access FE across our company WAN (Wide Area Network). IT will not give me access to scheduled tasks control panel on our Citrix Server Farm.

Now that we have coverted our BE to SQL 2008. I would like to move this process of sending weekly/daily reports to SSIS package is possible.

So the question is. Can I attach a MS Access Report to the Send Mail Task in SSIS? If I can, how?

If not hopefully there is an alternative via stored procedures.

ForEach Loop for CSV files

Fri, 20 Jul 2012 23:17:53 Z

I created an SSIS package that will grab CSV files in a folder and import them into the SQL Server. I've done imports in the past with Excel and experienced no problems. However, I'm having trouble in the deployed environment whereas the test environment is working without any problems.

The ForEach loop has a variable under expressions for the directory and it seeks CSV files (by name and extension). The import file is also listed under variable mappings. The process should check the folder for ANY CSV file and import them into SQL Server. In the test environment, this works. However, in the deployed environment, it does not grab the files (the directories are the same, test file names are the same).

Does anyone know what the problem might be?

(Note: in the deployed environment, SSIS will show that it successfully completed with no debug errors, however, no data were imported).

PK violation

Fri, 20 Jul 2012 16:11:05 Z

my dataflow task in SSIS 2008 is failing with PK violation error, when run as job. I checked the source data and couldn't find any duplicate . The destination is always truncated before the dataflow task runs. But when I run the same package from visual studio, I executes successfully. there is around 13mil rows. Any idea what could be causing this?

Thanks

How to load data from SQL to TERADATA table using ODBC connection

Fri, 20 Jul 2012 20:44:19 Z

Hi all,

I need to send data from SQL to Teradata destination. TPT load is not working properly on my machine. I have 32 bit ODBC driver for Tera data on my machine. SO can any one please let me know the procedure like what kind of destination i should use and how to set properties.

Thanks in advance,

Jyo

FTP Automated downloader

Wed, 18 Jul 2012 17:59:51 Z

I dont have much experiance with SSIS but i know this is doable on it. What i am looking for is a script that would let me download from an FTP website. Couple of critierias that are important:

unix server

files from server are deleted every four days that is why i want to download the files to company hard drive.

i dont want a duplicate of folders/files downloaded every time.

when i log in as the admin, there are 20 folders. each folder is named after a company member. when the company member logs in they see only the content of the folder that is specified to them. i want to download the content of the folders. because if i download the folder. it will just download the whole folder and i'll have a lot of folders by the same name. so the thing i did was make directories on my hard drive that has the same name as the folders i see. (this part might be confusing so ask me to explain if needed. )

also i think it would be a good idea to delete the contents of the folder once the download happens so no duplicates are downloaded.

if anyone has written a similer script or has a SSIS(visual studio) file that will help me out, i'd appricate it.

basic steps:

go into folder A

download the content of folder A to hard drive(time stamping would help)

delete the content of folder A.

go into folder B

download the content of folder B to hard drive

delete the content.

keep on doing until done to all folder.

Thank you!

How to access different versions of ADO.net driver?

Wed, 18 Jul 2012 17:08:02 Z

Hi,

we want to access data via a SQL Anywhere ADO.net driver. The issue is that this driver has slightly different versions on different servers and developer machines, while the package should be the same across the servers.

The version info, as found in machine.config is e. g. as follows:

It seems that the ADO.net connection manager references the exact version of the driver in its "Qualifier" property, which is e. g.

iAnywhere.Data.SQLAnywhere.SAConnection, iAnywhere.Data.SQLAnywhere.v3.5, Version=12.0.1.3473, Culture=neutral, PublicKeyToken=f222fc4333e0d400

This property is not available for configurations.

I tried to edit the machine.config file to contain:

where the newVersion value matches the version installed on the machine. But this does not seem to be used, and I get the same error as without this entry if the version in the Qualifier property does not exactly match the version installed on the machine:

Error: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.

This error does not occur if the version info exactly matches the version of the driver installed.

Any idea how I could get this running?

Frank

SSIS Logging

Tue, 17 Jul 2012 14:47:56 Z

Hi,

I have enabled SSIS SQL logging on my package for OnInformation event. When I run it on my machine (Windows 7 32-bit), it logs component level log such as row count for data flow tasks.

Example:

"The final commit for the data insertion in "component "xxxxxxxxxxxx" (392)" has started. "

"component "xxxxxxx" (97)" wrote 1 rows.

But when I deploy same package to server (Win2008 R2 64 bit) then it logs all other informational messages except above msgs. I did not find anything on google or bing related to this issue.

What is the cause?

Thanks,

Balvinder Singh

Access To the Path is Denied

Mon, 09 Jul 2012 18:28:52 Z

First, I have read through the numerous posts on this topic and now of the provided solutions have worked. So I post my specidifc issue.

Using VSTA2008 from SQL2008 STD edition.

Using local machine for development. I am the local and domain admin.

The path that I am denied access to is the Local C:\TEMP directory.

In BId, I now have two scripts that run. the first script sets the file level permissions to full control under the context of the task that is being run.

The secong task performs a PGP decryption of the files. This is were I get the error. I can set the file permissions with out an issue, yet as soon as I caaess the same files to decrypt them, I get the error. The error is being thrown as soon as I access the files. The PGP decryption routine will run when attached to a windows forms project on the same machine. This is how I was able to test and debug the code prior to adding it to the SSIS Task.

In total the job picks up a file from an FTP site, moves it to the local machine, decrypts file, then pipes it along for further processing. The only point of failure is in the one script task. and I am at a loss.

Let me add this in case it is relevant. in the affected script, I reference a third party dll (bouncycastle.crypto) for the PGP encrypt/decrypt utilities. A second code (class) file exists that performs all of the logic, and i call it from the script main() passing in the validated arguments.

If anyone has any ideas, I am open to any and all suggestions.

Thanks in advance.

SSIS Excel Source Returning NULL Values

Sat, 14 Jul 2012 03:06:31 Z

Hi All,

In my SSIS package excel source i am building the expression at the run time. In the excel sheet for a particular column the rows are blank up to certain no of rows from beginning. So while loading the whole column value is loading as NULL value. I have put IMEX=1 in the connection string.

Still i am getting all NULL values.

Any suggestion is appreciated.

Thanks

Package fails when trying to load Flat file to SQL Server 2008

Thu, 19 Jul 2012 15:45:42 Z

Trying to load Flat File to SQL Server. Number of columns in Flat file

ProductID - two-byte signed integer [DT_I2]
Name -Unicode string [DT_WSTR](50)
ProductNumber-Unicode string [DT_WSTR](25)
MakeFlag-Boolean [DT_BOOL]

Getting issue with last column 'MakeFlag'.

I have attached sample Flat file if some body wants to test it

Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "D:\Users\Administrator\Desktop\New Folder\IndiaIndia.txt" on data row 211.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Need to convert the boolean value to a 0 or 1 before importing it

Used derived column to convert the boolean value to a 0 or 1

Expressions what i used in derived column for two columns MakeFlag and FinishedGoodsFlag

MakeFlag ? (DT_BOOL)1 : (DT_BOOL)0

FinishedGoodsFlag ? (DT_BOOL)1 : (DT_BOOL)0

I get error messages as below.Any thing wrong with the expression i am using here.Please let me know.Please send me the correct expression

Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

XML-->SQL Table:: table load question

Thu, 19 Jul 2012 17:14:57 Z

I'm loading a table that has a generated primary key. For every records being pushed to this table I want to see if Serial Number has already been pushed. If it has been pushed once I will change new record's column ActionCode to "U" for update. The default value for ActionCode is "I". How can I do that if serialNumber doesn't have a primary key? Duplicate records are allowed in table as a requirement.

Evan Johnson

SSIS does not recognize CRLF as the en of line

Fri, 14 Jul 2006 08:29:46 Z

Hi all,

I'm having a strange behavior here, or maybe I'm doing something wrong, I'm not sure.

Anyway, I have a csv file, the Flat File Connection Manager is configured like this:
Row delimiter: {CR}{LF}
Column delimiter: {;}

For some rows in my file the last two columns are empty and the there is no semicolon for these empty rows but these rows are still ended by a CRLF but SSIS does not consider the CRLF as the end of the row, it consider the first 2 columns of the next row as the last 2 columns of the current row.

Sample:

CSV file:
Col 1;Col 2;Col 3;Col 4;Col 5
AAAA;BBB;CCC;;
AAA1;BBB1;CCC1;;
AAA2;BBB2;CCC2
AAA3;BBB3;CCC3

Imported rows in SSIS:
Col 1      Col 2       Col 3    Col 4    Col 5
AAAA    BBB        CCC
AAA1    BBB1      CCC1
AAA2    BBB2      CCC2   AAA3   BBB3;CCC3

Any idea ?

Sébastien